Converting CSV in VBA

Discussion in 'Mac Apps and Mac App Store' started by Pathagoras1953, Aug 25, 2011.

  1. Pathagoras1953 macrumors newbie

    Joined:
    Aug 22, 2011
    #1
    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:
    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?
     
  2. Hansr macrumors 6502a

    Joined:
    Apr 1, 2007
    #2
    Does it need to be read via VBA code or just by Excel?
     
  3. Pathagoras1953 thread starter macrumors newbie

    Joined:
    Aug 22, 2011
    #3
    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.
     
  4. Hansr macrumors 6502a

    Joined:
    Apr 1, 2007
    #4
    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.
     

Share This Page