How i can import a file(csv/excel) with partial data to table in database through phpmyadmin?

Question

I needed from my client updated information to fill a clients table.

I exported the table that i wanted to an excel file, and asked them to fill with the new information (it was only a column that i needed updated) and they've sent me back the file.

Now I want to import that information back to my table column.
Trial and error many times, converted to csv the excel file and imported through phpmyadmin.

But it didnt update any column.

What am I doing wrong?

Answer 1

If you have the file in a .csv and you know some PHP, you can just write a script which loops through the file and inserts/updates the records in the database.

For example, lets say that each line in your csv is structured like this:

id,name,address,email,date

E.g:

1,bob smith,344 abc street,[email protected],2009-04-01

You could loop through it in this way:

<?php
$data=file_get_contents('your-file.csv');
//Split the file and get an array representing the lines/rows in the .csv
$rows=explode("\n",$data); 

foreach ($rows as $row)
{

   //Remove any excess whitespace from start and end of the row:
   $row=trim($row);
   $id=$row[0];
   $name=$row[1];
   $address=$row[2];
   $email=$row[3];
   $date=$row[4];
   mysql_query("UPDATE TABLE SET name='$name',....);
}
?>

Answer 2

PHP has a function called fgetcsv() to parse CSV files.

You could use that to loop through your CSV file and create MySQL update strings. Which you could execute either through mysql_query() or just to copy and paste into the Query window in PHPMyAdmin.

Answer 3

If you just need to generate UPDATE statements from CSV data, you may want to take a look at my FOSS CSV tool, CSVFix, which can do this and a lot more without you having to write any code, PHP or otherwise.

Answer 4

Hi,

we use for our clients dbTube.org . We create the import definition via graphical Ajax UI and the client use the import only part to upload and import excel sheets. Booth are PHP (graphical editor and importer)

Answer 5

This isnt an answer its more of a question.

If I have say a whole reference table, I mean row of data, like so:

Say I have one product on an online shop (making it as simple as possible).

There's a pricelist.csv file yea?

With the rows like so:

Col A (not used in Import) | Col B (Manufacturer Part code, needed to ref x ref table) | Description (not used at all | Qty (we use to update the stock quantities) | Price (we use like quantity to update prices yea?)

I hope this makes sense.

Then we query Col B (in the csv file) against the xref table, to find the product OEM code, which exits in the table xcart_products.productcode column right?

It then knows then which row to update for qty and prices.

How can this be done? (also noting we want it to go for a calculation to the price before doing it, how can this be done)?

Sorry if this seems a bit scatty, I am really stuck with this idea.

Any suggestions are appreciated, Jeremy.