Importing .csv to MySQL

Discussion in 'Web Design and Development' started by EndingDesire, Sep 22, 2010.

  1. EndingDesire macrumors newbie

    Joined:
    Sep 22, 2010
    #1
    Currently i'm trying to import a bunch of records that are in csv format and have this look to them,

    When adding the csv file to my database it doesn't seem to interpret the end of the line correctly. I'm using the following settings:
    CSV using Load Data
    fields terminated by,
    Fields enclosed by ,
    Fields escaped by \
    lines terminated by auto
    and the appropriate column names.


    After the import the table looks like this,
    It seems to take the AL5 from the second record and append it to the last part of the first record, and stops. Can anyone shed light on how I can make this continue throughout the rest of the file?
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    Do you know what kind of line endings the CSV file is set for? Editors like TextWrangler will show it is set for Mac/Unix/Windows. This could be impacting this.
     
  3. EndingDesire thread starter macrumors newbie

    Joined:
    Sep 22, 2010
    #3
    Text wrangler says,

    Line Breaks: Classic Mac (CR)

    Is there a keystoke to denote this in mysql import?
     
  4. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
  5. Darth.Titan macrumors 68030

    Darth.Titan

    Joined:
    Oct 31, 2007
    Location:
    Austin, TX
    #5
    Leave off the
    Code:
    fields enclosed by ,
    line. The fields can't be terminated by AND enclosed by commas after all.
     
  6. EndingDesire thread starter macrumors newbie

    Joined:
    Sep 22, 2010
    #6
    Adding a \r to the line terminated by seemed to work. However, now I have some other importing questions.

    I downloaded a .csv sheet of alot of information similar to the above
    However, what if, for instance one of the records is as follows,
    The import seems to mess up the ,, and just start to perform very strange actions. Is there a way I can battle this?
     
  7. BertyBoy macrumors 6502

    Joined:
    Feb 1, 2009
    #7
    Yeah, it might.

    I normally process my CSVs from third-parties

    = to validate each row has the correct number of fields, you don't need to do this.
    = to turn them into tab-separated files, .tsv - just so I know if it's been processed or not, you don't need to do this.
    = to correct those double commas, inserting a \N so that the import treats it as a NULL. Make sure these columns are NULLABLE. You will need to do this.
     

Share This Page