[SOLVED] csv loading structure into LOAD DATA INFILE

webent · August 25, 2008

Correct me if I'm mistaking, but the csv file needs to have the columns in the same place as the fields in which the csv file is populating upon loading of the csv file via LOAD DATA INFILE... so, what if that is not the case with the csv file, should I go through some sort of csv conversion process, load it all into a temporary db and then redown back into a csv file into the structure that I need it to be in? Or is there any easier way to go about this?

toplay · August 25, 2008

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.

webent · August 25, 2008

Thank you, I did not find any information previous to your post in regards to being able to define which columns came first, second, etc. ...

The only problem that leaves me with then is that some of the data has to be manipulated prior to being inserted into the db... Here's what I've been using so far for my code, it's pretty extensive...

if(!isset($_POST['file_name']))	{
$dirname = "/home/webzcart/public_html/resource/";
echo '<form action="' . $_SERVER['php_self'] . '" method="post">';
$pattern="(\.csv$)";
$files = array();
    if($handle = opendir($dirname)) {
    echo '
    <table style="width: 300px; margin-top: 50px;">
      <tr>
  	    <td>
    <select name="file_name" size="10" width="30">';
    while(false !== ($file = readdir($handle)))	{
    if(eregi($pattern, $file))
    echo '<option value="' . $file . '">' . $file . '</option>';
										    }
    echo '</select>
    </td>';
    closedir($handle);
    }
echo '<td width="20"> </td>
<td><input type="submit" name="fields_defined" value="Import"></td>
  </tr>
</table>
  </form>';
}
if(isset($_POST['fields_defined']))	{
$date_time_stamp = date("Y-m-d h:i:s");
$row_counter = 0;
$handle = fopen($_POST['file_name'], "r");
while (($fields = fgetcsv($handle, 0, ",")) !== FALSE) {
    $row_counter++;
    if ($row_counter == 1) { 
        // First row header removed. 
    }	else {
        
// Remove apostrophes
$search = array("'");
$replace = array("");
$fields = str_replace($search,$replace,$fields);

list($product_line, $product_master_category, $product_category) = split('[|]', $fields[3]);

$path = pathinfo($fields[4]);
$product_image_path = $path['dirname'] . "/";
$product_image = $path['basename']; 
  
$product_vendor = "doba";    
$product_sku = $fields[1];   
$product_quantity = $fields[17];  
$product_manufacturer = $fields[5];  
$product_name = $fields[2]; 
$product_description = $fields[6] . '<br />' . $fields[7];  
$product_price = $fields[15];  
$product_msrp = $fields[16];  
if ($fields[14] > 0) {
$product_map = "Yes";    
}
    else {
        $product_map = "No";     
    }                                           
$product_map_price = $fields[14];  
$product_weight = $fields[8];       
$product_set_ship_price = $fields[19];      
$product_added_date = $date_time_stamp;  
$product_upc = $fields[21];    
$product_metatags_title = $fields[21] . " - " . $fields[2];  
$product_metatags_keywords = $fields[21] . " - " . $fields[2];  
$product_metatags_description = $fields[6]; 

//////////// Begin Products Section //////////////////

//////////// Get the id of the product in the database ///////////////
$product_id = 0;
$query = "SELECT product_id FROM products WHERE product_vendor = '$product_vendor' AND product_sku = '$product_sku'";
$results = mysql_query($query);
while ($row  =  mysql_fetch_assoc($results))	{
	$product_id = $row['product_id'];    
    }
    if ($product_id < 1) {
$query_type = "insert";
    }
    if ($product_id > 0) {
$query_type = "update";
    }
if (!$results) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}                               
/////////////////////////////////////////////////////////////////////////////////////////   
if ($query_type == "insert") {
$query = "INSERT INTO products VALUES ('','$product_vendor','$product_model','$product_sku','$product_status','$product_quantity','$product_manufacturer','$product_name','$product_line','$product_master_category','$product_category','$product_image','$product_image_path','$product_description','$product_price','$product_msrp','$product_map','$product_map_price','$product_weight','$product_height','$product_width','$product_length','$product_set_ship_method','$product_set_ship_price','$product_quick_ship','$product_added_date','$product_upc','$product_asin','$product_ebay_cat_id','$product_metatags_title','$product_metatags_keywords','$product_metatags_description')";
    $results = mysql_query($query);
if (!$results) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
}  
if ($query_type == "update") {
    
$query="";

if (!empty($product_sku) OR $product_sku != ""){
$query=$query."product_sku='$product_sku', ";
}  
if (!empty($product_quantity) OR $product_quantity != ""){
$query=$query."product_quantity='$product_quantity', ";
}
if (!empty($product_manufacturer) OR $product_manufacturer != ""){
$query=$query."product_manufacturer='$product_manufacturer', ";
}
if (!empty($product_name) OR $product_name != ""){
$query=$query."product_name='$product_name', ";
}
if (!empty($product_line) OR $product_line != ""){
$query=$query."product_line='$product_line', ";
}
if (!empty($product_master_category) OR $product_master_category != ""){
$query=$query."product_master_category='$product_master_category', ";
}
if (!empty($product_category) OR $product_category != ""){
$query=$query."product_category='$product_category', ";
}
if (!empty($product_image) OR $product_image != ""){
$query=$query."product_image='$product_image', ";
}
if (!empty($product_image_path) OR $product_image_path != ""){
$query=$query."product_image_path='$product_image_path', ";
}                                                          
if (!empty($product_description) OR $product_description != ""){
$query=$query."product_description='$product_description', ";
} 
if (!empty($product_price) OR $product_price != ""){
$query=$query."product_price='$product_price', ";
}  
if (!empty($product_msrp) OR $product_msrp != ""){
$query=$query."product_msrp='$product_msrp', ";
} 
if (!empty($product_map) OR $product_map != ""){
$query=$query."product_map='$product_map', ";
} 
if (!empty($product_map_price) OR $product_map_price != ""){
$query=$query."product_map_price='$product_map_price', ";
} 
if (!empty($product_weight) OR $product_weight != ""){
$query=$query."product_weight='$product_weight', ";
} 
if (!empty($product_set_ship_method) OR $product_set_ship_method != ""){
$query=$query."product_set_ship_method='$product_set_ship_method', ";
}                                                           
if (!empty($product_set_ship_price) OR $product_set_ship_price != ""){
$query=$query."product_set_ship_price='$product_set_ship_price', ";
}  
if (!empty($product_added_date) OR $product_added_date != ""){
$query=$query."product_added_date='$product_added_date', ";
} 
if (!empty($product_upc) OR $product_upc != ""){
$query=$query."product_upc='$product_upc', ";
}  
if (!empty($product_metatags_title) OR $product_metatags_title != ""){
$query=$query."product_metatags_title='$product_metatags_title', ";
}                                                            
if (!empty($product_metatags_keywords) OR $product_metatags_keywords != ""){
$query=$query."product_metatags_keywords='$product_metatags_keywords', ";
}
if (!empty($product_metatags_description) OR $product_metatags_description != ""){
$query=$query."product_metatags_description='$product_metatags_description', ";
}                      
    $query = substr_replace($query," ",-2);
    $sub_query = "UPDATE products SET ".$query. " WHERE product_id = '$product_id'";
        $results = mysql_query($sub_query);
if (!$results) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
}     
/////////////////////////////////////////////////////////////////////////////////////////   
    }
}
echo $row_counter . ' rows were processed.';
}

