Issue with importing CSV into Mysql

Asked

Viewed 932 times

1

I am importing csv file information into mysql database some spreadsheets were imported correctly, however some worksheets when importing to the bank appear a double quotes that does not exist in the worksheet thus affecting the position of the column information I am using the load data local infile to import the csv file.

the line on the bench trims so 

id | material| categoria  |   lote  |   mes   |
1  |   "     |   janeiro  |   null  |  null   |
2  | lápis   |  escolar   |   "5    |   null  |

note that the month appears in the category column in the worksheet this in the month column and the quotes do not have in the worksheet I have already copied to a blank sheet only with pure values and even so picks these double quotes the load this normal because some worksheets did not give these problems the code of the load this below:

$sql = "LOAD DATA LOCAL INFILE 'C:/inetpub/wwwroot/report/uploads/$campo'
                            INTO TABLE reparo.oob
                            character set 'utf8'
                            FIELDS TERMINATED BY ','
                            LINES TERMINATED BY '\r\n'
                            IGNORE 1 LINES
                            (@FAMILIA,@BRAND,@QT_LOTE,@QT_AMOSTRA,@MAQ_REPROVADAS,@DESCRICAO,@DESCRICAO2,@MES)
                            SET
                            `Familia` = trim(@FAMILIA),
                            `Brand` = trim(@BRAND),
                            `QT_Lote` = trim(@QT_LOTE),
                            `QT_Amostra` = trim(@QT_AMOSTRA),
                            `maq_reprovadas` = trim(@MAQ_REPROVADAS),
                            `descricao` = trim(@DESCRICAO),
                            `descricao2` = trim(@DESCRICAO2),
                            `mes` = trim(@MES),
                            `week` = '$week'
                            ";

the variable $week is the name of the file without the extension I played in the variable the variable $campos is the name of the file with the extension. the rest is the columns you have in the table of the bank and in the spreadsheet csv someone knows why giving this problem.

php excel csv

  • This problem usually happens when you open the CSV on Excell because the bastard puts those quotes and doesn’t show them to you making you think they don’t exist. Open the file in a text editor and see if the quotes really don’t exist

  • put in txt picked quotes, I found way in mysql called ENCLOSED BY ' " ' more in php this giving syntax error because of the quotes that delimits the load for example: $query = "load data local infile ...";

1 answer

2

Lacked that:

 OPTIONALLY ENCLOSED BY '"'

to escape the quotation marks.

See the syntax in the manual:

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Thus remaining:

$sql = "
  LOAD DATA LOCAL INFILE 'C:/inetpub/wwwroot/report/uploads/$campo'
       INTO TABLE reparo.oob
       character set 'utf8'
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"'
       LINES TERMINATED BY '\\r\\n'
       IGNORE 1 LINES
       ...
";

Note the inverted bars (\) to escape the special characters.


Another solution is to use HEREDOC:

$sql = <<<FINAL
LOAD DATA LOCAL INFILE 'C:/inetpub/wwwroot/report/uploads/$campo'
                            INTO TABLE reparo.oob
                            character set 'utf8'
                            FIELDS TERMINATED BY ','
                               OPTIONALLY ENCLOSED BY '"'
                            LINES TERMINATED BY '\\r\\n'
                            IGNORE 1 LINES;
                            ...
FINAL;

Browser other questions tagged php excel csv

You are not signed in. Login or sign up in order to post.