Results 1 to 13 of 13
  1. #1
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    MySQL LOAD DATA INFILE question
    I am very good at MYSQL but this one has me stumped.

    I was to use the LOAD DATA INFILE statement to import a pipe delimited file into my database. My number of columns in the data file do not match what I have in my database, and the order does not match.

    How do you get LOAD DATA INFILE to skip certain columns on an import and to put data element 4 from a line into column 1 of my database?

    Example:
    Data file: SKU, PRODUCTNAME, URL
    Database columns in order: URL, PRICE, PRODUCTNAME, IMG, SKU

    I know with the LOAD DATA INFILE you can specify what columns in what order. But AFAIK you cannot skip or rearrange columns.

    This can be done in PHP by building each insert the way I need it in a loop and then executing it. However LOAD DATA INFILE is much faster and it is designed for bulk imports.

    TIA.
    P
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  2. #2
    Pimp Duck popdawg's Avatar
    Join Date
    January 18th, 2005
    Location
    Take off eh?
    Posts
    3,249
    I'd like to know this as well although I have a workaround. I load to a temp table and then alter that table to drop the columns I don't need or that I merge into one.
    Works ok but was curious if there is a more efficient way to do it.
    ================================================================
    Been away, now I'm back. Not as much, but I'm back & starting from scratch. Where I was, was fantastic. Where I am now, less so. Things have changed, become harder. So have I. Game ON!!!
    ================================================================

  3. #3
    Lite On The Do, Heavy On The Nuts Donuts's Avatar
    Join Date
    January 18th, 2005
    Location
    Winter Park, FL
    Posts
    6,930
    this work? see dummy trick where vars used to make nothing import to chosen fields and they'll then be set to your table's default values.

    ~~~~~~~~~~~~~~~~~~~~~~~~

    http://dev.mysql.com/doc/refman/5.0/en/load-data.html

    By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

    LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

    You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

    Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

    User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

    LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, @var1)
    SET column2 = @var1/100;

    The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

    LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

    You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

    LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, @dummy, column2, @dummy, column3);

    ~~~~~~~~~~~~~~~~~~~~~~~~~

  4. #4
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Thank you Mr. Nuts. I'll check this out.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  5. #5
    Pimp Duck popdawg's Avatar
    Join Date
    January 18th, 2005
    Location
    Take off eh?
    Posts
    3,249
    I like chocolate donuts and maple donuts and jelly filled donuts and boston creme donuts and glazed donuts and who knew ... I like mysql/php guru donuts.

    Thanks Donuts!!
    ================================================================
    Been away, now I'm back. Not as much, but I'm back & starting from scratch. Where I was, was fantastic. Where I am now, less so. Things have changed, become harder. So have I. Game ON!!!
    ================================================================

  6. #6
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    OK. My eyes are burning from looking at this too long. Any of fellow MySQLers want to take a look and see why mysql is balking at the SQL below. I've made sure that the list of columns match what is in the pipe delimited file mydata.txt, and the column names match with real column names in my products table:

    ---

    Code:
    LOAD DATA INFILE 'C:\\temp\\mydata.txt'
        INTO TABLE marketing.products
        FIELDS terminated by "|"
        LINES terminated by "\n"
       (skuid, productname, @dummy, @dummy, buyurl, picturelinksmall, picturelinklarge, priceretail,
        @dummy, category1, category2, desclong, category3, category4, category5, @dummy,
        yearmade, @dummy, productstatus )
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  7. #7
    Full Member TLE's Avatar
    Join Date
    January 21st, 2005
    Location
    Southern California
    Posts
    338
    Quote Originally Posted by patrick24601
    How do you get LOAD DATA INFILE to skip certain columns on an import and to put data element 4 from a line into column 1 of my database?
    LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (@var1, column2, column3, @var4)
    SET column1 = @var4;

    What error message are you getting?

    Tuan

  8. #8
    Lite On The Do, Heavy On The Nuts Donuts's Avatar
    Join Date
    January 18th, 2005
    Location
    Winter Park, FL
    Posts
    6,930
    space after productstatus and before ) should be ignored...

    missing ; from end of your command also likely not a problem...

    try \r\n instead of \n ?

    avoid desc as part of any field name since desc is a sql command?

  9. #9
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Error message is:

    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@dummy, @dummy, buyurl, picturelinksmall, picturelinklarge, priceretail,
    @d' at line 5"
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  10. #10
    Lite On The Do, Heavy On The Nuts Donuts's Avatar
    Join Date
    January 18th, 2005
    Location
    Winter Park, FL
    Posts
    6,930
    could it be...

    "Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables."

  11. #11
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Might be. The local version here is 4.1.14. I'll have to look see how to do a LOAD DATA for a version that far back.

    Once again you show me the error of my ways.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  12. #12
    I like traffic lights
    Join Date
    January 18th, 2005
    Location
    Southern hemisphere - away from Fukushima
    Posts
    2,936
    4.1.14 isn't THAT far back. Only a little way.

  13. #13
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Yeah but I think it is old enough to where the LOAD DATA got more flexible in version 5. For right now I have succumbed to a two stepper: Load it into a table that matches the vendor columns, then do an INSERT INTO one table using the select from the first table. My host is running PHP4 so I cannot take advantage of the new features yet.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  14. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Data Feeds - Load and Comparision
    By Black Magic in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 4th, 2007, 09:11 AM
  2. MYSQL LOAD DATA - access denied
    By vivekar in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: October 4th, 2004, 04:58 PM
  3. Replies: 14
    Last Post: February 21st, 2004, 10:13 AM
  4. Try to load a datafile into MySQL....
    By TJ in forum Programming / Datafeeds / Tools
    Replies: 26
    Last Post: February 7th, 2003, 06:45 PM

Posting Permissions

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