CSV to mySQL

[richkoi]

I have a CSV file I spun out from MS Excel that I would like to put in a mySQL table. I use two hosts that have different versions of phpMyAdmin and one host does not have the CSV upload option. Is there software (preferable FREE) out there that converts CSV files into mySQL table create queries so I can upload this file on the host that does not support the CSV upload?

Thanks,

Rich

[brian.mark]

Ever used mysqlfront? I've used it for that purpose in the past.

They have a 30 day trial posted here:

http://www.mysqlfront.de/download.html

Brian.

[richkoi]

I believe this program requires mySQL to be installed on my computer. I don't have a mySQL server installed on my machine. I use the servers at GoDaddy.com.

Isn't there a simple converter that takes CSV data and converts it into a table insert query that I can copy and paste into myPHPAdmin?

Thanks,

Rich

p.s. Since GoDaddy.com uses a fairly new copy of myPHPAdmin is seems like they should have a csv upload option. Am I missing something? Thanks.

[brian.mark]

I use that and don't have the server program on my PC. It connects via the net to our databases just fine.

Brian.

[richkoi]

Thanks for letting me know. I'll try it out.

Rich

[richkoi]

mysqlfront works great... Except GoDaddy.com does not allow access from anything but their web access portal!

Right now I am using another host to create my tables and then exporting them to .sql files and then importing them using the GoDaddy webportal.

What would work much better for me is if I could use localhost to do this. I tried installing mySQL and PHP on my Windows XP pro machine... but I don't know how to get it all to work together. I'm a n00b when it comes to this so any help setting this up would be appreciated.

Rich

[brian.mark]

mysqlfront works great... Except GoDaddy.com does not allow access from anything but their web access portal!

Right now I am using another host to create my tables and then exporting them to .sql files and then importing them using the GoDaddy webportal.

What would work much better for me is if I could use localhost to do this. I tried installing mySQL and PHP on my Windows XP pro machine... but I don't know how to get it all to work together. I'm a n00b when it comes to this so any help setting this up would be appreciated.

Rich

You may be able to use Putty to set up an SSH session, then use port forwarding to forward 3306 to the other end of the ssh session. That's how we do it here (we block 3306 with our firewall, then use SSH to open a port forward.) If you do a Google search for putty ssh port forward it usually comes back with some good results. Once that's set up, you just connect mysqlfront to localhost and you're good to go - it connects to the remote machine as if it is local.

Brian.

[RichardLynch]

If you have SSH access in the first place, you don't really need to muck around with port forwarding and mysqlfront, really...

1. Upload the CSV to the server, just like you would any HTML/JPG/XYZ file.

2. SSH in to the host, and use:

mysql -u YOURUSERNAME -p YOURDATABASE

You'll be prompted for your password.

Then you can use MySQL's LOAD DATA INFILE command with the path to your CSV file.

http://mysql.com will have more details about how to work this command.

You could even write some kind of shell script or a PHP script to do this automatically whenver you upload a new file to some special directory or something.

There may well be a tool out there to do what the original question asked.

I'd try to Google for:
csv2sql
and then for
"convert CSV to SQL INSERT"

If those don't turn up something, it probably doesn't exist, because everybody else found it easier to just use mysql built-in tools than make a special tool just for that.

[brian.mark]

If you have SSH access in the first place, you don't really need to muck around with port forwarding and mysqlfront, really...

1. Upload the CSV to the server, just like you would any HTML/JPG/XYZ file.

2. SSH in to the host, and use:

mysql -u YOURUSERNAME -p YOURDATABASE

You'll be prompted for your password.

Then you can use MySQL's LOAD DATA INFILE command with the path to your CSV file.

http://mysql.com will have more details about how to work this command.

You could even write some kind of shell script or a PHP script to do this automatically whenver you upload a new file to some special directory or something.

There may well be a tool out there to do what the original question asked.

I'd try to Google for:
csv2sql
and then for
"convert CSV to SQL INSERT"

If those don't turn up something, it probably doesn't exist, because everybody else found it easier to just use mysql built-in tools than make a special tool just for that.

That may be an option, but a GUI is much simpler for beginners.

I've been doing mysql stuff for 6 years now and prefer a decent gui to the command line interface. Command line works, but a typo makes it not work and a GUI prevents those.

Brian.

[Rod Swift]

I had a similar problem so I wrote a short PHP routine to open an uploaded CSV file, read each line, and load it into the database.

If you want some sample code then let me know.

Best Regards

Rod Swift