Help With Bulk CSV Upload

amarcus

Hey guys,

I have been racking my brain trying to work this one out. I have searched everywhere and couldn't find anything...hopefully you guys can help 🙂

What is required:
A user uploads a CSV file which contains headers as the first row. The user is then taken to a second page whereby they have to map the CSV file headers with the database fields...example:
Filename: test.csv

name,age,sex,postcode
josh,25,m,3333
lisa,26,f,2222
john,33,m,2223
etc...

A database table users contains the following fields: POSTCODE,NAME,SEX

Therefore, the user must be able to specify that the database field POSTCODE is equal to the last field of the CSV etc...

If anyone knows how to do this...it would be greatly appreciated...

all i need is pseudocode not actual PHP.

thanks

laserlight

Take a look at: [man]fgetcsv/man.

EDIT:

Therefore, the user must be able to specify that the database field POSTCODE is equal to the last field of the CSV etc...

This is a little confusing though. My interpretation is that you want to use the first line of the CSV file to determine the format, not get the user to manually specify it. If so, then it is a matter of constructing:

INSERT INTO users (name,age,sex,postcode) VALUES ('?','?','?','?')

where "name,age,sex,postcode" is taken straight from the CSV file, then you bind the corresponding values of each line of the CSV file to the statement parameters.

Sxooter

Yet another case of "If you were using pgsql you'd be done by now...

copy table (col3, col1, col2) from /tmp/myinputfile.csv with CSV;

tada!

Barring that, yeah, fget_csv is a pretty good choice.

amarcus

laserlight wrote:
Take a look at: [man]fgetcsv/man.

EDIT:

This is a little confusing though. My interpretation is that you want to use the first line of the CSV file to determine the format, not get the user to manually specify it. If so, then it is a matter of constructing:
INSERT INTO users (name,age,sex,postcode) VALUES ('?','?','?','?')
where "name,age,sex,postcode" is taken straight from the CSV file, then you bind the corresponding values of each line of the CSV file to the statement parameters.

My problem is that the CSV headers do not match with the field names from the database....this is my biggest problem so far..

laserlight

My problem is that the CSV headers do not match with the field names from the database....this is my biggest problem so far..

That does not matter, since by constructing the query you are arranging the order in which the columns are specified. Presumably PostgreSQL does the same.

Sxooter

amarcus wrote:
My problem is that the CSV headers do not match with the field names from the database....this is my biggest problem so far..

Wait, you mean they're out of order, or the names aren't the same?

Out of order is easy (in pgsql) as I mentioned above, because you make a simple one line copy command with them re-ordered the way you need.

If you're using php's fget_csv() then you do the reordering when you create your insert statements.

If the names don't exactly match up, then that's a whole other kettle of fish. Are they at least consistent?