autonum key and load data file?

conjurer

I need to load a csv text file into my table. The file does not have a value field for every field of the table.

For discussion lets say the table has 12 fields. The first field is userid - a primary key that is set up to autoincrement. Fields 2-12 are various things like Name, address, etc.

The csv file starts with field 2 of my table definition. That being First name. It then matches up with fields 3-6. Field 6 is the last field in the data file.

So what I want to do is to import the file into my table populating fields 2-6. In addition I would like the primary key to assign a user_id number as the rows are added. I want to leave fields 7-12 blank, to be updated later when other data is available.

I was able to use the load data file process to get fields 2-6 imported, but it left the user_id blank.

I have tried to look at the syntax to figure this out but I don't see how to get it to also assign the userid number as it imports the data.

Can anyone help me out?

Pig

You could manually explode the file by line, and then by the delimiter. Then just run through the array and import just what you want.

conjurer

You could manually explode the file by line, and then by the delimiter. Then just run through the array and import just what you want.

Uhh, maybe you could do that. Don't think I could.:rolleyes:

Pig

use file_get_contents() to get the file into a string, then you can explode based off of "\r\n" (the newline characters)
There might be a function that does that already, but I don't recall.

Anway, then you just do explode the array you got from there, based on the delimiter to create a multidimensional array with all of your data.

You use foreach() to go through the array, and create an INSERT query using the components of the array.

I would knock out a mock up of the code for you, but I need to get some shut eye. Maybe someone will come along and give you an example.

jayant

RTFM of "Load Data ... Infile ..." query on Mysql.com

conjurer

RTFM of "Load Data ... Infile ..." query on Mysql.com

Oh Thanks Alot. I Read the Fg Manual on load data and it is just as clear as your kind explanation!

So maybe you would like to answer my question instead of insulting my intelligence. :p

jayant

do u have auto_increment for the user_id field on?

conjurer

I believe I have it "on" -- I defined the column as auto_increment in the "extra" field. Is that what you mean as "on"

I was able to upload this data and then drop the user_id field and then add the user_id back with auto increment and get the numbers in that way.

However, now that I have data in the table if I add more data down the road I am going to need it to fill the user_id field.