Load CSV file into MySQL

[creativodev]

Could someone please help me load a CSV file into a MySQL table.

 

I created a script that will import a CSV file to a MySQL table.

 

It does not load properly.

 

These are the columns on the table:

idkey

id

code

caption

taxable

name

sale-price

orderable

price

ship-weight

brand

 

There is data entered into the wrong column, something's happening with the quotes that is also messing up the data, and the first row entered enters column names instead of data.

 

Here is the query made by PHP that loads the data into the table:

'LOAD DATA LOCAL INFILE "/filename.csv" 

    INTO TABLE `wheels-tires`

    FIELDS

        TERMINATED BY ","

        ENCLOSED BY "\""

    LINES

        TERMINATED BY "\n" 

(id, code, caption, taxable, name, `sale-price`, orderable, price, `ship-weight`, brand)'; 

 

Here is a sample of the data on a csv file:

id,code,orderable,price,ship-weight,brand,caption,taxable,name,sale-price

2,"2",Yes,900,,"Kumho","Set of four AUTHENTIC NEW STYLE! Sport Edition KV5 Silver wheels mounted and balanced with NEW tires. The wheels and tires have never been mounted on any vehicle. The wheels are 19""x8.5"" Offset 38 (Front) & 19""x9.5"" Offset 45 (Rear), with 5x114.3 bolt pattern.1 wheel has minor scratches, please take a look at the pictures. Tires are Kumho Sumitomo HTRZIII: 245/45/19 (Front) & 275/40/19 (Rear). All pictures below are the ACTUAL rims and tires we are selling! They will fit perfectly on All Ford Mustang Models From 2005-Up. Also included are Valve Steams, Center Caps, Lug Nuts, Mounting and Balancing !We do offer !FREE SHIPPING INSIDE THE CONTINENTAL U.S.",Yes,"19 SPORT SILVER WHEELS TIRE FORD MUSTANG 2005 & UP",

1,"1",Yes,675,,"BF Goodrich","You are bidding on a 100% BRAND NEW set of four Sport Edition KV5 Silver wheels mounted and balanced with NEW tires. The wheels and tires have never been mounted to any vehicle. The wheels are 17""x8"" offset 20 (All Four), with 5x120 bolt pattern. 1 wheel has minor scratches, please take a look at the picture. Tires are BF Goodrich g-Force Sport: 235/40/17 (All Four). They will fit perfectly on all BMW 5, 6, 7 & 8 Series. For other models, please check to make sure about the fitment before bidding. Center caps, Lug Bults, Mounting, Balancing & Valve Steams are also included!. !We do offer Free Shipping Inside the Continental U.S. With BUY IT NOW! If you have any questions, please give us a call at at 956-7223102 9-6 central. ",Yes,"17 SPORT SILVER WHEELS TIRES RIMS BMW 5, 6, 7 & 8 ",

6,"6",Yes,650,,"ASA","You are bidding on a 100% BRAND NEW set of four authentic ASA AR1 Black W/Machined Lip wheels mounted and balanced with NEW tires. These wheels & tires have never been mounted to any vehicle. The wheels are 17""x7"" Offset 38 (All Four) with 4x100 bolt pattern. 1 wheel has minor scratches, please take a look at the picture. Tires are Kumho ECSTA AST: 205/45/17 (All Four). All pictures below are the ACTUAL rims & tires we are selling! WHEELS AND TIRES WILL BE MOUNTED AND BALANCED! They will fit perfectly on all Mini Cooper & Cooper S Models, All V.W. Models W/4 Lug Patern & All Toyota Yaris Models. If you are not sure about the fitment, please check to make sure before bidding. This auction is also included a set of 4 Wheel Center Caps as shown in the pictures, Valve Steams, Lug Bults, Mounting & Balancing!! !We do offer FREE SHIPPING INSIDE THE CONTINENTAL U.S. With BUY IT NOW, if you have any questions, please give us a call at 956-7223102, 9-6 central. ",Yes,"17 ASA BLACK WHEELS TIRES MINI COOPER S. V.W. YARIS",

 

I just tried to escape them by loading it to a variable and using addslashes, like this:

 

 

PHP Code:

ob_start(); // start buffer

include ($newfilename);

$content = ob_get_contents(); // assign buffer contents to variable

ob_end_clean(); // end buffer and remove buffer contents

$content = addslashes($content);

echo '<div style="background-color:#FFF;">'.$content.'</div>'; 

 

but then it escapes ALL the quotes, not just the ones that i want it to escape, like this:

 

Quote:

...\"2\",,\"Kumho\",\"You are bidding on a NEW set of four AUTHENTIC NEW STYLE! Sport Edition KV5 Silver wheels mounted and balanced with NEW tires. The wheels and tires have never been mounted on any vehicle. The wheels are 19\"\"x8.5\"\" Offset... 

 

I only need it to escape the quotes INSIDE the description field.

 

Please help me. I need to finish this project by Nov 1.

 

[Barand]

the column list in the LOAD DATA statement should define the order they appear in the CSV.

 

Edit: you also need "IGNORE 1 LINES" to skip the header