Dumping into a database

[degroat]

Let me just start of saying that this is what I'm working with....

"NOS","20154","MOVIE POSTER","REPRINT","KING KONG",0.00,0.00,27.00,40.00,29.50,"king kong;movie","1"
"NOS","25001","MOVIE POSTER","CLASSIC","ADAMS RIB",0.00,0.00,26.00,35.00,29.50,"adams rib;spencer tracy;katherine hepburn;movir","1"
"NOS","25002","MOVIE POSTER","CLASSIC","AFRICAN QUEEN",0.00,0.00,26.00,35.00,29.50,"african queen;movie;humphrey bogart;katherinte hepburn","1"


What I have is a 96,000 line CSV file that I need to get into a mySQL database. However, in addition to the fields included in the file I also need to add a few additional fields such as a an item number. (This database contains a vendor code and their item number, but we want a generic item number also).

One approach that I have considered is using "LOAD DATA", but I ran into a couple problems with that... I wasn't able to get it to work and if I did get it to work, I was afraid I would be unable to add the additional fields.

Is "LOAD DATA" the way to go? If so... can someone give me some advice on getting this to work right. If not, what should I do?

 

[sleipnir214]

"LOAD DATA" is the way to go.

What I recommend is that you create a table that has columns to store the data in the file, but with and extra auto_increment column for the item number.

For example, in a simplified scenario you want to import the following:

"poster1",5.00
"poster2",2.00
"poster3",3.00

I would create a table like:

create table poster_data (
  id int unsigned auto_increment primary key,
  name varchar(20),
  price decimal(6,2)
)

Then I would use LOAD DATA INFILE as:

load data infile '/path/to/poster_data.txt'
into table poster_data
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
(name, price)

Often, the "gotcha" is the "lines terminated by..." clause. If you hand MySQL some data and it only inserts the first line of data, then that clause is not right.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

Well... that's pretty much what I've been trying to do.

The only real difference was that I was trying to use Load Data Local Infile while using SSH since I am not able to place the CSV file on the server with the MySQL db. But, when I do that I get a message saying that the file is not found.

Here is exactly what I have typed and the error I get...
---------------------------------------------------
mysql> load data local infile 'c:\\print.csv' into table print fields terminated by ',' enclosed by '"' lines terminated by '\n';
ERROR:
File 'c:\print.csv' not found (Errcode: 2)
---------------------------------------------------------

 

[degroat]

I keep reading about "load-local-infile" and how if it is set to 0 in some versions of mySQL that it will prevent this from working, but I don't have the first clue on how to change this value to 1. Can you point me in the right direction?

 

[sleipnir214]

I've never been able to get "LOCAL" to work.

Why can't you place the file on the MySQL server?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

I may be able to, but I have to talk to my hosting co. and I haven't been able to get ahold of him. But, the guy is pretty anal about security, so I might be SOL.

 

[degroat]

Well...I think I may have figured out the path to the file, but I get the following error...

===========================================================
ERROR 13: Can't get stat of '/afgart/public_html/print.csv' (Errcode: 2)
===========================================================

Do you know what would be causing this?

 

[sleipnir214]

Either the file isn't there, or the user that is trying to open the file doesn't have permissions to the file.

I'd bet the former. "/afgart" doesn't look like a normal unix filesystem directory.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

Hmm... what is the normal format?

 

[sleipnir214]

No, your format is correct:

/directory/directory/directory


It's just that a directory called "afgart" that high in the hierarchy ("/afgart&quot is pretty unusual.

Normally, you'll see something like:

/home/users/afgart

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

Sweet! I got it to work.

One more question for you... it said 18 warnings even though it all went in just fine. Do I need to be concerned about these? If so, how do I get to them?

 

[sleipnir214]

Compare the number of records in the text file to the number of records in the database. There may have been some spurious characters somewhere that MySQL could not interpret correctly.

Generally speaking, I have the least trouble with tab-delimited files that have no quotes of any kind in them.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

You were right... it was only grabbing every other record so I changed the line termination to '\r\n'.

A few more questions for you, if you don't mind...

- I'm still getting 4 warnings... is there something I can type to get those to show?

- In all of this, at one point I accidently sent the wrong field into the print_id field you suggested above. Since that happened, the print_id is counting from ~25,000 instead of at 0. I'd like to get those values back, so is there a way I can reset this?

- Is there a way to set up a field to automatically insert today's date in the same way that print_id generates its own value?

 

[sleipnir214]

The warnings will be tough to find. There is no command to get details on which records caused the warnings.



If you are running MySQL 3.x, perform a &quot;DELETE from <tablename>&quot; and reload the data. That version of MySQL is supposed to speed deletion of all records by dropping and recreating the table.

If you are running MySQL 4.x, perform the query &quot;alter table <tablename> auto_increment = 1&quot;



If your table contains a column of type timestamp, then whenever you insert or update a record, the date/time stamp of the time of the operation will be automagically set. (

http://www.mysql.com/doc/en/DATETIME.html)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

Just to specify.. the date that I'm wanting to save is the date when the record is created and I don't want it to change automatically afterwards.

 

[sleipnir214]

If you don't want the entry to be changed when you update the record, I recommend you use a column type of datetime and set it manually. There is no way to have MySQL set that value automatically while bulk loading.

I recommend that you add a datetime column, perform the bulk upload, then perform a query like &quot;UPDATE <tablename> SET <datetime column name> = now()&quot;

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!