convert excel file to mysql

[planketa]

Hi,

I've heard that phpmyadmin can convert excel files into mysql, by converting them first into csv.

Can anyone show me how?

Thanks.

[swirlee]

In Excel, save or export the file as .CSV and then import it into PHPMyAdmin as per the documentation.

[planketa]

ei swirlee, I've done it but i have errors.

my csv doesnt really have commas and quotations. i dont kow why

my excel table looks like this

name date address

when i save it as csv, it is still the same, but the extension is csv.

I'm doing this because i have a database in excel and i import it to access. because i use ASP (which really sucks, I know...) and now I want to do it on PHP and MySQL, but i dont have a software for converting Access to MySQL, all I find on the net are software which are of course shareware and does limited conversion.

Please help. thanks man.

[ruturajv]

You don't have to just SAVE AS...
But you need to EXPORT.. as CSV

[planketa]

i've tried looking for export as or anything that would export my xls file, but i cant find it.

[swirlee]

Did you try Save As...? It's right there.

[planketa]

Yes swirlee, I've tried save as, that's the first thing that I did and I got some errors when I import it to phpmyadmin, because it doesnt make it a comma delimmited file, it's just the same excel but different file extension and icon.

[swirlee]

Well it works great in my copy of Excel (2000), and has worked at least all the way back to Excel 97. Make sure you're actually choosing to save as CSV and not just typing .csv after the filename. And examine the file in a text editor to make sure it's not an Excel file (the difference should be clear). And make sure your import options are correct. If all else fails, stop using phpMyAdmin (I don't understand why people cling so dearly to this program) and just use the command line interface or any desktop MySQL client.

[planketa]

This is an example of my csv converted from excel

17,JOSEFA V. SANTOS,,2004-06-15,XPEIT,440557,

the , is of course the dellimiter, but dont i need the "" to enclose data?

and as you can see i have ,, it indicates blank data to be inserted.

the columns are idnum, name, address, date, course, certificate_num

please help.

I need to convert my excel file, because it contains 100,000 rows of info.

or if you have a free converter for an access file to mysql please send some links, i can only find shareware on the net.

I've also tried exporting a data from mysql and save it as csv, then i edited the csv and import it back to mysql. there still errors.

maybe i'll just stick to access to mysql converter, do you have freeware versions?


thank you so much.

[swirlee]

17,JOSEFA V. SANTOS,,2004-06-15,XPEIT,440557,

the , is of course the dellimiter, but dont i need the "" to enclose data?

and as you can see i have ,, it indicates blank data to be inserted.

the columns are idnum, name, address, date, course, certificate_num

You only need the data enclosed in quotation marks if that's the option you choose when importing the file. LOAD DATA INFILE is very flexible, and you can choose or omit any options you want. Really enclosing eachfield should only be necessary if the data itself has commas in it (which MySQL might mistake for delimeters).

And yes, ,, does indicate blank data. If it's blank in the Excel file, then it'll be blank in your CSV and blank in MySQL. Just make sure the column's not set to NOT NULL.

[planketa]

ei man, thanks. you're so accomodating! thanks again!