liz1976
asked on
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!
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!
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
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
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
#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';
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window