asked on 1/4/2009

how do i upload large csv file to phpmyadmin?

hi,

i have a database table structure all set up in phpmyadmin but with no records in it. all the records are in a 200MB .csv file (67MB when zipped) on my local drive. i'd like to find a simple way of uploading this data as i'm going to have to re-upload files of this size fairly frequently and the phpmyadmin import tool has a limit of about 50MB (and it doesn't seem to work when i try to upload zipped files). i've tried using the free 'bigdump' tool to split the huge csv file up but i think it only works with .sql databases and not .csv files?

i'd be very grateful for any advice!
thanks,
liz

p.s. it's getting late here in london so i'm not going to last much longer but i'll check back first thing in the morning!

MySQL Server

Last Comment

liz1976

1/4/2009

Roxxor

1/4/2009

You could use something like (SQL command):

LOAD DATA LOCAL INFILE 'c:/file.csv' INTO TABLE yourtable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Open in new window

k_murli_krishna

1/4/2009

Refer to below links. You will get some more ways of doing it.
http://www.programmingado.net/a-629/Importing-CSV-file-into-SQL-Server.aspx
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
http://www.cryer.co.uk/brian/sqlserver/howtoimportcsv.htm
http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html

liz1976

1/4/2009

ASKER

thanks both for your replies. i've been trying to upload the file via the attached sql statement and am not having any luck. i keep getting the following error message:

#2 - File 'c:/testliz.txt' not found (Errcode: 2)

i got the same error message when i tried the following variations:
'c://testliz.txt'
'c:\testliz.txt'
'c:\\testliz.txt'

any ideas? also is there any way to upload a .zip file rather than a .txt file, just to save time?

thanks,

liz

LOAD DATA LOCAL INFILE 'c:/testliz.txt'
INTO TABLE DataNew
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Open in new window

Roxxor

1/4/2009

Is the path to the file correct?

I cannot remember since I last imported a cvs file to a database, but maybe you should use double slashes, like c://Document and settings//...//testliz.txt in the path. Try it!

You could also test by saving the cvs data to a plain text file (you can do it with Excel and then import it to mysql with:

LOAD DATA INFILE 'path/testliz.txt ' INTO TABLE yourtable

liz1976

1/4/2009

ASKER

hi roxxer,

thanks for your comment again but that's what i was trying. the file is definitely in the right place and i've tried all the combinations of back and forward slashes i can think of. i'm still having a real nightmare actually - have been trying to get the bigdump tool to work most of the day and it doesn't seem to like my csv file. i'd be grateful if anyone had any other ideas...

thanks again,

liz

SOLUTION

Roxxor

1/4/2009