Arg, data issue is driving me mad.

Discussion in 'Mac Programming' started by detz, Feb 4, 2009.

  1. detz macrumors 65816

    Jun 29, 2007
    I've been sent a whole bunch of excel files from a client and I need to get that data out and make use of it in the app. The issue is the data is in excel, it's not consistent and has weird MS characters in it. The biggest issue is probably that the second column has everything from quotes to commas to newlines so if I try to export it as csv it breaks. I've tried exporting in ever type to see if there was a way I could parse it but I'm out of ideas. There must be a way, I mean excel and numbers both read the xsl and csv files correctly but I can't get anything else to.

    Is there a way I can export this from one of these programs into a meaningful portable way?
  2. toddburch macrumors 6502a

    Dec 4, 2006
    Katy, Texas
    I've successfully used Ruby on Windows to set and get data from an excel spreadsheet. Haven't tried it on a Mac yet.
  3. lazydog macrumors 6502a

    Sep 3, 2005
    Cramlington, UK
    Are you sure the csv is broken? csv can handle quotes, commas and new line characters - fields are quoted if they contain the separator character and any quote characters are escaped (off hand I think a quote character is escaped as "").
    The other thing you could do is change the separator character to something you know the data isn't using... perhaps ^, @ or $ and then export again.

    b e n
  4. GroovyLinuxGuy macrumors regular


    Apr 2, 2006
    I don't know if this will work or not, but try opening the files in OpenOffice

    It may allow you to save it as another format. I have done this in the past for weird acting excel and word docs and sometimes it works but conversly sometimes it doesn't.


Share This Page