Mysql Import Textfile

Hi Guys,

I have managed to import CSV files into my MYSQL DB, however there is 1 file having almost 135000 records, I am not able to import this one. when I try importing, it only imports 58000 records. Do u know why?

Thanx

Are there errors when you load it? Could the data be malformed at line 58,0001 or possibly in about 70,000 lines in the file that don't get imported?

HI Dhouston.

The data is not corrupt. I am sucessfully able to import the same file in ACCESS and it does it without any errors. THis is achieved within a minute, whereas when I try importing it to MYSQL it takes 25 mins for the import and then too imports only 58000 rows.

prav5110,

How are you importing the file, command line or GUI-based?

What's the code you're using to import the file?

Hi,

I am using a GUI called Navicat. Its really good and am having sucess importing all other tables but this one (the one with 135000 rows).

I have tried a couple of more GUI's and used importing the file with command line using the command

load data infile 'C:\\LucentNonPerfRpts.csv' into table tblluc_non_perf fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

But in all these cases it imports 29000 rows only. I am using a MYISIAM table type.

Its with Navicat that i am able to import 58000 rows,
Do u know how to resolve this?

Try breaking your file up into three files and see if you can import them separately. With nice round numbers like 29,000 and 58,000, I can't help thinking there's some limit set somewhere.

Yeah, I'm with DHouston on that... I searched the MySQL site, and couldn't find anything related to import limits; however, it does seem like a "round number" suited to a set limit.

Guys,

I have checked that out... How to break a CSV file into parts as when u open it it does not display more than 65000 Rows?

Go to row 50,000, press CTRL-SHIFT-END, and then hit delete. Save the top 50,000 rows AS a new file. Close the original doc without saving. Reopen the document. Go to row 50,001, press CTRL-SHIFT-HOME, and then hit delete. Hopefully this'll shift all your rows up. If not, save as a new doc and reopen. Repeat, rinse, etc.

HI Guys,

This is not just working!!! My file with 136000 rows, I am able to import it to Ms Access DB. All rows imported perfectly and ths process takes 2 mins. When i do the same to mysql, it only imports 56000 rows and that too in 1 hr.

Could you help!!

Did you try to break it out into separate files?

Have you tried this not within a GUI? Have you tried it at the command line, I mean?

If all else fails, write a little script that reads the CSV file and writes a bunch of insert statements. It'd take a while to run, but it'd get your data in at least, provided the data are well-formed.

I'm running mysql on my Linux box. I wrote a perl script that wrote a CSV file with 7 columns and 136000 rows. I created a database and a table with seven columns and issued the following command at the mysql command line:



It imported all 136000 rows in 0.60 seconds. Definitely try this at the command line instead of through a GUI. If that doesn't work, you'll need to check your mysql settings to make sure there's not some limit set, and then you'll need to check and make sure there's not some weird limitation within mysql on Windows.