Converting CSV in VBA

[Pathagoras1953]

This may relate to the other thread re: problems with conversion.

I am trying to read a csv file in VBA. I am using the Input #1 command

Mac conversions of csv within vba seem to use a 'space' as a value break point as well as commas. So a term such as "My Term" gets read as two separate fields. So quotes appear to be necessary when the value is more than one word, in addition to being necessary when there is a comma organic to the value (such as in "Jacksonville, FL").

I am having an additional issue decoding CSV data (which may actually be what is happening to the others):

The "Enter" (line end) at the end of a row of values that contain quotes enclosing values (see above) is being picked up as the first character of the next value. How stupid is that?

I can remove the Enter with vba code is like this:

If left(data,1) = chr(13) then data=mid(data,2)

but I should not have to

Converting csv files on a Mac is a real challenge. Is anyone else having similar experiences and do you have any suggestions?

 

[Hansr]

Does it need to be read via VBA code or just by Excel?

 

[Pathagoras1953]

It needs to be read by vba because I am in Word, not Excel, and am processing the data behind the scenes, not on-screen.

 

[Hansr]

Thinking about it, running a simple regex on the file might be the best option before running it into VBA. E.g. first just make everything quoted between all commas (for the comma separated within fields you might want to switch to Euro CSV (; as separator)).

For the line breaks it's most likely a \r\n vs \n only line break. Switching all to \n only should do the trick.

/usr/bin/sed would most likely be the best options to regex the csv before processing considering that VBA doesn't have access to a regex library on Mac.