tables - Como importar o arquivo CSV para a tabela MySQL




workbench import from csv (12)

Eu tenho um CSV de diário de eventos não normalizado de um cliente que estou tentando carregar em uma tabela MySQL para que eu possa refatorar em um formato sã. Eu criei uma tabela chamada 'CSVImport' que tem um campo para cada coluna do arquivo CSV. O CSV contém 99 colunas, portanto, isso já era uma tarefa difícil:

CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);

Não há restrições na tabela e todos os campos contêm valores VARCHAR (256), exceto as colunas que contêm contagens (representadas por INT), sim / não (representadas por BIT), preços (representados por DECIMAL) e blurbs de texto ( representado por TEXT).

Eu tentei carregar dados no arquivo:

LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023  Deleted: 0  Skipped: 0  Warnings: 198256
SELECT * FROM CSVImport;
| NULL             | NULL        | NULL           | NULL | NULL               | 
...

A tabela inteira está cheia de NULL .

Eu acho que o problema é que os textos contêm mais de uma linha, e o MySQL está analisando o arquivo como se cada nova linha correspondesse a uma linha de dados. Eu posso carregar o arquivo no OpenOffice sem nenhum problema.

O arquivo clientdata.csv contém 2593 linhas e 570 registros. A primeira linha contém nomes de colunas. Eu acho que é delimitado por vírgula, e o texto é aparentemente delimitado com doublequote.

ATUALIZAR:

Em caso de dúvida, leia o manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Eu adicionei algumas informações à instrução LOAD DATA que o OpenOffice era inteligente o suficiente para inferir, e agora ele carrega o número correto de registros:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Mas ainda há muitos registros completamente NULL , e nenhum dos dados que foram carregados parece estar no lugar certo.


Altere servername, username, password, dbname, caminho do seu arquivo, tablename e o campo que está no seu banco de dados que você deseja inserir

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "bd_dashboard";
    //For create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    $query = "LOAD DATA LOCAL INFILE 
                'C:/Users/lenovo/Desktop/my_data.csv'
                INTO TABLE test_tab
                FIELDS TERMINATED BY ','
                LINES TERMINATED BY '\n'
                IGNORE 1 LINES
                (name,mob)";
    if (!$result = mysqli_query($conn, $query)){
        echo '<script>alert("Oops... Some Error occured.");</script>';
        exit();
            //exit(mysqli_error());
       }else{
        echo '<script>alert("Data Inserted Successfully.");</script>'
       }
    ?>

Eu sei que a questão é antiga , mas eu gostaria de compartilhar isso

Eu usei esse método para importar mais de 100 mil registros ( ~ 5 MB ) em 0,046 segundos

Veja como você faz isso:

LOAD DATA LOCAL INFILE  
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

É muito importante incluir a última linha, se você tiver mais de um campo, isto é, normalmente ele pula o último campo (MySQL 5.6.17)

LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

Então, supondo que você tenha a primeira linha como o título para seus campos, você pode querer incluir esta linha também

IGNORE 1 ROWS

Isto é o que parece se o seu arquivo tiver uma linha de cabeçalho.

LOAD DATA LOCAL INFILE  
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);

A maneira mais simples que eu importei mais de 200 linhas está abaixo do comando na janela phpmyadmin sql

Eu tenho uma tabela simples de país com duas colunas CountryId, CountryName

aqui é dados .csv

aqui está o comando:

LOAD DATA INFILE 'c:/country.csv' 
INTO TABLE country 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Tenha uma coisa em mente, nunca apareça, na segunda coluna, caso contrário, sua importação será interrompida


Ainda outra solução é usar a ferramenta csvsql da incrível suíte csvkit .

Exemplo de uso:

csvsql --db mysql://$user:[email protected]/$database --insert --tables $tablename  $file

Esta ferramenta pode inferir automaticamente os tipos de dados (comportamento padrão), criar uma tabela e inserir os dados na tabela criada. --overwrite opção --overwrite pode ser usada para eliminar a tabela, se já existir. --insert opção - para preencher a tabela do arquivo.

