Importa dati da fogli di calcolo Excel o CVS in MySQL

Ho un foglio di calcolo che ha in realtà solo una tabella complicata. In pratica, converto il foglio di calcolo in un cvs e uso uno script groovy per generare gli script INSERT.

Tuttavia, non posso farlo con una tabella che contiene 28 campi con dati all’interno di alcuni campi del foglio di calcolo che rendono ancora più complicata l’importazione nel CVS. Quindi i campi nel nuovo CVS non sono differenziati in modo appropriato o il mio script non ne ha tenuto conto.

Qualcuno ha qualche suggerimento su un approccio migliore per fare questo? Grazie.

Dai un’occhiata all’istruzione LOAD DATA INFILE . Ti aiuterà a importare i dati dal file CSV nella tabella.

Lavoro su un prodotto chiamato SQLizer https://sqlizer.io che converte i file Excel (e CSV) in istruzioni di inserimento MySQL che possono essere eseguite per inserire i dati o incollati in vari sistemi di gestione DB.

SQLizer

È gratuito per file fino a 5.000 righe di dati

Questa è una domanda ricorrente su StackOverflow. Ecco una risposta aggiornata.

Esistono diversi modi per importare un file Excel in un database MySQL con diversi gradi di complessità e successo.

  1. Utilità Excel2MySQL o Navicat . Full disclosure, sono l’autore di Excel2MySQL. Queste 2 utilità non sono gratuite, ma sono l’opzione più semplice e hanno il minor numero di limitazioni. Includono anche funzionalità aggiuntive che aiutano nell’importazione di dati Excel in MySQL. Ad esempio, Excel2MySQL crea automaticamente la tabella e ottimizza automaticamente i tipi di dati sul campo come date, orari, float, ecc. Se hai fretta o non puoi ottenere le altre opzioni per lavorare con i tuoi dati, queste utility potrebbero soddisfare le tue esigenze.

    screenshot di Excel2MySQL

  2. CARICARE DATI INFILATI : Questa opzione popolare è forse la più tecnica e richiede una certa comprensione dell’esecuzione dei comandi di MySQL. È necessario creare manualmente la tabella prima di caricare e utilizzare tipi di campi VARCHAR di dimensioni appropriate. Pertanto, i tipi di dati sul campo non sono ottimizzati. LOAD DATA INFILE ha problemi nell’importazione di file di grandi dimensioni che superano la dimensione ‘max_allowed_packet’. È necessario prestare particolare attenzione per evitare problemi nell’importazione di caratteri speciali e caratteri Unicode estranei. Ecco un esempio recente che ho usato per importare un file csv chiamato test.csv.

    inserisci la descrizione dell'immagine qui

  3. phpMyAdmin : seleziona prima il tuo database, quindi seleziona la scheda Importa. phpMyAdmin creerà automaticamente la tabella e ridimensionerà i campi VARCHAR, ma non ottimizzerà i tipi di campo. phpMyAdmin ha problemi nell’importazione di file di grandi dimensioni che superano la dimensione ‘max_allowed_packet’.

    inserisci la descrizione dell'immagine qui

  4. MySQL per Excel : questo è un componente aggiuntivo gratuito di Excel di Oracle. Questa opzione è un po ‘noiosa perché utilizza una procedura guidata e l’importazione è lenta e buggata con file di grandi dimensioni, ma questa potrebbe essere una buona opzione per i file di piccole dimensioni con dati VARCHAR. I campi non sono ottimizzati.

    inserisci la descrizione dell'immagine qui

Qualche tempo fa ho risposto a una domanda molto simile sul sito EE e ho offerto il seguente blocco di Perl, come esempio rapido e sporco di come caricare direttamente un foglio Excel in MySQL. Bypassare la necessità di esportare / importare tramite CSV e quindi sperare di preservare più di quei caratteri speciali ed eliminare la necessità di preoccuparsi di sfuggire al contenuto.

