Importing a csv file into a mysql database
Hi there --
I recently created a database with one table in it. The table, in turn has three fields. Listed below are the characteristics of the fields: Quote:
Quote:
The version of MySQL that is being used here is 5.0.45. Does anyone have an idea as to why this is occurring, and how I can correct it? Thanks. |
There aren't any commas in the excerpt, so how is this a comma separated value (csv) file?
Forrest |
CVS File
Example of a CVS file
Code:
Field Type Code:
"2";"2.800,1 - 5.474";"30";"6.61";"0";"214055.75";"25" |
Actually, that is a semi-colon separated value file (and I think you meant csv, not cvs which is a version control system). Also, int's and double's wouldn't have quotes. A cvs, or comma separated value, file would look like:
Code:
2,"2.800,1 - 5.474",30,6.61,0,214055.75,25 HTH Forrest |
Yes, it's a semi-colon format, didn't change it in phpmyadmin when i did the export, but is the same logic.
And sorry, was CSV not CVS, typo there :D. I'm from venezuela so format number here is xxx.yyy,zzzz, meaning like one thousand would be 1.000,00 |
@forrest
Quote:
(do as I say, not as I do, iirc) Anyway, to be serious: @pliqui : show us a few lines of the actual(!) data file, the SQL cmd/msgs and use a desc cmd on the table. If you had no errors(!), but no data appears, I'd say you used the wrong table name or schema (db) name. |
pliqui is not the one who posted the question. I think he/she only tried to explain
I admit, I was also a bit confused ;) |
chrism01, I guess typing cvs and csv so many times made my fingers confused (what's that thing about bedeg aale to rsed sasefkeig as ladg as tke fiast and ladt leasdrs are corsdesct?)
Anyway, pliqui isn't the one w/ the question or the csv (hey, I got it right that time) file, kaplan71 is. However, (s)he doesn't seem to be interested in the thread, so the entire exercise seems moot at this point. Forrest |
Hi there --
Sorry about the delay in getting back to you all. I am definitely interested in the thread, so if I was not prompt in replying, it was completely accidental. I was using a web interface to do the job, and that did not seem to work. The next step was to try the command listed below: Code:
load data local infile '/home/ahk/August2009-1.csv' into table offsite_archive Quote:
|
Be aware that the filename is case sensitive. Further, because you're using local, the mysql client looks on the machine on which it's running for the file and it needs to be able to access it (permissions?).
|
Hi there --
I have made some progress. Once I changed the permissions on the file, I ran the commands again. This time, the first line of the file was imported into the database, but not the remaining lines. Just to recap: The file is saved as a csv file, and can be opened within Microsoft Excel, or OpenOffice Spreadsheet. Any ideas? Thanks. |
The lines might not be terminated by '\n'. So it reads the whole file as one line and there's only space for a limited number of fields and the rest is ignored.
|
Hi there --
The removal of the "lines terminated by '\n'" code made the difference. Thanks again to everyone for the help. |
Sorry i couldn't answer before but had some production problems.
I'm at home right now and don't have the desc of the table, but what i did was an export of the table with phpmyadmin and saved the file as csv. Phpmyadmin allows to change the paramater for the separator, so could be ',', ';','|', etc. the default one is ';'. But glad you could resolve your problem. Isaac Abouganem |
All times are GMT -5. The time now is 02:08 AM. |