Page 1 of 1

How do you import a CSV into a MySQL database?

Posted: Fri Oct 13, 2017 10:49 am
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.

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

Posted: Fri Oct 13, 2017 10:55 am
by requinix
Take a look at LOAD DATA INFILE.

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

Posted: Fri Oct 13, 2017 11:00 am
by simonmlewis
Bloody hell that is a lot to read.
Surely it's more straight forward than that???

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

Posted: Fri Oct 13, 2017 3:42 pm
by requinix
It's complicated because reading CSVs, processing data, and adding to a table is a complicated process.

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

Posted: Fri Oct 13, 2017 3:46 pm
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.

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

Posted: Fri Oct 13, 2017 3:56 pm
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

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

Posted: Fri Oct 13, 2017 4:16 pm
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.

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

Posted: Sat Oct 14, 2017 8:43 am
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?