#!/usr/bin/perl -w # Purpose: Insert each Worksheet, in an Excel Workbook, into an existing MySQL DB, of the same name as the Excel(.xls). # The worksheet names are mapped to the table names, and the column names to column names. # Assumes each sheet is named and that the first ROW on each sheet contains the column(field) names. # use strict; use Spreadsheet::ParseExcel; use DBI; use Tie::IxHash; die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV; my $sDbName = $ARGV[0]; $sDbName =~ s/\.xls//i; my $oExcel = new Spreadsheet::ParseExcel; my $oBook = $oExcel->Parse($ARGV[0]); my $dbh = DBI->connect("DBI:mysql:database=$sDbName;host=192.168.123.123","root", "xxxxxx", {'RaiseError' => 1,AutoCommit => 1}); my ($sTableName, %hNewDoc, $sFieldName, $iR, $iC, $oWkS, $oWkC, $sSql); print "FILE: ", $oBook->{File} , "\n"; print "DB: $sDbName\n"; print "Collection Count: ", $oBook->{SheetCount} , "\n"; for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) { $oWkS = $oBook->{Worksheet}[$iSheet]; $sTableName = $oWkS->{Name}; print "Table(WorkSheet name):", $sTableName, "\n"; for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) { tie ( %hNewDoc, "Tie::IxHash"); for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) { $sFieldName = $oWkS->{Cells}[$oWkS->{MinRow}][$iC]->Value; $sFieldName =~ s/[^A-Z0-9]//gi; #Strip non alpha-numerics from the Column name $oWkC = $oWkS->{Cells}[$iR][$iC]; $hNewDoc{$sFieldName} = $dbh->quote($oWkC->Value) if($oWkC && $sFieldName); } if ($iR == $oWkS->{MinRow}){ #eval { $dbh->do("DROP TABLE $sTableName") }; $sSql = "CREATE TABLE IF NOT EXISTS $sTableName (".(join " VARCHAR(512), ", keys (%hNewDoc))." VARCHAR(255))"; #print "$sSql \n\n"; $dbh->do("$sSql"); } else { $sSql = "INSERT INTO $sTableName (".(join ", ",keys (%hNewDoc)).") VALUES (".(join ", ",values (%hNewDoc)).")\n"; #print "$sSql \n\n"; eval { $dbh->do("$sSql") }; } } print "Rows inserted(Rows):", ($oWkS->{MaxRow} - $oWkS->{MinRow}), "\n"; } # Disconnect from the database. $dbh->disconnect(); 

Nota:

  1. Modificare la stringa di connessione ($ oConn) in base alle proprie esigenze e, se necessario, aggiungere un ID utente e una password agli argomenti.
  2. Se hai bisogno del supporto XLSX, passa rapidamente a Spreadsheet :: XLSX è tutto ciò che serve. In alternativa bastano poche righe di codice, per rilevare il tipo di file e chiamare la libreria appropriata.
  3. Quanto sopra è un semplice trucco, presuppone che tutto in una cella sia una stringa / scalare, se il tipo di conservazione è importante, una piccola funzione con poche espressioni regolari può essere utilizzata insieme ad alcune istruzioni if ​​per garantire che i numeri / date rimangano nella pertinente formato quando scritto nel DB
  4. Il codice sopra riportato dipende da un numero di moduli CPAN, che è ansible installare, presupponendo che l’accesso ftp in uscita sia consentito, tramite:

    cpan YAML Data :: Dumper Spreadsheet :: ParseExcel Tie :: IxHash Encode Scalar :: File Util :: Basename DBD :: mysql

Dovrebbe restituire qualcosa lungo le seguenti linee (è piuttosto lento, a causa del commit automatico):

 # ./Excel2mysql.pl test.xls FILE: test.xls DB: test Collection Count: 1 Table(WorkSheet name):Sheet1 Rows inserted(Rows):9892 

Per i file con valori separati da virgola (CSV), il pannello di visualizzazione dei risultati in Workbench ha un’opzione “Importa record da file esterno” che importa i dati CSV direttamente nel set di risultati. Eseguilo e fai clic su “Applica” per confermare le modifiche.

Per i file Excel, prendere in considerazione l’utilizzo del plugin ufficiale MySQL per Excel .