Importa CSV in una tabella mysql

Qual è il modo migliore / più veloce per caricare un file csv in una tabella mysql? Vorrei che la prima riga di dati fosse usata come nome di colonna.

Trovato questo:

Come importare il file CSV nella tabella MySQL

Ma l’unica risposta era usare una GUI e non una shell?

Invece di scrivere uno script per inserire informazioni da un file CSV, puoi colbind MYSQL direttamente ad esso e caricare le informazioni usando la seguente syntax SQL.

Per importare un file Excel in MySQL, esportarlo prima come file CSV. Rimuovi le intestazioni CSV dal file CSV generato insieme ai dati vuoti che Excel potrebbe aver inserito alla fine del file CSV.

Puoi quindi importarlo in una tabella MySQL eseguendo:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, uniqComments) 

come letto su: Importa il file CSV direttamente in MySQL

MODIFICARE

Per il tuo caso, dovrai prima scrivere un interprete, trovare la prima riga e assegnarli come nomi di colonne.


EDIT-2

Da documenti MySQL su syntax LOAD DATA :

L’opzione IGNORE number LINES può essere utilizzata per ignorare le righe all’inizio del file. Ad esempio, è ansible utilizzare IGNORE 1 LINES per saltare sopra una riga di intestazione iniziale contenente i nomi di colonna:

 LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES; 

Pertanto, è ansible utilizzare la seguente dichiarazione:

 LOAD DATA LOCAL INFILE 'uniq.csv' INTO TABLE tblUniq FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (uniqName, uniqCity, uniqComments) 

Ecco un semplice script da riga di comando PHP che farà ciò di cui hai bisogno:

  

Creerà una tabella in base alla prima riga e importerà le righe rimanenti in essa. Ecco la syntax della riga di comando:

 php csv_import.php csv_file.csv table_name 

se hai la possibilità di installare phpadmin c’è una sezione di importazione dove puoi importare file csv nel tuo database c’è anche una casella di controllo per impostare l’intestazione sulla prima riga del file contiene i nomi delle colonne della tabella (se questa è deselezionata, la la prima riga diventerà parte dei dati

Per prima cosa creare una tabella nel database con lo stesso numero di colonne presenti nel file csv.

Quindi utilizzare la seguente query

 LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 

Per caricare i dati dal file di testo o dal file CSV, il comando è

 load data local infile 'file-name.csv' into table table-name fields terminated by '' enclosed by '' lines terminated by '\n' (column-name); 

Nel comando precedente, nel mio caso c’è solo una colonna da caricare, quindi non c’è “terminato da” e “racchiuso da” così l’ho tenuto vuoto altrimenti il ​​programmatore può inserire il carattere di separazione. per es. , (virgola) o “o; o qualsiasi cosa.

** per le persone che utilizzano mysql versione 5 e successive **

Prima di caricare il file in mysql è necessario assicurarsi che la riga di rimorchio sottostante sia aggiunta in side etc/mysql/my.cnf

per modificare il comando my.cnf è

sudo vi /etc/mysql/my.cnf

 [mysqld] local-infile [mysql] local-infile 

Se si avvia mysql come “mysql -u -p –local-infile”, funzionerà correttamente

Ho lottato con questo per un po ‘di tempo. Il problema non sta nel come caricare i dati, ma come build la tabella per tenerli. È necessario generare un’istruzione DDL per creare la tabella prima di importare i dati.

Particolarmente difficile se la tabella ha un numero elevato di colonne.

Ecco uno script python che (quasi) fa il lavoro:

 #!/usr/bin/python import sys import csv # get file name (and hence table name) from command line # exit with usage if no suitable argument if len(sys.argv) < 2: sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename') ifile = sys.argv[1] # emit the standard invocation print 'create table ' + ifile + ' (' with open(ifile + '.csv') as inputfile: reader = csv.DictReader(inputfile) for row in reader: k = row.keys() for item in k: print '`' + item + '` TEXT,' break print ')\n' 

Il problema che lascia risolvere è che il nome del campo finale e la dichiarazione del tipo di dati sono terminati con una virgola, e il parser mySQL non lo tollererà.

Ovviamente ha anche il problema che usa il tipo di dati TEXT per ogni campo. Se la tabella ha diverse centinaia di colonne, VARCHAR (64) renderà la tabella troppo grande.

Questo sembra anche interrompersi al massimo numero di colonne per mySQL. Questo è quando è il momento di passare a Hive o HBase se ne sei capace.

Ho scritto del codice per farlo, inserirò alcuni frammenti:

 $dir = getcwd(); // Get current working directory where this .php script lives $fileList = scandir($dir); // scan the directory where this .php lives and make array of file names 

Quindi ottieni le intestazioni CSV in modo che tu possa dire a mysql come importare (nota: assicurati che le tue colonne mysql corrispondano esattamente alle colonne csv):

 //extract headers from .csv for use in import command $headers = str_replace("\"", "`", array_shift(file($path))); $headers = str_replace("\n", "", $headers); 

Quindi invia la tua richiesta al server mysql:

 mysqli_query($cons, ' LOAD DATA LOCAL INFILE "'.$path.'" INTO TABLE '.$dbTable.' FIELDS TERMINATED by \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES ('.$headers.') ; ')or die(mysql_error()); 

Ecco come l’ho fatto in Python usando csv e il connettore MySQL :

 import csv import mysql.connector credentials = dict(user='...', password='...', database='...', host='...') connection = mysql.connector.connect(**credentials) cursor = connection.cursor(prepared=True) stream = open('filename.csv', 'rb') csv_file = csv.DictReader(stream, skipinitialspace=True) query = 'CREATE TABLE t (' query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames) query += ')' cursor.execute(query) for row in csv_file: query = 'INSERT INTO t SET ' query += ','.join('`{}` = ?'.format(column) for column in row.keys()) cursor.execute(query, row.values()) stream.close() cursor.close() connection.close() 

