August 17th, 2000, 11:31 PM
i got an excel file with a table which contains 3 columns, ARTIST TITLE & GENRE.. for example:
METALLICA LOAD ROCK
METALLICA RELOAD ROCK
MEGADETH YOUTHANASIA HEAVY
the thing is that i wanna pass the 11450 files to a table in mysql database. i've latest phpmyadmin and it has an option 4 making that... the thing is that i copy the text contained in all files and i paste in a text file but when i exec command in phpmyadmin all the row enters in one field... i've learn that it has an option which allows phpmyadmin to recognize which data to insert in which field.. it's a char separator. So, if i specify a ";" as a separator my table must be like...
METALLICA; LOAD; ROCK;
METALLICA; RELOAD; ROCK;
MEGADETH; YOUTHANASIA; HEAVY;
I know that if i do this the data will be inserted right, but how can i do to put a ";" at the end of each data (of each column) in each row ? There're 11000 rows more or less... i dunno anything about excel.. but....
IS THERE A BETTER WAY TO TRANSFER THIS DATA FROM EXCEL TO A TABLE IN MYSQL ?
Thanks in advance
Yo gotta help me plz... i'm becoming MAD !!
Henry - Argentina
August 18th, 2000, 01:04 AM
Well, there has to be some way to export the cells inside one datasheet to a text file. Try under the "File" menu. You didn't specify which version of Excel you are using, so I really cannot help you that much.
On a side note, if you have Excel, then you probably have Access as well. Why don't you try importing the table into Access and the using Access' wizards to export an ordinary char-delimited text file??
August 18th, 2000, 01:06 AM
I use the export function in excel to export a tab or comma delimited text file. Once I have that I go to the phpmyadmin "insert text files". For the fields terminated by use your delimiter (tab t, comma ,), the others I leave blank except for the lines terminated by which I leave at n
Something else I've run into on different mysql server setups is that you have to modify the phpmyadmin file ldi_check.php3 there is a command "stripslashes" that is part of the Load data infile command. On some servers this doesn't seem to matter, on others this command causes mysql not to understand the data it is receiving and it gets corrupted. If you remove this command from the script the data loads with no problem.
If your problem continues try the second solution.
August 18th, 2000, 06:19 AM
I can't speak about phpMyAdmin as I've never used it, but if none of the above works, you could export from excel into a delimited file and then hack up a small php script that reads the file in line by line and builds up the SQL inserts.
August 18th, 2000, 11:48 AM
the above replies are correct, as I've done this a million times.
1. Save or export Excel spreadsheet as a tab/pipe delimited text file. whatever, as long as its delimited.
2. write the php script to open the file, and read each line in, then use something like split() to break each line up into an array.
3. do unto the array elements what you will!
August 18th, 2000, 01:30 PM
You could also try using the pipe character as your delimiter when you export from Excel. (Usually, all my rows have commas, semicolons, etc. in the fields so that seems to mess things up when I import. The pipe character works every time.) "|"
From the day we're born, we're running out of time.