Problem with CSV import to Mysql

Question

Problem with CSV import to Mysql

Navigation

#1 by (2 votes)

1

I am importing csv file information into the mysql database some spreadsheets have been imported correctly, however some spreadsheets when importing into the database appear a double quotation mark that does not exist in the spreadsheet so affecting the position of the column information I am using load data local infile to import the csv file.

The line on the seat looks like this

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 quotation mark does not have in the worksheet I've already copied it to a blank worksheet only with the pure values and even then it takes that double quotation mark the load it's normal because some spreadsheets did not give these problem the load code 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'
                            ";

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

php excel csv

asked by anonymous 15.06.2016 / 16:07

1 answer

Apache Virtual Host without ServerName Why does not the alarm open to Activity?

score 2 · Answer 1

Missing this:

 OPTIONALLY ENCLOSED BY '"'

to escape the quotes.

See the syntax in the manual:

link

Looking like this:

$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 backslashes ( \ ) to escape 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;