How do you import a CSV into a MySQL database?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you import a CSV into a MySQL database?

Post by simonmlewis »

We might be required to create a website whereby administrators can import a CSV into it.

I've done queries where you export a CSV, but not import.

Is it easy to do?
I know I would need to tie up the column headers in the CSV to the field headers in the database, but no idea how.

Any help would be appreciated.

Thanks.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you import a CSV into a MySQL database?

Post by requinix »

Take a look at LOAD DATA INFILE.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you import a CSV into a MySQL database?

Post by simonmlewis »

Bloody hell that is a lot to read.
Surely it's more straight forward than that???
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you import a CSV into a MySQL database?

Post by requinix »

It's complicated because reading CSVs, processing data, and adding to a table is a complicated process.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you import a CSV into a MySQL database?

Post by simonmlewis »

Often a practical solution, with some comments is better than [lots] [and] [lots] of tags on a screen that to some, is unreadable.

I had a Google for it and found a few results, with comments. Because the page you sent, sadly was just 'greek' to me. Sorry.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

Re: How do you import a CSV into a MySQL database?

Post by Vegan »

I use something like:

Code: Select all

LOAD DATA LOCAL
	INFILE 'your file goes here.CSV'
INTO TABLE
	`your database goes here`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;
everybody's favorite, SQL to the rescue

the code expects the CSV to be uploaded to the same box (VM) as the database server
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you import a CSV into a MySQL database?

Post by simonmlewis »

I Was reading about it and saw the script to look at the uploaded file.
Then the query. It seemed to ut each row into some sort of array,[0], [1]... and that went into insert script, lined up with the field names in the DB.

Into a Row.

A practical actual idea of the script is easier to learn from. That's what I mean.
Then comments to explain each part.
Rather than ... ENGINE + BRAKES + CALIPERS = Move then Stop.
An actual explanation of what the engine is doing, what the brakes are doing and so on.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you import a CSV into a MySQL database?

Post by Celauran »

If you don't want to go with the method suggested above, you can do it all in PHP if you think that would be easier to understand. You reference a script in your last post, but didn't post it. Do you have anything written yet? What are you stuck on?
Post Reply