I don't mind getting rid of the update part of the code and just deleting all relevant entries first and then doing a fresh insert, but still there are things that need to be done prior to insertion, such as the categories field in the csv file has 3 separate categories in one, so I split them... and the image path field has to be broken down into to two different fields, the path and the image...

You have any ideas as to how I can go about doing this... Here's the new code without the update...

<?
if(!isset($_POST['file_name']))	{
$dirname = "/home/webzcart/public_html/resource/";
echo '<form action="' . $_SERVER['php_self'] . '" method="post">';
$pattern="(\.csv$)";
$files = array();
    if($handle = opendir($dirname)) {
    echo '
    <table style="width: 300px; margin-top: 50px;">
      <tr>
  	    <td>
    <select name="file_name" size="10" width="30">';
    while(false !== ($file = readdir($handle)))	{
    if(eregi($pattern, $file))
    echo '<option value="' . $file . '">' . $file . '</option>';
										    }
    echo '</select>
    </td>';
    closedir($handle);
    }
echo '<td width="20"> </td>
<td><input type="submit" name="fields_defined" value="Import"></td>
  </tr>
</table>
  </form>';
}
if(isset($_POST['fields_defined']))	{
$date_time_stamp = date("Y-m-d h:i:s");
$row_counter = 0;
$handle = fopen($_POST['file_name'], "r");
while (($fields = fgetcsv($handle, 0, ",")) !== FALSE) {
    $row_counter++;
    if ($row_counter == 1) { 
        // First row header removed. 
    }	else {
        
// Remove apostrophes
$search = array("'");
$replace = array("");
$fields = str_replace($search,$replace,$fields);

list($product_line, $product_master_category, $product_category) = split('[|]', $fields[3]);

$path = pathinfo($fields[4]);
$product_image_path = $path['dirname'] . "/";
$product_image = $path['basename']; 
  
$product_vendor = "doba";    
$product_sku = $fields[1];   
$product_quantity = $fields[17];  
$product_manufacturer = $fields[5];  
$product_name = $fields[2]; 
$product_description = $fields[6] . '<br />' . $fields[7];  
$product_price = $fields[15];  
$product_msrp = $fields[16];  
if ($fields[14] > 0) {
$product_map = "Yes";    
}
    else {
        $product_map = "No";     
    }                                           
$product_map_price = $fields[14];  
$product_weight = $fields[8];       
$product_set_ship_price = $fields[19];      
$product_added_date = $date_time_stamp;  
$product_upc = $fields[21];    
$product_metatags_title = $fields[21] . " - " . $fields[2];  
$product_metatags_keywords = $fields[21] . " - " . $fields[2];  
$product_metatags_description = $fields[6]; 

//////////// Begin Products Section //////////////////

//////////// Delete all entries from the database ///////////////
mysql_query("DELETE FROM products WHERE product_vendor = 'doba'");                            
/////////////////////////////////////////////////////////////////////////////////////////   

$query = "INSERT INTO products VALUES ('','$product_vendor','$product_model','$product_sku','$product_status','$product_quantity','$product_manufacturer','$product_name','$product_line','$product_master_category','$product_category','$product_image','$product_image_path','$product_description','$product_price','$product_msrp','$product_map','$product_map_price','$product_weight','$product_height','$product_width','$product_length','$product_set_ship_method','$product_set_ship_price','$product_quick_ship','$product_added_date','$product_upc','$product_asin','$product_ebay_cat_id','$product_metatags_title','$product_metatags_keywords','$product_metatags_description')";
    $results = mysql_query($query);
if (!$results) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
     
/////////////////////////////////////////////////////////////////////////////////////////   
    }
}
echo $row_counter . ' rows were processed.';
}
?>

