ti-enxame.com

Como importar o arquivo CSV para a tabela MySQL

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 é preenchida com 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 NULL completamente, e nenhum dos dados que foram carregados parece estar no lugar certo.

255
Iain Samuel McLean Elder

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 Windows HeidiSQL . Ele fornece uma interface gráfica para construir o comando LOAD DATA; você pode reutilizá-lo programaticamente mais tarde.

Import textfile

Captura de tela: "Import textfile" dialog

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

enter image description here

117
Pekka 웃

Use mysqlimport para carregar uma tabela no banco de dados:

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p Database \
             TableName.csv

Eu encontrei em http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

Para tornar o delimitador uma guia, use --fields-terminated-by='\t'

170
michalzuber

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 .csvCSV FILE

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

73
DareDevil

Eu sei que o question é antigo, mas eu gostaria de share this

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

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, 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);
53
Fahad

phpMyAdmin pode manipular a importação de CSV. Aqui estão os passos:

  1. Prepare o arquivo CSV para ter os campos na mesma ordem que os campos da tabela MySQL.

  2. Remova a linha de cabeçalho do CSV (se houver), para que apenas os dados estejam no arquivo.

  3. Vá para a interface do phpMyAdmin.

  4. Selecione a tabela no menu à esquerda.

  5. Clique no botão de importação no topo.

  6. Navegue até o arquivo CSV.

  7. Selecione a opção "CSV usando LOAD DATA".

  8. Digite "," nos "campos terminados por".

  9. Digite os nomes das colunas na mesma ordem em que eles estão na tabela do banco de dados.

  10. Clique no botão Ir e você está pronto.

Esta é uma nota que preparei para meu uso futuro e compartilho aqui se alguém puder se beneficiar.

24
Farhan

Você pode corrigir isso listando as colunas na instrução LOAD DATA. A partir do manual :

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

... então, no seu caso, você precisa listar as 99 colunas na ordem em que aparecem no arquivo csv.

11
lafncow

A linha de comando do mysql é propensa a muitos problemas na importação. Aqui está como você faz isso:

  • usar o Excel para editar os nomes dos cabeçalhos para não ter espaços
  • salvar como .csv
  • use Navigator Lite Sql Browser grátis para importar e criar automaticamente uma nova tabela (dê um nome a ela)
  • abra a nova tabela insira uma coluna de número automático principal para ID
  • altere o tipo das colunas conforme desejado.
  • feito!
7
user1464271

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.

6
David

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 deseja importar ou carregar dados em uma tabela existente

enter image description here

3
Vitaliy Pak

Você também pode tentar esta ferramenta on-line para gerar instruções de criação/inserção de SQL com base em 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 instruções CREATE 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.
2
Ievgen

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 . Gostar:

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.

1
Juan

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>'
       }
    ?>
1
Srikrushna Pal

Se você estiver usando uma máquina Windows com planilha Excel carregada, o novo plugin mySQL para o Excel é fenomenal. O pessoal da Oracle realmente fez um bom trabalho nesse software. Você pode fazer a conexão do banco de dados diretamente do Excel. Esse plug-in analisará seus dados e configurará as tabelas para você em um formato consistente com os dados. Eu tinha alguns grandes arquivos csv de dados para converter. Esta ferramenta foi uma grande economia de tempo. 

http://dev.mysql.com/downloads/windows/Excel/

Você pode fazer atualizações no Excel que serão preenchidas no banco de dados online. Isso funcionou muito bem com arquivos mySql criados em hospedagem compartilhada GoDaddy ultra barata. (Observe que quando você cria a tabela no GoDaddy, é necessário selecionar algumas configurações fora do padrão para habilitar o acesso ao site fora do banco de dados ...)

Com este plugin você tem pura interatividade entre sua planilha XL e o armazenamento de dados mySQL on-line. 

1
zipzit

Outra solução é usar csvsql tool from amazing csvkit suite.

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. A 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
1
ruvim

PHP Consulta para importar o 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,[email protected]
Brandon Wilson,541-309-5149,[email protected]
Craig White,516-795-8065,[email protected]
David Whitney,713-214-3966,[email protected]
1

Aqui está um exemplo de captura de tela de arquivo do Excel:

enter image description here

Salve como e escolha .csv.

E você terá como mostrado abaixo. Captura de tela de dados .csv se você abrir usando o notepad ++ ou qualquer outro bloco de notas.

enter image description here

Certifique-se de remover o cabeçalho e ter o alinhamento da coluna em .csv como na tabela mysql. Substitua folder_name pelo nome da pasta

LOAD DATA INFILE LOCAL
'D: /folder_name/myfilename.csv' INTO TABLE mail CAMPOS TERMINADOS POR ',' (Fname, lname, email, phone);

Se dados grandes, você pode tomar café e ter que carregar !.

Isso é tudo que você precisa. 

1
Magige Daniel

Como importar arquivos csv para tabelas sql

Arquivo de exemplo: Overseas_trade_index data CSV File

Passos:

  1. Precisa criar uma tabela para overseas_trade_index.

  2. Precisa criar colunas relacionadas ao arquivo csv.

    Consulta SQL:

    ( id int not null primary key auto_increment,
    series_reference varchar (60),
    period varchar (60),
    data_value decimal(60,0),
    status varchar (60),
    units varchar (60),
    magnitude int(60),
    subject text(60),
    group text(60),
    series_title_1 varchar (60),
    series_title_2 varchar (60),
    series_title_3 varchar (60),
    series_title_4 varchar (60),
    series_title_5 varchar (60),
     );
    
  3. Precisa conectar o banco de dados mysql no terminal.

    =>show databases;
    =>use database;
    =>show tables;
    
  4. Por favor digite este comando para importar os dados csv para as tabelas mysql.

    load data infile '/home/desktop/Documents/overseas.csv' into table trade_index fields terminated by ',' lines terminated by '\n' (series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2,series_title_3,series_title_4,series_title_5);
    
  5. Encontre os dados do índice de comércio exterior no sqldatabase:

    select * from trade_index;
    
0
sivamani
0
Georgy Gobozov

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"

0
Mehdi