Importing CSV to mysql database losing content

Question

I have an Excel file (.xlsx format) and I want to import it to my database.

I first saved it in .csv format and I checked spelling because I have some fields with text that is formatted for Romanian language with diacritics and it's OK.

Then I made the table with the column names and imported the table using phpMyAdmin selecting Character set of the file: utf-8 and the format using Format: CSV using LOAD DATA.

It loads fine except I lose all Romanian diacritics and I have a column with text that doesn't import all text, it stops if he finds a Romanian letter ex: (ă,â,î,ș,ț). What formatting should i use? for Romanian language is recommended utf-8. Edit 1: OK, Wilk helped me with the solution and i think it's this:

SET NAMES utf8; 
LOAD DATA INFILE '/home/public_html/db/test_db.csv' INTO 
TABLE test_db FIELDS 
TERMINATED BY ';' 
ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n';

But now i have a new error #1045 - Access denied for user 'user'@'localhost' (using password: YES). :)

The column in the table you made that stores the text has the character set to utf8? — Stephan
– Stephan, Commented Jun 21, 2013 at 7:52
did you tried SET NAMES utf8 before doing the LOAD DATA ? — Wilq
– Wilq, Commented Jun 21, 2013 at 7:54
@tmanolescu you should execute it before load data in the same mysql session — Stephan
– Stephan, Commented Jun 21, 2013 at 7:57

Wilq · Accepted Answer · 2013-06-21 08:17:19Z

3

Try:

SET NAMES utf8

Put it in line before LOAD DATA

Your full SQL should look like:

SET NAMES utf8
LOAD DATA INFILE 'file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

Remember, that FIELDS TERMINATED BY operator is your field seperator

edited Jun 21, 2013 at 8:17

answered Jun 21, 2013 at 7:58

Wilq

2,2674 gold badges33 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tmanolescu Over a year ago

I am using import function from phpMyAdmin, what would be the correct slq? Something like this? SET NAMES utf8 LOAD DATA LOCAL INFILE '/home/temp/testdb/test_db.csv' INTO TABLE test_db FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

tmanolescu Over a year ago

ok right now my file is on the server in home/public_html/db so the correct path would be:

SET NAMES utf8 LOAD DATA INFILE 'home/public_html/db/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

??

tmanolescu Over a year ago

@Wilq y, forgot to put it sorry :P

Collectives™ on Stack Overflow

Importing CSV to mysql database losing content

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related