Punti chiave

  • Usa istruzioni preparate per l’INSERIMENTO
  • Apri il file.csv nel file binario 'rb'
  • Alcuni file CSV potrebbero aver bisogno di modifiche , come l’opzione skipinitialspace .
  • Se 255 non è sufficientemente ampio, riceverai errori su INSERT e dovresti ricominciare da capo.
  • Regolare i tipi di colonna, ad es. ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);
  • Aggiungi una chiave primaria , ad es. ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

Importa file CSV nella tabella mysql

 LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; Character Escape Sequence \0 An ASCII NUL (0x00) character \b A backspace character \n A newline (linefeed) character \r A carriage return character \t A tab character. \Z ASCII 26 (Control+Z) \N NULL 

visite: http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html

Come altri hanno già menzionato, l’infile locale dei dati di caricamento funziona bene. Ho provato lo script php pubblicato da Hawkee, ma non ha funzionato per me. Piuttosto che eseguirne il debug, ecco cosa ho fatto:

1) copia / incolla la riga di intestazione del file CSV in un file txt e modifica con emacs. aggiungi una virgola e una CR tra ogni campo per far sì che ognuno si acceda alla propria linea.
2) Salva il file come FieldList.txt
3) modifica il file per includere i defns per ogni campo (la maggior parte erano varchar, ma alcuni erano int (x). Aggiungi crea tablename table (all’inizio del file e) alla fine del file. Salva come CreateTable .sql
4) avviare il client mysql con l’input dal file Createtable.sql per creare la tabella
5) avvia il client mysql, copia / incolla nella maggior parte del comando ‘LOAD DATA INFILE’ sostituendo il nome della tabella e il nome del file csv. Incolla nel file FieldList.txt. Assicurati di includere “IGNORE 1 LINES” prima di incollare nell’elenco dei campi

Sembra un sacco di lavoro, ma facile con emacs …..

Ho cercato su google molti modi per importare csv su mysql, includere “carica dati in infile”, usare mysql workbench, ecc.

quando utilizzo il pulsante di importazione mysql workbench, prima devi creare la tabella vuota da sola, imposta ogni tipo di colonna da solo. Nota: è necessario aggiungere la colonna ID alla fine come chiave primaria e non null e auto_increment, altrimenti il ​​pulsante di importazione non sarà visibile in seguito. Tuttavia, quando avvio il caricamento del file CSV, nulla è caricato, sembra un bug. Mi arrendo.

Fortunatamente, il modo più semplice finora trovato è quello di utilizzare MySQL di Oracle per Excel. puoi scaricarlo da qui mysql per Excel

Questo è quello che stai per fare: apri il file csv in excel, nella scheda Dati, trova mysql per il pulsante excel

seleziona tutti i dati, fai clic su Esporta su MySQL. Nota per impostare una colonna ID come chiave primaria.

al termine, andare su mysql workbench per modificare la tabella, ad esempio il tipo di valuta deve essere decimale (19,4) per i decimali di importo elevato (10,2) per l’uso regolare. un altro tipo di campo può essere impostato su varchar (255).