webent · August 25, 2008

I found this, I think it might be what I'm looking for...

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;

LOL... now I just have to upgrade my version of mysql from MySQL 4.1.22 to MySQL 5.0.3

webent · August 25, 2008

Well, I've read the instructions, and I thought that I had done it correctly, but I must be mistaken,... Here's my code...

if(!isset($_POST['file_name']))	{
$dirname = "/home/webzcart/public_html/resource/";
echo '<form action="' . $_SERVER['php_self'] . '" method="post">';
$pattern="(\.csv$)";
$files = array();
    if($handle = opendir($dirname)) {
    echo '
    <table style="width: 300px; margin-top: 50px;">
      <tr>
  	    <td>
    <select name="file_name" size="10" width="30">';
    while(false !== ($file = readdir($handle)))	{
    if(eregi($pattern, $file))
    echo '<option value="' . $file . '">' . $file . '</option>';
										    }
    echo '</select>
    </td>';
    closedir($handle);
    }
echo '<td width="20"> </td>
<td><input type="submit" name="fields_defined" value="Import"></td>
  </tr>
</table>
  </form>';
}
if(isset($_POST['fields_defined']))	{
$date_time_stamp = date("Y-m-d h:i:s");
$row_counter = 0;
$handle = fopen($_POST['file_name'], "r");
while (($fields = fgetcsv($handle, 0, ",")) !== FALSE) {
    $row_counter++;
    if ($row_counter == 1) { 
        // First row header removed. 
    }	else {
        
// Remove apostrophes
$search = array("'");
$replace = array("");
$fields = str_replace($search,$replace,$fields);

list($product_line, $product_master_category, $product_category) = split('[|]', $fields[3]);

$path = pathinfo($fields[4]);
$product_image_path = $path['dirname'] . "/";
$product_image = $path['basename']; 
  
$product_vendor = "doba";    
$product_sku = $fields[1];   
$product_quantity = $fields[17];  
$product_manufacturer = $fields[5];  
$product_name = $fields[2]; 
$product_description = $fields[6] . '<br />' . $fields[7];  
$product_price = $fields[15];  
$product_msrp = $fields[16];  
if ($fields[14] > 0) {
$product_map = "Yes";    
}
    else {
        $product_map = "No";     
    }                                           
$product_map_price = $fields[14];  
$product_weight = $fields[8];       
$product_set_ship_price = $fields[19];      
$product_added_date = $date_time_stamp;  
$product_upc = $fields[21];    
$product_metatags_title = $fields[21] . " - " . $fields[2];  
$product_metatags_keywords = $fields[21] . " - " . $fields[2];  
$product_metatags_description = $fields[6]; 

//////////// Begin Products Section //////////////////

//////////// Delete all entries from the database ///////////////
mysql_query("DELETE FROM products WHERE product_vendor = 'doba'");                            
/////////////////////////////////////////////////////////////////////////////////////////

$query = "LOAD DATA LOCAL INFILE '$_POST[fields_defined]'
    INTO TABLE products
      (column1,@dummy,column2,$product_vendor,column3,@dummy,column4,$product_sku,column5,@dummy,column6,$product_quantity,column7,$product_manufacturer,column8,$product_name,column9,$product_line,column10,$product_master_category,column11,$product_category,column12,$product_image,column13,$product_image_path,column14,$product_description,column15,$product_price,column16,$product_msrp,column17,$product_map,column18,$product_map_price,column19,$product_weight,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,$product_set_ship_price,column24,@dummy,column25,$product_added_date,column26,$product_upc,column27,@dummy,column28,@dummy,column29,$product_metatags_title,column30,$product_metatags_keywords,column31,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'";   

$results = mysql_query($query);

if (!$results) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
     
/////////////////////////////////////////////////////////////////////////////////////////   
    }
}
echo $row_counter . ' rows were processed.';
}

Here's the error I'm getting...

Invalid query: 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 '262,column5,@dummy,column6,1,column7,Atwater Carey,column8,Atwater Carey Pro 2.0' at line 3 Whole query: LOAD DATA INFILE 'Import' INTO TABLE products (column1,@dummy,column2,doba,column3,@dummy,column4,262,column5,@dummy,column6,1,column7,Atwater Carey,column8,Atwater Carey Pro 2.0 First Aid Kit,column9,leisure and sport,column10,hunting & fishing,column11,hunting gear,column12,262.jpg,column13,http://images.doba.com/products/2/,column14,The Pro 2.0 dramatically expands the focus of the Pro 1.0 with the addition of a versatile SAM® splint additional bandaging and burn supplies and more medications. Comprehensive supplies and a practical design make the 2.0 suitable for extended travel to remote areas. Intended for a group of no more than five people for up to fourteen days.
> Instructional Materials: > > "Ask the Expert" free mebership > Backcountry first aid book > Emergency action card > > Personal Protection Items: > > Antimicrobial towelettes > Biohazard label > CPR life mask > Two pairs nitrile protective gloves > Resealable biohazard bag 6" x 4" > > Essential tools: > > 5 1/2" EMTshears > Three patient assessment forms > Pencil > Four safety pins > SAM splint - full size 4 x 36" > Tweezer in valve > Potable aqua with PA+ > > Wound treatment: > > Three antibiotic ointments > Six antiseptic towelettes > Green soap sponge > Irrigation syringe (12cc) > Five sterile wound closures > Two sterile cotton tipped applicators > Tincture of Benzoin > > Bandage materials: > > Six adhesive bandages 1" x 3" > Adhesive tape 1" x 10 yds > Conforming gauze 3" x 4 yds > Elastic bandage 3" x 4 1/2 yds > Two fabric knuckle bandages > Micro-thin dressing 2" x 3" > Two moleskin bandages 2" x 3" > Two oval patches non-adherent 2" x 4" > Pressure wrap > Two telfa non-adherent 2" x 3" > Two trauma pads 5" x 9" > Triangular bandage > > Medications: > > Six acetaminophen tablets > Two antihistamine tablets > Six ibuprofen tablets,column15,47.02,column16,74.99,column17,No,column18,0,column19,1.600000024,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,7.38,column24,@dummy,column25,2008-08-25 01:14:25,column26,,column27,@dummy,column28,@dummy,column29, - Atwater Carey Pro 2.0 First Aid Kit,column30, - Atwater Carey Pro 2.0 First Aid Kit,column31,The Pro 2.0 dramatically expands the focus of the Pro 1.0 with the addition of a versatile SAM® splint additional bandaging and burn supplies and more medications. Comprehensive supplies and a practical design make the 2.0 suitable for extended travel to remote areas. Intended for a group of no more than five people for up to fourteen days.) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' '

webent · August 25, 2008

Is this code not correct?

$query = "LOAD DATA LOCAL INFILE '$_POST[fields_defined]'
    INTO TABLE products
      (column1,@dummy,column2,$product_vendor,column3,@dummy,column4,$product_sku,column5,@dummy,column6,$product_quantity,column7,$product_manufacturer,column8,$product_name,column9,$product_line,column10,$product_master_category,column11,$product_category,column12,$product_image,column13,$product_image_path,column14,$product_description,column15,$product_price,column16,$product_msrp,column17,$product_map,column18,$product_map_price,column19,$product_weight,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,$product_set_ship_price,column24,@dummy,column25,$product_added_date,column26,$product_upc,column27,@dummy,column28,@dummy,column29,$product_metatags_title,column30,$product_metatags_keywords,column31,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'";   

$results = mysql_query($query);

I assumed that I was supposed to use @dummy for fields with NULL values...

webent · August 25, 2008

Ok, I think I was making a stupid mistake with the last one... so here's a revamp... I have to wait a while, while my server finishes it's updates, but I hope this one will work...

$query = "LOAD DATA LOCAL INFILE '$_POST[fields_defined]'
    INTO TABLE products
    SET
    product_vendor = '$product_vendor', 
    product_model = '',
    product_sku = '$product_sku', 
    product_status = '', 
    product_quantity = '$product_quantity', 
    product_manufacturer = '$product_manufacturer', 
    product_name = '$product_name', 
    product_line = '$product_line',
    product_master_category = '$product_master_category',  
    product_category = '$product_category',
    product_image = '$product_image', 
    product_image_path = '$product_image_path', 
    product_description = '$product_description',  
    product_price = '$product_price',  
    product_msrp = '$product_msrp', 
    product_map = '$product_map',  
    product_map_price = '$product_map_price',  
    product_weight = '$product_weight',   
    product_height = '',  
    product_width = '',   
    product_length = '',   
    product_set_ship_method = '',   
    product_set_ship_price = '$product_set_ship_price',  
    product_quick_ship = '',  
    product_added_date = '$product_added_date', 
    product_upc = '$product_upc',  
    product_asin = '',  
    product_ebay_cat_id = '', 
    product_metatags_title = '$product_metatags_title',   
    product_metatags_keywords = '$product_metatags_keywords',   
    product_metatags_description = '$product_metatags_description'  
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'";

Mchl · August 25, 2008

Use Excel or OpenOffice Calc to load and modify CSV files.

webent · August 25, 2008

@Mchl Thank you for the response, but that just wouldn't be feasible, as it has to be done every few hours with about 20 different csv files... It has to be an automated process as you can imagine.

Mchl · August 25, 2008

I see.

I use CSV only for migrating data from spreadsheet based systems to database based systems, so I usually just prepare them in Calc

fenway · August 25, 2008

You can use CSV as an engine type...

webent · August 25, 2008

You can use CSV as an engine type...

I'm sorry, I don't understand what you mean fenway...

It's alright anyway, I just went to rent-a-coder and hired a person, this was pretty high priority to me and I just wasn't getting any responses regarding the proper use of LOAD DATA INFILE.

fenway · August 25, 2008

See here.

webent · August 25, 2008

Thanks fenway, as always I appreciate your help, but that is so far above my head, I'd have to go to school just to understand it...

fenway · August 26, 2008

Is this code not correct?

$query = "LOAD DATA LOCAL INFILE '$_POST[fields_defined]'
    INTO TABLE products
      (column1,@dummy,column2,$product_vendor,column3,@dummy,column4,$product_sku,column5,@dummy,column6,$product_quantity,column7,$product_manufacturer,column8,$product_name,column9,$product_line,column10,$product_master_category,column11,$product_category,column12,$product_image,column13,$product_image_path,column14,$product_description,column15,$product_price,column16,$product_msrp,column17,$product_map,column18,$product_map_price,column19,$product_weight,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,$product_set_ship_price,column24,@dummy,column25,$product_added_date,column26,$product_upc,column27,@dummy,column28,@dummy,column29,$product_metatags_title,column30,$product_metatags_keywords,column31,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'";   

$results = mysql_query($query);

I assumed that I was supposed to use @dummy for fields with NULL values...

What error do you get with this?

webent · August 26, 2008

@fenway

Invalid query: 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 '2669,column7,SpaSak,column8,SpaSak™ 6pc Spa Set,column9,bed and bath,colum' at line 3 
Whole query: LOAD DATA LOCAL INFILE 'Import'
    INTO TABLE products
      (column1,@dummy,column2,doba,column3,@dummy,column4,GFSPASAK,column5,@dummy,column6,2669,column7,SpaSak,column8,SpaSak™ 6pc Spa Set,column9,bed and bath,column10,bath,column11,bath accessories,column12,GFSPASAK_800.jpg,column13,http://images.doba.com/products/6/,column14,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.,column15,4.15,column16,24.95,column17,No,column18,0.00,column19,0.8000000119,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,8.59,column24,@dummy,column25,2008-08-26 09:46:32,column26,024409012877,column27,@dummy,column28,@dummy,column29,024409012877 - SpaSak™ 6pc Spa Set,column30,024409012877 - SpaSak™ 6pc Spa Set,column31,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES
    TERMINATED BY '

webent · August 26, 2008

Here's all the different variations that I've tried...

/*
$query = "LOAD DATA LOCAL INFILE '$_POST[file_name]'
    INTO TABLE products 
    (@dummy,$product_vendor,@dummy,$product_sku,@dummy,$product_quantity,$product_manufacturer,$product_name,$product_line,$product_master_category,$product_category,$product_image,$product_image_path,$product_description,$product_price,$product_msrp,$product_map,$product_map_price,$product_weight,@dummy,@dummy,@dummy,@dummy,$product_set_ship_price,@dummy,$product_added_date,$product_upc,@dummy,@dummy,$product_metatags_title,$product_metatags_keywords,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'
    IGNORE 1 LINES";
*/ 
/*   
    $query = "LOAD DATA LOCAL INFILE '$_POST[file_name]'
    INTO TABLE products
      (column1,@dummy,column2,$product_vendor,column3,@dummy,column4,$product_sku,column5,@dummy,column6,$product_quantity,column7,$product_manufacturer,column8,$product_name,column9,$product_line,column10,$product_master_category,column11,$product_category,column12,$product_image,column13,$product_image_path,column14,$product_description,column15,$product_price,column16,$product_msrp,column17,$product_map,column18,$product_map_price,column19,$product_weight,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,$product_set_ship_price,column24,@dummy,column25,$product_added_date,column26,$product_upc,column27,@dummy,column28,@dummy,column29,$product_metatags_title,column30,$product_metatags_keywords,column31,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'
    IGNORE 1 LINES";      
*/
/*
$query = "LOAD DATA INFILE '$_POST[file_name]'
    INTO TABLE products
    SET
    product_vendor = '$product_vendor', 
    product_model = '',
    product_sku = '$product_sku', 
    product_status = '', 
    product_quantity = '$product_quantity', 
    product_manufacturer = '$product_manufacturer', 
    product_name = '$product_name', 
    product_line = '$product_line',
    product_master_category = '$product_master_category',  
    product_category = '$product_category',
    product_image = '$product_image', 
    product_image_path = '$product_image_path', 
    product_description = '$product_description',  
    product_price = '$product_price',  
    product_msrp = '$product_msrp', 
    product_map = '$product_map',  
    product_map_price = '$product_map_price',  
    product_weight = '$product_weight',   
    product_height = '',  
    product_width = '',   
    product_length = '',   
    product_set_ship_method = '',   
    product_set_ship_price = '$product_set_ship_price',  
    product_quick_ship = '',  
    product_added_date = '$product_added_date', 
    product_upc = '$product_upc',  
    product_asin = '',  
    product_ebay_cat_id = '', 
    product_metatags_title = '$product_metatags_title',   
    product_metatags_keywords = '$product_metatags_keywords',   
    product_metatags_description = '$product_metatags_description'  
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'
    IGNORE 1 LINES"; 
*/
$query = "LOAD DATA LOCAL INFILE '$_POST[file_name]'
    REPLACE INTO TABLE products
    SET
    column1 = '$product_vendor', 
    column2 = '',
    column3 = '$product_sku', 
    column4 = '', 
    column5 = '$product_quantity', 
    column6 = '$product_manufacturer', 
    column7 = '$product_name', 
    column8 = '$product_line',
    column9 = '$product_master_category',  
    column10 = '$product_category',
    column11 = '$product_image', 
    column12 = '$product_image_path', 
    column13 = '$product_description',  
    column14 = '$product_price',  
    column15 = '$product_msrp', 
    column16 = '$product_map',  
    column17 = '$product_map_price',  
    column18 = '$product_weight',   
    column19 = '',  
    column20 = '',   
    column21 = '',   
    column22 = '',   
    column23 = '$product_set_ship_price',  
    column24 = '',  
    column25 = '$product_added_date', 
    column26 = '$product_upc',  
    column27 = '',  
    column28 = '', 
    column29 = '$product_metatags_title',   
    column30 = '$product_metatags_keywords',   
    column31 = '$product_metatags_description'  
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\n'
    IGNORE 1 LINES";

fenway · August 26, 2008

@fenway

Invalid query: 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 '2669,column7,SpaSak,column8,SpaSak™ 6pc Spa Set,column9,bed and bath,colum' at line 3 
Whole query: LOAD DATA LOCAL INFILE 'Import'
    INTO TABLE products
      (column1,@dummy,column2,doba,column3,@dummy,column4,GFSPASAK,column5,@dummy,column6,2669,column7,SpaSak,column8,SpaSak™ 6pc Spa Set,column9,bed and bath,column10,bath,column11,bath accessories,column12,GFSPASAK_800.jpg,column13,http://images.doba.com/products/6/,column14,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.,column15,4.15,column16,24.95,column17,No,column18,0.00,column19,0.8000000119,column20,@dummy,column21,@dummy,column22,@dummy,column23,@dummy,column24,8.59,column24,@dummy,column25,2008-08-26 09:46:32,column26,024409012877,column27,@dummy,column28,@dummy,column29,024409012877 - SpaSak™ 6pc Spa Set,column30,024409012877 - SpaSak™ 6pc Spa Set,column31,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES
    TERMINATED BY '

Those aren't column names, those are field values.... what gives/

webent · August 26, 2008

You mean like this?

    $query = "LOAD DATA LOCAL INFILE '$_POST[file_name]'
    INTO TABLE products
      (product_id,@dummy,product_vendor,$product_vendor,product_model,@dummy,product_sku,$product_sku,product_status,@dummy,product_quantity,$product_quantity,product_manufacturer,$product_manufacturer,product_name,$product_name,product_line,$product_line,product_master_category,$product_master_category,product_category,$product_category,product_image,$product_image,product_image_path,$product_image_path,product_description,$product_description,product_price,$product_price,product_msrp,$product_msrp,product_map,$product_map,product_map_price,$product_map_price,product_weight,$product_weight,product_height,@dummy,product_width,@dummy,product_length,@dummy,product_set_ship_method,@dummy,product_set_ship_price,$product_set_ship_price,product_quick_ship,@dummy,product_added_date,$product_added_date,product_upc,$product_upc,product_asin,@dummy,product_ebay_cat_id,@dummy,product_metatags_title,$product_metatags_title,product_metatags_keywords,$product_metatags_keywords,product_metatags_description,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'
    IGNORE 1 LINES";

That gives me this error...

Invalid query: 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 '2669,product_manufacturer,SpaSak,product_name,SpaSak™ 6pc Spa Set,product_' at line 3 
Whole query: LOAD DATA LOCAL INFILE 'bed_and_bath_8_21_08.csv'
    INTO TABLE products
      (product_id,@dummy,product_vendor,doba,product_model,@dummy,product_sku,GFSPASAK,product_status,@dummy,product_quantity,2669,product_manufacturer,SpaSak,product_name,SpaSak™ 6pc Spa Set,product_line,bed and bath,product_master_category,bath,product_category,bath accessories,product_image,GFSPASAK_800.jpg,product_image_path,http://images.doba.com/products/6/,product_description,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.,product_price,4.15,product_msrp,24.95,product_map,No,product_map_price,0.00,product_weight,0.8000000119,product_height,@dummy,product_width,@dummy,product_length,@dummy,product_set_ship_method,@dummy,product_set_ship_price,8.59,product_quick_ship,@dummy,product_added_date,2008-08-26 10:55:50,product_upc,024409012877,product_asin,@dummy,product_ebay_cat_id,@dummy,product_metatags_title,024409012877 - SpaSak™ 6pc Spa Set,product_metatags_keywords,024409012877 - SpaSak™ 6pc Spa Set,product_metatags_description,Set includes: natural exfoliating loofah, nylon mesh sponge, pumice stone, 3 wooden massagers and storage bag.) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES
    TERMINATED BY '
'
    IGNORE 1 LINES

fenway · August 26, 2008

You don't understand what I'm saying... that list is the list of COLUMN NAMES, not the list of values.

webent · August 26, 2008

Do you mean like this,

    $query = "LOAD DATA LOCAL INFILE '$_POST[file_name]'
    INTO TABLE products
      (@dummy,$product_vendor,@dummy,$product_sku,@dummy,$product_quantity,$product_manufacturer,$product_name,$product_line,$product_master_category,$product_category,$product_image,$product_image_path,$product_description,$product_price,$product_msrp,$product_map,$product_map_price,$product_weight,@dummy,@dummy,@dummy,@dummy,$product_set_ship_price,@dummy,$product_added_date,$product_upc,@dummy,@dummy,$product_metatags_title,$product_metatags_keywords,$product_metatags_description) 
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'
    IGNORE 1 LINES";

If so, that gave the same error... or is that not what you meant either?

fenway · August 26, 2008

The error you posted above shows DATA in the column specification! These should be column names only -- you shouldn't have variables there.

webent · August 26, 2008

fenway, from what I can tell, the csv file has to be in the same format as the table that it's being inserted into... so I devised a plan... check it out... pretty proud of myself... I convert the csv file into a csv file that matches the table and then LOAD DATA LOCAL INFILE ... It works, just everything is in the wrong columns, the commas are throwing it off, but that's just tweaking I'm sure on the conversion part...

$new_field = array();
$new_field["product_id"] = '';    
$new_field["product_vendor"] = $product_vendor; 
$new_field["product_model"] = '';
$new_field["product_sku"] = $product_sku; 
$new_field["product_status"] = ''; 
$new_field["product_quantity"] = $product_quantity; 
$new_field["product_manufacturer"] = $product_manufacturer; 
$new_field["product_name"] = $product_name; 
$new_field["product_line"] = $product_line;
$new_field["product_master_category"] = $product_master_category;  
$new_field["product_category"] = $product_category;
$new_field["product_image"] = $product_image; 
$new_field["product_image_path"] = $product_image_path; 
$new_field["product_description"] = $product_description;  
$new_field["product_price"] = $product_price;  
$new_field["product_msrp"] = $product_msrp; 
$new_field["product_map"] = $product_map;  
$new_field["product_map_price"] = $product_map_price;  
$new_field["product_weight"] = $product_weight;   
$new_field["product_height"] = '';  
$new_field["product_width"] = '';   
$new_field["product_length"] = '';   
$new_field["product_set_ship_method"] = '';   
$new_field["product_set_ship_price"] = $product_set_ship_price;  
$new_field["product_quick_ship"] = '';  
$new_field["product_added_date"] = $product_added_date; 
$new_field["product_upc"] = $product_upc;  
$new_field["product_asin"] = '';  
$new_field["product_ebay_cat_id"] = ''; 
$new_field["product_metatags_title"] = $product_metatags_title;   
$new_field["product_metatags_keywords"] = $product_metatags_keywords;   
$new_field["product_metatags_description"] = $product_metatags_description; 


$fp = fopen('file.csv', 'w');

foreach ($new_field as $line) {
    fputcsv($fp, split(',', $line));
}

fclose($fp);

$query = "LOAD DATA LOCAL INFILE 'file.csv'
    INTO TABLE products   
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\r\n'";

BTW: WOW, it was fast!

fenway · August 26, 2008

Wasn't it in a CSV file?

webent · August 26, 2008

Wasn't it in a CSV file?

Yes, but not in the same columnar format as the db table.

Sign In

[SOLVED] csv loading structure into LOAD DATA INFILE

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information