Newbie trying to import CSV data to mySQL

ollyno1uk

Hi there

I am completely new to this so forgive me,

I currently run an online shop. Some of my products are shipped from my supplier. They send me an update of stock remianing once a day. This is in the form of a CSV file.

The CSV file consists of product ID and Stock amongst some other information i do not require. I use OScommerce and in my database I have a product ID and stock amount with other fields containing my data.

There will be products in the CSV file that I don't have in my store and products in my store that is not on the CSV file.

I can access my SQL via PHPadmin from my Cpanel of my host. There is an import possibility here and places to write SQL script.

I have onl learnt the very basics of SQL So I am stuck with how to import this information. If anyone could help or advise I woul dbe most grateful.

Installer

Something like this should do it:

$csvp = fopen('data.csv', 'r');
while (($data = fgetcsv($csvp, 512, ',')) !== false) {
    $query = "UPDATE my_table SET remaining = " . $data[3] . " WHERE product = " . $data[0];
    msql_query($query);
}
fclose($handle);

This is supposing that the product id is the first field ([0]) in each csv line and in stock is the fourth ([3]), and that 512 will cover the longest line. Adjust appropriately, of course.

If the file and database are large and this method takes too much time, you'll want to check out using a temp table, and/or joins, and/or "INSERT DATA INFILE".

ollyno1uk

the database holds about 300 products. Will this method update all the records at once or will I need to manually insert each product ID?

I was intending to upload the CSV file into a random table within the databse having removed the columns in excel of the data I do not require.

Can you tell me what specifit parts I need to fill in? Is it the stuff in red?

Thaks a lot for taking the time to reply.

Installer

The code should work pretty much as it is. You'll need to make changes to fit your files and database, though. You shouldn't need to enter anything manually.

/ Connect to database first :)

$csv_file = 'csv/file'  // Put your csv path and file here.
$csvp = fopen($csv_file, 'r'); 
while (($data = fgetcsv($csvp, 512, ',')) !== false) { 
    // In the query, change my_table, remaining, and product to whatever you call them.
    // Also adjust the $data keys to the positions on the csv lines corespondingly.
    $query = "UPDATE my_table SET remaining = " . $data[3] . " WHERE product = " . $data[0]; 
    msql_query($query); 
} 
fclose($handle);

This updates only the products that are already in your database. It's not real clear from your post if that's what you want.

There will be products in the CSV file that I don't have in my store and products in my store that is not on the CSV file.

ollyno1uk

yes this is exactly what I require.

So do I replace the word remaining with the 'stock' column's name or do I say remaining = 'stock' column name?

and thats the same question with product.

If I only have 2 columns then I assume those numbers will be 1 and 2 .

And just to confirm, I will not need to write in any of the actual product ids - it will look down the list and any produc ids that match, it will fill in the stock levels?

I hope I am making myself clear?! and once again, many thanks.

Installer

Replace the word "remaining" with the "stock" column's name and the word "product" with the product column's name. If you have only 2 culumns then they will be "$data" array index numbers 0 and 1 (no quotes), instead of 0 and 3 as I made them. And yes, it will be automatic, just give it the correct path and file name for your csv file.

Glad to help.

ollyno1uk

I'mfeeling like I should give in!

I dont know if this is the place to post this issue but in mysql datavase I have created a new table. Using the PHPadmin I have attempted to upload my CSV file. It says Invalid field count in CSV input on line 1.. I have changed the file to a .txt file and still it says the same. I have then checked the .txt file and there are the values then a large space and another value. I have gone through a few and literally added a comma and a space and it still says Invalid field count in CSV input on line 1.

Where am I going so wrong?

Thanks

ollyno1uk

Ok, just an update. I have finally managed to get my file up into a table I created. The only thing Im stuck on now is what do I put as the location of the CSV file?

Thanks

SQL query: 

$csv_file = 'C:\Documents and Settings\Ollyno1uk\Desktop\weebsite\er.csv' // Put your csv path and file here. $csvp = fopen($csv_file, 'r'); 

MySQL said:  

#1064 - 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 '$csv_file = 'C:\Documents and Settings\Ollyno1uk\Desktop\weebsite\er.csv'  // Pu' at line 1