Board index   FAQ   Search  
Register  Login
Board index php forum :: Database phpMyAdmin

convert excel file to mysql

How to use phpmyadmin.... phpmyadmin related arguments. Even phpMyAdmin issues

Moderators: macek, egami, gesf

convert excel file to mysql

Postby planketa » Sun Aug 15, 2004 11:14 pm

Hi,

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

Can anyone show me how?

Thanks.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby swirlee » Mon Aug 16, 2004 5:42 am

In Excel, save or export the file as .CSV and then import it into PHPMyAdmin as per the documentation.
User avatar
swirlee
Moderator
Moderator
 
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back

thanks

Postby planketa » Mon Aug 16, 2004 6:37 pm

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.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby ruturajv » Mon Aug 16, 2004 7:31 pm

You don't have to just SAVE AS...
But you need to EXPORT.. as CSV
User avatar
ruturajv
php-forum Super User
php-forum Super User
 
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India

thanks

Postby planketa » Mon Aug 16, 2004 11:09 pm

i've tried looking for export as or anything that would export my xls file, but i cant find it.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby swirlee » Tue Aug 17, 2004 6:02 am

Did you try Save As...? It's right there.
User avatar
swirlee
Moderator
Moderator
 
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back

Save as

Postby planketa » Thu Aug 19, 2004 4:33 pm

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.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby swirlee » Thu Aug 19, 2004 5:13 pm

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.
User avatar
swirlee
Moderator
Moderator
 
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back

csv example

Postby planketa » Thu Aug 19, 2004 10:16 pm

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.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Re: csv example

Postby swirlee » Fri Aug 20, 2004 5:39 am

planketa wrote: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.
User avatar
swirlee
Moderator
Moderator
 
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back

thanks

Postby planketa » Tue Aug 24, 2004 12:41 am

ei man, thanks. you're so accomodating! thanks again!
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines


Return to phpMyAdmin

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron