CSV to mySQL

richkoi · #1 (**permalink**) 06-03-2005, 12:53 PM

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** · #2 (**permalink**) 06-03-2005, 03:36 PM

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 · #3 (**permalink**) 06-03-2005, 03:57 PM

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** · #4 (**permalink**) 06-03-2005, 04:28 PM

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 · #5 (**permalink**) 06-03-2005, 04:34 PM

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

Rich

richkoi · #6 (**permalink**) 06-30-2005, 01:04 PM

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** · #7 (**permalink**) 06-30-2005, 01:23 PM

Quote:

Originally Posted by 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

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 · #8 (**permalink**) 07-01-2005, 03:38 PM

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** · #9 (**permalink**) 07-01-2005, 04:14 PM

Quote:

Originally Posted by 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.

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 · #10 (**permalink**) 07-02-2005, 09:49 AM

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

ReviewGolf.com · #11 (**permalink**) 07-27-2005, 01:46 PM

What version of phpMyAdmin are you looking at? Most likely quite outdated. Install your own latest copy.

ceo4u · #12 (**permalink**) 02-08-2006, 05:27 PM

Visit the website below to convert from CSV to MySQL >>>

http://www.ceo4u.com/data

http://www.ceo4u.com/data

Peace,
Chris

scottymore · #13 (**permalink**) 06-27-2006, 05:40 PM

An inexpensive solution:

http://www.dbqwiksite.com/

There are a variety of products from this company which I have been very pleased with.

kgun · #14 (**permalink**) 07-05-2006, 10:19 AM

Quote:

Originally Posted by 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

You do that in 5 minutes using XAMPP. Read the first paragraphs of the front page of OopSchool in my signature.

What may be difficult is to find out where you have to store your projects. They should be stored in

c:\programfiles\xampp\htdocs\myproject

or wherever you installd xampp.

I use it with DreamWeaver and test PHP / MySQL pages by hitting F12.

Can it be easier?

I have installed Vista Beta, but not tried it on that plattform.

Atlantean · #15 (**permalink**) 09-14-2006, 03:42 PM

I'm having the same issue as the OP and I just don't understand it.

Just a couple of months ago I was doing the same operation with a CSV file from one of my merchants and inside the PHPMyAdmin CP I was able to select the file I wanted to import and I was ALSO able to choose option associated with importing a CSV file.

But now, there are no CSV options to select. What happened to them?