Home > Enterprise >  OPTIONALLY ENCLOSED BY '"' makes \\n become invalid
OPTIONALLY ENCLOSED BY '"' makes \\n become invalid

Time:09-27

I tried to load a csv file into database using Python:

SQLCMD = "LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv' \
INTO TABLE salaries.salaries \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' \
LINES TERMINATED BY '\\n' \
IGNORE 1 ROWS"

When I run it, I got the error in line 4:

SyntaxError: unexpected character after line continuation character

I know the error is related to \\n because it's black(I use Jupyter notebook) and other parts are all red.

I think the reason is because the double quote in this part:

OPTIONALLY ENCLOSED BY '"'

This makes Python think the entire part ends here:

"LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv' \
    INTO TABLE salaries.salaries \
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"

But OPTIONALLY ENCLOSED BY '"' is a must, I need this part because some columns contain commas.

How can I solve this problem?

CodePudding user response:

This issue is caused by the " in OPTIONALLY ENCLOSED BY '"' ending the SQLCMD string. You could fix this by using a backslash to escape the quote, but it'd be easier to use a triple-quoted string to avoid having to escape the quotes altogether:

SQLCMD = """LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv' 
INTO TABLE salaries.salaries 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS"""
  • Related