What's the best way to get an excel file into a mysql database?

[kuper20]

Hello,

So I have an excel file with a bunch of columns that match my mysql database and I want to import them in a speedy and easy manner. I've been doing it by saving the excel as a csv file and then specifying in phpmyadmin when doing the import that fields are separated by a comma, but now I have more fields where there are usually commas in the middle of the field and that screws everything up. It doesn't have an option in phpmyadmin to import straight from xls, so I don't know what to do now. The only other option other that CSV and CSV USING LOAD DATA is SQL, which I don't think would work very well unless I could write an entire parsing script from the excel file.

Any suggestions would be appreciated. Thanks.

[londrum]

you should still be able to do it exactly the same way. if there's a comma in the cell then it saves the whole lot inside quotation marks, and when it uploads to the database the quotation marks disappear.

that's what happens with mine anyway, and it seems to work alright.

[kuper20]

Well the thing is csv files aren't surrounded by quotes, the fields are terminated and surrounded by commas - csv(comma seperated values). well let me tell you how my phpmyadmin import works. There are three ways to import. CSV, CSV using LOAD DATA, and SQL. The two CSV ways have four boxes I can change, with the current default values displayed.

Fields terminated by: ;
Fields enclosed by: "
Fields escaped by: \
Lines terminated by: auto
Column Names:

I change fields to be terminated and enclosed by commas, and make lines terminated by "+". (I make the last column in my excel file a column of +'s so that when it does mess up it doesn't follow to the next row.) This works for everything except fields with commas, which makes sense. If I leave it as default it doesn't do anything, and I can tell by opening the csv file in wordpad, and it's really just the fields separated by commas.

[londrum]

when i save my spreadsheet it wraps the individual cells inside a quote.
so if these are three of my cells...

cell 1: apples
cell 2: bananas, oranges, pears
cell 3: grapefruit

when i save it as a comma delimited .csv file, it comes out as this:

apples,"bananas, oranges, pears",grapefruit

and i can import that to the table as it is.

maybe it is something to do with what the cell contains. mine are all text fields. are yours numbers or something like that?

[kuper20]

ok so mine does that too actually, i didn't look very carefully. i don't know why phpmyadmin isn't working for it

[kuper20]

nvm that did fix it I just forgot to empty the table...oops

thanks for the help