What do the following MySQL csv import query terms mean?

Question

For MySQL, I found the following query to import a CSV file:

LOAD DATA LOCAL INFILE '/example.csv'
INTO TABLE exampletable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(example_field_name,example_field_city,example_field_state);

I'm just starting to learn, so please forgive the trivial question, but I'm wondering if someone can explain the following parts of this in a simpler way than what's in the MySQL help docs:

What does this mean?

OPTIONALLY ENCLOSED BY '"'

What does this mean?

ESCAPED BY '\\'

What do the \r and \n mean in the following?

LINES TERMINATED BY '\r\n'

Thank you for your help.

Solution

Everyone thinks CSV file format is simple, it's not.... You can read it's history on wikipedia

OPTIONALLY ENCLOSED BY '"'

Some columns include the separation character. You want the following row to be loaded as 5 columns,

one, two, "three, tres, trois", four, five

ESCAPED BY '\\'

And what if your data contained a quote character?

one, two, "In France, one would say \"trois\"", four, five

LINES TERMINATED BY '\r\n'

Windows file format. Lines end with two white-space characters "carriage return" (\r) and "End of line" (\n).

Ever notice that windows notepad can't open unix files properly? That's because such files terminate lines with only the EOL character. All this dates back to the type-writer days of computing and incompatible text standards between operating systems.

Comments

[Nathan]

Hi @dchrastil, thanks for the reply. I'm still not sure about the last two. When you say "There are certain characters which aren't safe in CSV files, and those characters are escaped with the double backslashes" -- what characters are not safe? I don't understand your Denver\\, CO example.

[Nathan]

As for \r\n, I see now that this is defining that the record is terminated, but I don't understand what \r stands for and what \n stands for. I've seen some examples that only show \r, but not \n -- for exporting CSV in example. I'm just trying to get a better "lower level" understanding. Thanks.

[Nathan]

Hi Mark, thanks for the reply. I think I got it now, except for one small part. Escape by '\\' -- why two \\ instead of only one \

[Nathan]

Hi Holger. Thanks for the reply. I'm left with one small question that I asked above of Mark O'Connor. Perhaps you know the answer?

[Holger Just]

@Nathan The two backslashes are used to escape the escape sequence. If there was only one backslash like ESCAPED BY '\', the backslash would escape the closing apostrophe and thus cause a syntax error. Using an escaped backslash `\`, you indicate that you mean an actual literal backspace and not the start of a escape sequence. That means, you define that your data uses a single backslash for escapes (and not a literal double backslash as written by dchrastil).

[Nathan]

I think I understand now. So the first backslash is escaping the second one -- is that right?

[Mark O'Connor]

Yup you got it. The backslash is normally used to indicate that the character following it should be treated specially (See the others in your example \r, \n). For more history take a look at wikipedia: en.wikipedia.org/wiki/Backslash