Excel - save as CSV fields enclosed in quotes...?

Discussion in 'Programming & Software Development' started by d_hall, Aug 15, 2004.

  1. d_hall

    d_hall Member

    Joined:
    Aug 24, 2001
    Messages:
    1,149
    Location:
    Hawthorn.Melb.Vic.Au
    I've been lumped with a task that involves trasferring a fairly substantial volume of data from one DB to another (MS Sql to MySQL). Given the timeframe and my (limited) programming knowledge I'm using Excel to do the data cleansing.

    Anyone know of a way to get Excel to save a worksheet as a CSV (or any other text format) where the fields are enclosed by double-quotes?

    The best I can manage is to concatenate four double quotes onto the front and back of each field, which produces a CSV with three double quotes around each field, which I then have to open in Word/text editor and find/replace back to single quotes.

    I've hade a poke through the "help", tried all the likely looking Excel save formats, and I can't see any options that seem to apply.

    Any suggestions for fixing this in Excel?

    Or perhaps some kind soul willing to knock me up a quick app that takes a big (10-20mb) text file and converts instances of """ to "? :)
     
  2. yoink

    yoink Member

    Joined:
    Feb 19, 2002
    Messages:
    3,489
    If you don't like programming, the use Scite to format the text :thumbup:
     
  3. nav

    nav Member

    Joined:
    Aug 20, 2001
    Messages:
    4,299
    Location:
    melbourne
    do you still need an app?
    pm me your email and i'll send you a script that should do the job.
     
  4. OP
    OP
    d_hall

    d_hall Member

    Joined:
    Aug 24, 2001
    Messages:
    1,149
    Location:
    Hawthorn.Melb.Vic.Au
    Thanks for the offer nav, but I found a prog called "HandyFile Find and Replace" that does the job.

    Still desparately need to solve the Excel issue though, as the data is not in a format where I can find/replace to insert the quotes around fields (unfortunately one of our data sources are idiots, and allow free text entry into the DB so it's full of HTML and advertising crap).

    The concatenate """" solution is only workable up to about 20,000 rows before Excel begins to chuck the sh*ts, and the set I'm working on at the moment (which is 1/4 of the total data) is about 450,000 rows.
     
  5. nav

    nav Member

    Joined:
    Aug 20, 2001
    Messages:
    4,299
    Location:
    melbourne
    yea, dad's run into the same problems you have. i think excel typically handles a max of 65,536 rows. i'm guessing you've hit resource limitations on your machine.. let us know if you find a work around.

    two questions for you:

    why do your fields need to be " delimited? a csv produces comma delimited/seperated values, which is the standard used by pretty much everything?

    and i don't really understand why you concatenate """" infront of fields? i'd image your work process would be something like this:
    * use excel to save as .csv
    * then search & replace all commas with quotations

    or have i missed something?
     
  6. OP
    OP
    d_hall

    d_hall Member

    Joined:
    Aug 24, 2001
    Messages:
    1,149
    Location:
    Hawthorn.Melb.Vic.Au
    The data contains both commas and quote marks, so a simple Excel CSV file won't work: I need to enclose each field, a bit like putting HTML tags before and after web content so the browser can understand it.

    Unfortunately since the data input hasn't been controlled, I can't be sure that there aren't misleading character combinations (like ,"... in fact I'm know that there are these kinds of errors).
     
  7. da_moat

    da_moat Member

    Joined:
    Jul 9, 2001
    Messages:
    209
    Location:
    Central Coast, NSW
    we have this problem alot at work cleaning up mailing data and i've never found a way of forcing excel to put quotes around fields, if it doesn't want to it just wont. i wrote a dodgy little addin to do exports, have that at work if its wanted. an alternative i use is to import your cleaned spreadsheet into an access database. make sure all your fields types in the table are set to text, then export it as a comma delimited txt file. access will put quotes around all fields if they text fields.
     

Share This Page

Advertisement: