Newbie trying to learn php and import cvs

Walle

Hi guys,

How do you import cvs files that have different column order and name to a mysql database?

sneakyimp

I'm guessing you mean CSV files.

option 1: Write a php script
Use [man]fopen[/man] to open the file, use [man]fgetscv[/man] to read the file row-by-row. fgetcsv returns a numerically-indexed array. You can build a query from each row.

option 2: Use LOAD DATA INFILE command in mysql
E.g.:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You can read the mysql manual on this. It says:

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.

Walle

Thansk sneakyimp
Sorry, i mean CSV files.

I have try with LOAD DATA INFILE but can’t get it work when column have different order in csv-files. If I first sort data from all csv-files by column order and copy them to a another file then LOAD DATA INFILE works. With columns that i have in database.

I found this earlier on php.net

<?php 
$handle = fopen('somefile.csv', 'r'); 
if ($handle) 
{ 
    set_time_limit(0); 

//the top line is the field names 
$fields = fgetcsv($handle, 4096, ','); 

//loop through one row at a time 
while (($data = fgetcsv($handle, 4096, ',')) !== FALSE) 
{ 
    $data = array_combine($fields, $data); 
} 

fclose($handle); 
} 
?>

its work and solves problem with column order but not if name is different. Maybe not possible if column name is different in different csv? And with different column-order?

problem is csv-files i need to import have different columns order on it, and some even with different name. If csv is with different column name i change them manually so it can be import to correct column in database and gues this is not best way to do.

Can this made smoother somehow? When file have different column order and some with different column name?

Weedpacket

Sneakyimp explicitly showed how to rearrange the column order when importing CSV files, so perhaps there's something wrong with the way you tried to do it?

The names of the columns shouldn't really matter, because it's legitimate to have CSV files with no column names at all.

Walle

I’m not sure if i was clear enough, but a example, let’s say you have 3 file structure like this

first example
CSV 1
col1, col2, col3
data, data, data

CSV 2
col2, col1, col3
data, data, data

CSV 3
col3, col1, col3
data, data, data

in database
col1, col2, col3

With this structure example I have no problem to import even columns are in different order, as you can see above, but, if name of columns are mixed as below it problem to import if not match column in database. First example below no problem but CSV 2 and CSV 3 will be problem to import because column not matching column in database.

Second example
CSV 1
col1, col2, col3
data, data, data

CSV 2
aaa, bbb, ccc
data, data, data

CSV 3
ddd, eee, fff
data, data, data

Or can this still be done somehow with php anyway? Some example would be nice. I run 1 file at time automatic in a script but if column name in some CSV file its not same as I have in database then data from this file will messed up in database column. Only file in this example that will works will be CSV 1, and files in first example

sneakyimp

OK here it is again:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

where it says 'col1, col2, ...' etc. in the sql statement you would need to re-order those columns to account for the out-of-order columns in your CSV file. If your spreadsheet had them in reverse order, it would be:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col3, col2, col1);

Try reading the documentation.

If you want to do the fgetcsv approach, then you are going to have to write your own query to insert the data. I have no idea why you would use [man]array_combine[/man] when you have told us (three times now) that your CSV fields are out of order. Not sure what to tell you except that you are going to have to think a little harder about this if you want a solution.

Walle

That’s not work. If it is only about one csv-file than it work and i already did something like you show before. But here it is more than one file and depends on csv-file, it have different order on columns and deferent column name depends on each file.

Also to get it work no matter how order is in csv it read line by line and match data with column in csv. LOAD DATA INFILE can’t do that as I know. I using that script I posted earlier and also tested LOAD DATA INFILE.

Maybe I missing something but how do you mean LOAD DATA INFILE can insert to correct column in database if I for example have 2 file as below into table with structure as col1, col2, col3? Lets forget about different column-name and only insert 2 file with different column order with LOAD DATA INFILE

CSV1
col1, col2, col3
data, data, data

CSV2
col2, col1, col3
data, data, data

I have read documentation several time before and as I know LOAD DATA INFILE can’t do it. Unless I rewrite query depends on each file structure or change table structure on each file structure.

Walle

From one of my old testfile with LOAD DATA INFILE

$loadsql = "LOAD DATA INFILE '$file1'
IGNORE INTO TABLE newtable
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
(col1,col2,col3)
";

Weedpacket

The column names in the statement are the names of columns in the table; they won't change.

The fields in the first column in the CSV will be loaded into the first table field named in the load statement. The second column in the CSV will be loaded into the second table field named in the load statement, and so on.

If the first line of your CSV file has column names in it, then you need to add "IGNORE 1 LINES".

If you mean that some of your CSV files have their columns in the wrong order, so that, for example, the second column in the CSV is what should go in the first column in the table, then you would need to do some work first. I suggest you read each CSV file in using fgetcsv, creating a new temporary file and using fputcsv to write all the rows (except the headers!) from all the rows of all the files in the right order into that new file. Then LOAD that file.

Walle

I just got an idea that can work LOAD DATA INFILE with no matter order is in csv. If its work LOAD DATA INFILE is absolutely best and much faster than read line by line. Idea is get first line from csv and use it somehow in LOAD DATA INFILE.

Example if first row (column in csv) is like this: blue, red, green and make some variable of it?

Example
(db table with blue, red, green)

$colum = "blue, red, green"; // this change depends on csv to csv

$loadsql = "LOAD DATA INFILE '$file1'
IGNORE INTO TABLE newtable
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
($column)

Maybe this work, but I don’t know how to get first row from csv, any idea or example? Or is this not works anyway?