Para instalar o pacote

pip install csvkit

Pré-requisitos: python-dev , libmysqlclient-dev , MySQL-python

apt-get install python-dev libmysqlclient-dev
pip install MySQL-python

Eu uso o mysql workbench para fazer o mesmo trabalho.

  1. criar novo esquema
  2. abra o esquema recém-criado
  3. clique com o botão direito do mouse em "Tabelas" e selecione "Table Data Import Wizard"
  4. forneça o caminho do arquivo csv e o nome da tabela e, finalmente, configure seu tipo de coluna, pois o assistente configura o tipo de coluna padrão com base em seus valores.

Nota: dê uma olhada no arquivo de log do mysql workbench para quaisquer erros usando "tail -f [mysqlworkbenchpath] /log/wb*.log"


Eu vejo algo estranho. Você está usando para ESCAPAR o mesmo caractere que você usa para ENCLOSING. Então o motor não sabe o que fazer quando ele encontra um '' 'e eu acho que é por isso que nada parece estar no lugar certo. Eu acho que se você remover a linha de ESCAPAR, deve rodar muito bem.

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

A menos que você analise (manualmente, visualmente, ...) seu CSV e encontre qual caractere usa para escape. As vezes é '\'. Mas se você não tiver, não use.


O núcleo do seu problema parece estar combinando as colunas no arquivo CSV com as da tabela.

Muitos clientes mySQL gráficos têm diálogos de importação muito bons para esse tipo de coisa.

Meu favorito para o trabalho é o HeidiSQL baseado no Windows. Ele fornece uma interface gráfica para construir o comando LOAD DATA ; você pode reutilizá-lo programaticamente mais tarde.

Captura de tela: "Import textfile" dialog

Para abrir a caixa de diálogo Import textfile ", vá para Tools > Import CSV file :


Se você estiver usando o MySQL Workbench (atualmente versão 6.3), você pode fazer isto:

  1. Clique com o botão direito em "Tabelas";
  2. Escolha o Assistente de Importação de Dados da Tabela;
  3. Escolha o arquivo csv e siga as instruções (o JSON também pode ser usado); O bom é que você pode criar uma nova tabela com base no arquivo csv que você deseja importar ou carregar dados em uma tabela existente


Tente isso, funcionou para mim

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

IGNORE 1 ROWS aqui ignora a primeira linha que contém os nomes dos campos. Observe que, para o nome do arquivo, você deve digitar o caminho absoluto do arquivo.



Você também pode tentar essa ferramenta on-line para gerar instruções de criação / inserção de SQL com base no seu CSV. http://www.convertcsvtomysql.com/

O que eu gosto sobre esta ferramenta:

  • Simples
  • Ele não apenas gera instruções INSERT, mas também cria instruções para gerar uma tabela.
  • Ao gerar instruções CREATE, essa ferramenta não apenas cria todos os campos VARCHAR - analisa dados em seu CSV e, com base nessa análise, escolhe o tipo de dados adequado.

Desvantagens

  • O tamanho do arquivo de entrada é limitado a 3mb
  • Você pode não estar disposto a doar seus dados para terceiros.

Consulta PHP para importar arquivo csv para o banco de dados mysql

$query = <<<EOF
            LOAD DATA LOCAL INFILE '$file'
             INTO TABLE users
             FIELDS TERMINATED BY ','
             LINES TERMINATED BY '\n'
             IGNORE 1 LINES
            (name,mobile,email)
    EOF;
if (!$result = mysqli_query($this->db, $query))
   {
        exit(mysqli_error($this->db));
   }

** Dados do arquivo CSV de amostra **

name,mobile,email
Christopher Gritton,570-686-3439,ChristopherKGritton@inbound.plus
Brandon Wilson,541-309-5149,BrandonMWilson@inbound.plus
Craig White,516-795-8065,CraigJWhite@inbound.plus
David Whitney,713-214-3966,DavidCWhitney@inbound.plus




load-data-infile