importing csv file into mysql

[yahoo182]

Hi I have a rather big csv file (exported from excel) and I am trying to import this into mySql db with the follwoing code.

mysql > load data local infile '/home/kkohakur/Hotel_All_Active07-05-05.csv'
    -> into table hotelinfo
    -> fields terminated by ','
    -> lines terminated by '\n'
    -> (HotelID,Name,AirportCode,Address1,Address2,Address3,City,StateProvince,Country,PostalCode,Longitude,Latitude,LowRate,HighRate,MarketingLevel,Confidence,HotelModified,PropertyType);

This code imports the data correctly, but only up till a 1000 records/rows, and I have a lot more records in the original csv file (around 50,000).

Does anyone know how I may be able to import all of the data?

The response I get form running the command is

Query OK, 58887 rows affected (6.53 sec)
Records: 58887  Deleted: 0  Skipped: 0  Warnings: 58909

Thanks

 

[yahoo182]

I think i figured it out.
I was using mysqlCC and the default setting was to show 1000.
To overcome this, we can write something like

select * from hoteldb limit 50000

 

[TonyGroves]

You might also want to pay attention to the warnings given, which probably indicate values that were not stored correctly. Are any of the fields enclosed in double-quotes? If so, you would need to say:[tt]
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'[/tt]

 

[yahoo182]

Hi there Tony.
Thanks for the tip. Does anyone know how we can actually see the contents of the warning messages?
Also, I see that the warning message is more than the rows of records actually ...

 

[TonyGroves]

You can get the warnings for the latest command by using:[tt]
SHOW WARNINGS;[/tt]

 

[yahoo182]

Hi there TonyGroves
I tried your code

fields terminated by '|' OPTIONALLY ENCLOSED BY '"'

but then the fields that are enclosed in quotes do not get imported totally into the db. What I was would like is to have them imported, but without the quotation marks around them.

Is this possible?

thanks

 

[TonyGroves]

You need to play around with the import options to suit the format of the file data. Since the data was exported from a reputable spreadsheet program, it should be valid CSV format, and you should be able to come up with suitable import options.