1

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".

Improve this question
3
  • "fields terminated by" - a char/substring which divides one value from another one. "enclosed by" - a char/substring which wraps the value and must be removed during importing. May be optional. "lines terminated by" - a char/substring which divides one line (data for one row) from another one (even when the amount of parsed columns values is less than the amount of columns). See dev.mysql.com/doc/refman/8.0/en/… Commented Mar 9, 2022 at 11:04
  • Maybe not exactly what you're asking for, but you could also just convert the CSV data file to an insert script using Notepad++ and the CSV Lint plug-in github.com/BdR76/CSVLint Commented Mar 9, 2022 at 16:05
  • Please use text for putting logs or code in your question. it makes your question more readable, and you will find your solution faster Commented Mar 10, 2022 at 8:13

1 Answer 1

Reset to default
1

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.