Como importar o ficheiro CSV para a tabela MySQL
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 existem restrições na tabela, e todos os campos têm valores VARCHAR (256), excepto as colunas que contêm contagens (representadas por INT), sim / não (representadas por BIT), preços (representados por DECIMAL) e borrões de texto (representados por texto).
tentei carregar dados no ficheiro:
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 |
...
Toda a mesa está cheia de NULL
.
acho que o problema é que os borrões de texto contêm mais de uma linha, e o MySQL está a analisar o ficheiro como se cada nova linha correspondesse a uma linha de dados. Posso carregar o ficheiro para o OpenOffice sem problemas.
The clientdata.o ficheiro csv contém 2593 linhas e 570 registos. A primeira linha contém nomes de colunas. Penso que se trata de uma vírgula delimitada, e que o texto é aparentemente delimitado por um duplo quote.
actualização:
Em caso de dúvida, leia o manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html Adicionei algumas informações à Declaração de que o OpenOffice era inteligente o suficiente para inferir, e agora carrega o número correcto de registos: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 registos completos, e nenhum dos dados que foram carregados parece estar no lugar certo.
15 answers
O cerne do seu problema parece estar a corresponder as colunas do ficheiro CSV às da tabela.
Muitos clientes gráficos mySQL têm janelas de importação muito boas para este tipo de coisa.
O meu favorito para o trabalho é o Windows based HeidiSQL. Ele dá-lhe uma interface gráfica para criar o comando LOAD DATA
; poderá usá-lo de novo programaticamente mais tarde.
Imagem: janela "Importar o ficheiro de texto"
Para abrir o Janela de importação do ficheiro de texto", ir para Tools > Import CSV file
:
Usar o mysqlimport {[5] } para carregar uma tabela na base de dados:
mysqlimport --ignore-lines=1 \
--fields-terminated-by=, \
--local -u root \
-p Database \
TableName.csv
Encontrei-o em http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/
Para fazer do separador uma aba, use --fields-terminated-by='\t'
A forma mais simples pela qual importei 200 + linhas está abaixo do comando na janela sql do phpmyadmin
Tenho uma simples tabela de país com duas colunas. País, País Aqui está .dados csvAqui 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 a sua importação irá parar
Eu sei que a questão é antiga, mas eu gostaria de partilhar isto.
Usei este método para importar mais de 100K registos (~5MB) em 0, 046 s
Eis como se faz: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 tiver mais do que um campo i. e normalmente salta o último campo (MySQL 5.6.17)
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
Então, assumindo que tens a primeira linha como título para a tua campos, você pode querer incluir esta linha também
IGNORE 1 ROWS
Isto é o que parece se o seu ficheiro tem 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);
O PhpMyAdmin consegue lidar com a importação de CSV. Aqui estão os passos:
Prepare o arquivo CSV para ter os campos na mesma ordem que os campos da tabela MySQL.
Remova a linha de cabeçalho do CSV (se existir), de modo que apenas os dados estejam no arquivo.
Vai para a interface phpMyAdmin.
Seleccione a tabela no menu à esquerda.
Carregue no botão Importar no topo.
Navegar pelo CSV arquivo.
Seleccione a opção "CSV utilizando os dados de carga".
Digite "," nos "campos terminados por".
Indique os nomes das colunas pela mesma ordem que estão na tabela de base de dados.
Clique no botão Ir e você está pronto.
A linha de comandos mysql é propensa a demasiados problemas na importação. Aqui está como você faz:
- use o excel para editar os nomes dos cabeçalhos para não ter espaços
- Salvar como .csv
- use o navegador Navicat Lite Sql livre para importar e criar automaticamente uma nova tabela (dê-lhe um nome)
- Abra a nova tabela insira uma coluna de Número automático primário para ID
- mude o tipo das colunas como desejar. Feito!
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;
Ignorar 1 linhas aqui ignora a primeira linha que contém os nomes de campo. Observe que para o nome do arquivo você deve digitar o caminho absoluto do arquivo.
Também pode tentar esta ferramenta online para gerar instruções SQL criar/Inserir com base no seu CSV. http://www.convertcsvtomysql.com/
O Que Eu Gosto nesta ferramenta:
- Simples
- não só gera instruções de inserção, mas também cria instruções para gerar uma tabela Ao gerar declarações de criação, esta ferramenta não só faz todos os campos VARCHAR - ele analisa dados em seu CSV, e com base nessa análise, ele escolhe os dados adequados tipo.
Desvantagens
- O tamanho do ficheiro de entrada está limitado a 3mb Pode não estar disposto a dar os seus dados a terceiros.
Se estiver a utilizar o MySQL Workbench (actualmente 6. 3 versão), pode fazê-lo por:
- carregue com o botão direito em "tabelas";
- Escolheu O Assistente De Importação De Dados Da Tabela;
- Escolha o seu ficheiro csv e siga as instruções (JSON também pode ser usado); A coisa boa é que você pode criar uma nova tabela com base no arquivo csv que você deseja importar ou carregar dados para uma tabela existente
Se estiver a usar uma máquina do windows com a folha de cálculo do Excel carregada, o novo plugin mySql para o Excel é fenomenal. O pessoal da Oracle fez um bom trabalho nesse software. Você pode fazer a conexão de banco de dados diretamente do Excel. Esse plugin irá analisar os seus dados, e configurar as tabelas para você em um formato consistente com os dados. Eu tinha alguns grandes ficheiros de dados do CSV para converter. Esta ferramenta era uma grande poupança de tempo.
Http://dev.mysql.com/downloads/windows/excel/
Você pode fazer atualizações de dentro do Excel que irá preencher a base de dados online. Isso funcionou muito bem com arquivos mySql criados em Hospedagem Compartilhada ultra barato GoDaddy. (Note quando você cria a tabela em GoDaddy, você tem que selecionar algumas configurações fora do padrão para permitir o acesso fora do site da base de dados...)
Com este 'plugin', terá pura interactividade entre a sua folha de cálculo XL e o mySql 'online' armazenamento.
Mudar o servidor, nome de utilizador, Senha, dbname, localização do seu ficheiro, nome de mesa e o campo que está na sua base de dados que 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>'
}
?>
Pesquisa em PHP para importar o ficheiro csv para a base 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));
}
* * Amostra de dados do ficheiro CSV * *
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]
Aqui está a imagem de imagem do excel:
Salvar como e escolher .csv.
E você terá como mostrado abaixo .imagem de ecrã de dados csv se Abrir com o notepad++ ou com qualquer outro bloco de notas.
Certifique-se que remove o cabeçalho e tem o alinhamento das colunas .csv como na mesa mysql. Substituir o folder_ nome pelo nome da sua pasta
CARREGAR O FICHEIRO LOCAL DOS DADOS
'D:/folder_name/myfilename.csv"
Para o correio de mesa
CAMPO TERMINADO POR","
(fname, lname ,email, telefone);
Se grandes dados, você pode tomar café e tê-lo carregado!.
É tudo o que precisas.Outra solução é usara ferramenta csvsql do pacote amazingcsvkit .
Exemplo de Utilização:
csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename $file
Esta ferramenta pode inferir automaticamente os tipos de dados (comportamento por omissão), criar uma tabela e inserir os dados na tabela criada. --overwrite
a opção pode ser usada para largar a tabela se já existir. --insert
opção-Preencher a tabela a partir do ficheiro.
Para instalar o conjunto
pip install csvkit
Pré-Requisitos: python-dev
, libmysqlclient-dev
, MySQL-python
apt-get install python-dev libmysqlclient-dev
pip install MySQL-python