SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL LOAD DATA INFILE is missing columns

    I'm testing a small sample of a CSV file to be uploaded into MySQL.

    However the LOAD DATA INFILE command is missing the first column and I don't know how to get it in.

    It always puts my second column from CSV into the first column.

    Here is the database;

    Code:
    ; Pseduocode Database
    id (primary key, auto_increment, int)
    OfferProcessType (var 32)
    TradingName (var 32)
    Now, here is the CSV file.

    Code:
    OfferProcessType,TradingName
    E,
    A,My Travel
    And here is my MySQL call;

    Code:
    LOAD DATA INFILE 'testy.csv' INTO TABLE `testy`
      FIELDS TERMINATED BY ',' ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
    ;
    Problems;

    1. For some reason this call does not pull in my first column. How do I get it to read the first column and put it into the database?

    2. How do I ensure that the columns in the CSV file are the same as those in MySQL?

    3. How do I make sure MySQL does not import the column names, or any blank rows (in my example the second row is empty).

    I am currently adding this line: "IGNORE 2 LINES;" into my MySQL call.

    This removes the first 2 rows, but I don't know if this is the right way to do it.

  2. #2
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, according to the docs, I can specify column names, but I don't know how to use it.

    Everytime I try it, it always skips the first column.

    Example;
    Code:
    LOAD DATA INFILE 'testy.csv' 
      INTO TABLE `testy`
      FIELDS TERMINATED BY ',' ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
      IGNORE 2 LINES  
      (id, OfferProcessType, TradingName) 
    ;
    The last line is meant to be the columns, but it seems to completly ignore this.

    The ignore 2 lines seems to work for hiding the first 2 rows.

  3. #3
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got it working now!

    Code:
    # Working version
    LOAD DATA INFILE 'testy.csv'  IGNORE INTO TABLE testy 
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 2 LINES  
    (OfferProcessType,TradingName)
    Yey!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    congrats for working it out, and thanks for updating us on the solution

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •