LOAD DATA INFILE/ Auto Increment ID field question

[way2many]

Hi,
here is the picture:

website (PHP/MySql) will give access to many existing vendors & partners to upload their large inventories of electronic components on daily/weekly basis.
Excel or text files (from several hundred to several thousands records each) have to be uploaded to our server and after that inserted into MySql DB.
I like LOAD DATA INFILE for its speed but I have no clue how to supply values for Auto Increment ID field in our case.

List of fields that will be supplied in text data files:
VendorID
PartNumber
PartDescription
PartManufacturer


I have tried INSERT but it is too slow.

Advices or helpful links would be appreciated.

Thank you

 

[azzazzello]

NULL will work as an autoincrement ID value. It will autoincrement properly

 

[sleipnir214]

Or if you specify columnnames in the LOAD TABLE query and don't mention the auto_increment columnname, MySQL should automagically generate the IDs for you.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[way2many]

Thank you azzazzello & sleipnir214 !

I kind of figure out that NULL works for autoIncrement field.
I have tried column names like this:

mysql_query('LOAD DATA INFILE "C:/data2a.csv" INTO TABLE parts_try (PartSKU,PartDescription) FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());

and got this:

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 'FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n"' at line 1

 

[azzazzello]

soooo...it's working now?

 

[sleipnir214]

way2many:
Your query's no good. According to

http://dev.mysql.com/doc/mysql/en/load-data.html

, the column names specification appears at the end of the query, not in the middle.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[way2many]

Yes sleipnir214,
you are correct!

Got it working.
It is so fast!

Now the problem is with inserting default date.
I have tried timestamp with NOW() as default value but after altering MyISAM table default value changes to '0000-00-00 00:00:00'

Any thoughts?

 

[sleipnir214]

Unless something has changed recently, you can't use a function as a default value. It has to be a literal value.

I suppose you could set the default value to some bogus date that should never appear in your data, then perform the import, then update those records with the bogus dates to now().


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[way2many]

Thank you for your great help!