CODEWITHSUNDEEP
mysqlcsv
BryanK
BryanK

Reputation: 1221

LOAD DATA INFILE, skipping first column in table

I am having trouble finding the correct syntax for loading a csv file into table, while skipping the first column which already exists in the table. My table columns looks like this:

ID    COL1    COL2    COL3    LOG_DATE

and my csv looks like this :

dataForCol1,dataForCol2,dataForCol3

So I want to load the values in the csv into COL1 COL2 and COL3 , skipping ID. The closest I can get is with SQL like this:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' 
(ID,COL1,COL2,COL3,LOG_DATE)
SET ID=0, LOG_DATE=CURRENT_TIMESTAMP

Note that I dont know SQL too well and I am not sure if I am using the SET clause correctly either, but this statement will supply the LOG_DATE column with a timestamp, and It will auto increment the ID column (ID is type: int(11) and auto_incerement) but the other data is off by one column so dataForCol1 is missing and dataForCol2 is in COL1 etc.

Upvotes: 3

Views: 12641

Answers (3)

user6377854
user6377854

Reputation: 31

Simply move your ID col to the last field position like this:

EG: COL1    COL2    COL3    ID    LOG_DATE

Then use the same code without defining your fields or id:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' 
SET LOG_DATE=CURRENT_TIMESTAMP

Super easy and works great.

Upvotes: 2

user3092711
user3092711

Reputation: 23

The only way I could solve this same problem is to create a temp table to load all the data into, then insert that data into the permanent table. I was using this in conjunction with a multi-file upload process:

# Check for attempted file/s upload
if (isset($_FILES['files']))
{
    # Create a temporary table to insert data into
    $sql="CREATE TEMPORARY TABLE `Temp` (Region VARCHAR(60), First_Name VARCHAR(35), Last_Name VARCHAR(35), Title VARCHAR(60), Account_Name VARCHAR(60), Phone VARCHAR(21), Email VARCHAR(60));";
    $Result = mysql_query($sql,$MySQL_Read);

    # Loop through file/s to be uploaded
    foreach ($_FILES['files']['tmp_name'] as $key => $tmp_name)
    {

        # Upload file
        move_uploaded_file($tmp_name, $Path."{$_FILES['files']['name'][$key]}");
        echo $Path."{$_FILES['files']['name'][$key]}<br>";

        # LOAD DATA from CSV file into Temp
        $sql="LOAD DATA INFILE '".$Path."{$_FILES['files']['name'][$key]}' INTO TABLE `Temp` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r' STARTING BY '' IGNORE 1 LINES;";
        $Result = mysql_query($sql,$MySQL_Write);
        echo($sql.'<br><br>');

        # Delete file
        unlink($Path."{$_FILES['files']['name'][$key]}");
    }
}

# Insert data from temp table into permanent table
$sql="INSERT INTO `Leads` (`Region`, `First_Name`, `Last_Name`, `Title`, `Account_Name`, `Phone`, `Email`) SELECT `Region`, `First_Name`, `Last_Name`, `Title`, `Account_Name`, `Phone`, `Email` FROM `Temp`;";
$Result = mysql_query($sql,$MySQL_Write);

# Delete temporary table
$sql="DROP TEMPORARY TABLE Temp;";
$Result = mysql_query($sql,$MySQL_Write);

Upvotes: 0

palindrom
palindrom

Reputation: 19101

The column list should iclude the columns that are in the file only:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' 
(COL1,COL2,COL3)
SET LOG_DATE=CURRENT_TIMESTAMP

Upvotes: 12

Related Questions