CODEWITHSUNDEEP
mysqlcsv
user18402670
user18402670

Reputation:

Importing .csv file in MySQL using LOAD DATA

SS of my raw data

You can see my raw data above. I'm trying to import this data on the table I've created on MySQL. Here's the code I should be using:

LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl

-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'

-> LINES TERMINATED BY '\r\n';

I can't understand which character is "fields terminated by", which one is "enclosed by" and which one is for "lines terminated by".

Upvotes: 1

Views: 481

Answers (1)

O. Jones
O. Jones

Reputation: 108841

Can you read your .csv file into Excel or LibreOffice Calc (or any spreadsheet program) correctly? I guess you probably can. That means it is formatted correctly.

.csv files contain one line of text for each row of data in a table. These LOAD INFILE directives tell MySQL how to find the rows and columns in the .csv file.

FIELDS TERMINATED BY ',' means each column of data ends with a comma. Notice your first line of data :

De Ruijterkade,,123400000001234,,1,105...

The first column is the street name. The second is empty, the third is 1, the fourth 105 et cetera.

ENCLOSED BY '"' means columns of data which themselves contain a comma (a field terminator) must be enclosed in " characters. For example, if your street name had the value De Ruijterkade, Kade your file would contain

"De Ruijterkade, Kade",,123400000001234,,1,105...

Finally LINES TERMINATED BY '\r\n' means each line in your file (row in your table) ends with a Windows-style <return><linefeed> character pair.

Akina correctly pointed out the documentation. https://dev.mysql.com/doc/refman/8.0/en/load-data.html#load-data-field-line-handling

Upvotes: 1

Related Questions