Advanced Search

Results 1 to 4 of 4

Thread: using LOAD DATA LOCAL INFILE

  1. #1
    Join Date
    Dec 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default using LOAD DATA LOCAL INFILE

    I have gotten great help using this LOAD DATA LOCAL INFILE This is very nice. I was wondering if anyone knows how to skip fields on the upload. The csv file has 126 columns, I only need to import about 15 of those. Do I need to alter the table I have to accept those 15? or is there something in the PHP that I need to be doing to import those columns or Both?

    Here is my code:

    PHP Code:

    <?php

    if (isset($_POST['load']))
    {

    include 
    '_inc/include.php';

    $temp $_FILES['myfile']['tmp_name'];

    $sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE carboncounty_PA02 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";

    mysql_query($sqlstatement) or die(mysql_error());

    echo 
    "It worked";


    ?>

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Untested, but I would suspect that you'd need to modify the data before sending it to MySQL. I can't find any syntax that would allow you to skip particular fields.

    Depending on the size of your files, it may or may not be practical to have PHP handle this.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Ok the problem I had was that the csv file is being uploaded, I am lucky that the file will always be pretty much the same 126 columns and I only needed to process/query 15 of those. SO (for search engine status) How to skip fields while using LOAD DATA INFILE. Those of you trying this, check with your host provider, as is the case with mine I had to add the line LOCAL to the statement, hence
    Code:
    $sqlstatement="LOAD DATA LOCAL INFILE
    How to skip fields on input using LOAD DATA LOCAL INFILE.

    Code:
    LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
        [REPLACE | IGNORE]
        INTO TABLE tbl_name
        [CHARACTER SET charset_name]
        [{FIELDS | COLUMNS}
            [TERMINATED BY 'string']
            [[OPTIONALLY] ENCLOSED BY 'char']
            [ESCAPED BY 'char']
        ]
        [LINES
            [STARTING BY 'string']
            [TERMINATED BY 'string']
        ]
        [IGNORE number LINES]
        [(col_name_or_user_var,...)]
        [SET col_name = expr,...]
    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. (dev.mysql)

    Code:
    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.

    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:
    Code:
    LOAD DATA INFILE 'file.txt'
      INTO TABLE t1
      (column1, @var1)
      SET column2 = @var1/100;

    you can discard value on input by assigning a user variable and not assigning the variable to a table column. By using @dummy as a vairiable it essentially skips over the field and leaving it NULL. Way faster. I have a lot of fields so in order to keep track I named them all COLxx My code looks something like this:

    Code:
    $sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE myuploadtable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@COL1, @COL2, @COL3, @COL4, @COL5, @COL6, @COL7, @COL8, @COL9, @COL10, @COL11, @COL12, @COL13, myfield1, myfield2, myfield3, @COL17, myfield4, myfield5, myfield6, myfield7, @COL22, @COL23, @COL24, myfield8, myfield9,..... )";
    mysql_query($sqlstatement) or die(mysql_error());
    echo "It worked";
    This worked out ok, I have not checked security yet but this has been tested.

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Cool! I must have missed that section.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

Similar Threads

  1. How can i change entries_url to load from local file
    By CyberPath in forum JavaScript
    Replies: 0
    Last Post: 07-27-2011, 03:11 PM
  2. using LOAD DATA INFILE
    By fastsol1 in forum PHP
    Replies: 1
    Last Post: 08-27-2010, 03:12 AM
  3. Replies: 5
    Last Post: 07-19-2010, 10:04 AM
  4. Can Cross Browser marquee II load data from external source?
    By ladyviola in forum Dynamic Drive scripts help
    Replies: 4
    Last Post: 05-12-2008, 05:54 AM
  5. Replies: 1
    Last Post: 06-04-2006, 06:57 PM

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
  •