Esporta dati da database a file CSV

Microsoft Access (2007, 2003) / MySQL / Postgres / MS SQL Server / Oracle / The Enterprise Data Publisher

SpatialKey richiede che i dati vengano caricati e importati tramite un file CSV prima di poter importare un set di dati. Abbiamo cercato di rendere il processo di importazione il più divertente e semplice possibile, ma SpatialKey non può aiutarti a ottenere i tuoi dati in formato CSV. In molti casi i preziosi dati a cui è necessario accedere verranno bloccati all’interno di un database. Esistono molti tipi di database e la maggior parte di essi ha funzionalità per aiutare a esportare i dati in un formato CSV, ma di solito sono specifici per il database in questione.

Questo obiettivo di questo articolo è quello di esaminare come ottenere i dati in un formato CSV da molti dei database più poplar e sistemi di gestione di database oggi sul mercato. Se non si ha accesso al database in cui risiedono i dati e si dispone di un amministratore del database o di personale IT che genera i report per l’utente, passare il link a questo articolo quando si richiedono dati per SpatialKey in quanto potrebbe risparmiare un po ‘ di tempo.

Si noti che questo articolo riguarda l’utilizzo dei comandi SQL (Structured Query Language) necessari per l’esecuzione su un database ed è destinato agli utenti che hanno familiarità con SQL e gli strumenti per il database che utilizzano comunemente.

Accesso Microsoft:

Microsoft Access è un popolare database basato su file utilizzato in genere da piccole organizzazioni con un numero limitato di utenti e anche da individui. È possibile accedere ai dati MS Access utilizzando l’applicazione MS Access in modo visivo e attraverso approcci basati su SQL tradizionali. Poiché MS Access ha recentemente subito un importante cambiamento nell’interfaccia utente, esploreremo entrambe le versioni 2007 e 2003 dell’applicazione (se si sta lavorando con una versione precedente, dovrebbero essere disponibili anche tecniche simili).

Accesso 2007:

Nella prima figura (1.1) puoi vedere che ho aperto il database di esempio standard fornito con MS Access (chiamato Northwind). Ho anche selezionato la query chiamata “Primi dieci ordini per importo di vendita”. A questa query, ho aggiunto le informazioni sull’indirizzo del cliente poiché SpatialKey ama i dati che contengono dati temporali (ora e data) e geospaziali (dati che possono essere tradotti in una posizione).

Fig. 1.1-MS Access 2007

Da qui posso facilmente esportare i risultati in un file CSV:

1. fare Clic sulla tabella o della query da cui esportare i dati (in questo esempio “Top Ten Ordini con Importo di Vendita” sulla sinistra)
2. fare Clic su “Dati Esterni” scheda nella parte superiore della finestra
3. Nella sezione “esporto” fare clic su “File di Testo” e apparirà una procedura guidata
4. Scegliere una posizione per il CSV esportato e il nome del file (assicurarsi che il file che termina con .estensione csv)
5. fare Clic su OK
6. Nella schermata successiva l’opzione “Delimitato” opzione è selezionata
7. fare Clic sul pulsante “Avanzate…” in basso a sinistra della finestra
8. SpatialKey memorizza i dati come Unicode UTF-8, abbiamo bisogno di garantire che esportare i dati in questo formato
   1. fare Clic sulla casella a discesa accanto a Codice-Pagina
   2. Scegli Unicode (UTF-8) nell’elenco delle opzioni
   3. fare Clic su OK
9. Torna all’Esportazione di Testo della finestra fare clic su “Avanti”
10. assicurati “Virgola” è selezionato come delimitatore e Qualificatore di Testo è una doppia citazione: “
11. Fai clic sulla casella di controllo “Includi nomi di campo sulla prima riga” (dovrebbe essere selezionato)
12. Fai clic su “Avanti”
13. Verifica il nome del file e la posizione e fai clic su “Fine”
14. La schermata finale della procedura guidata ti dà la possibilità di salvare i passaggi consentendo una facile riesportazione dei dati in futuro. Se prevedi di dover aggiornare i dati in SpatialKey, vai avanti e seleziona la casella di controllo per risparmiare tempo in futuro. Chiudere la finestra al termine.

Se apri il file in un editor di testo, dovresti vedere qualcosa del genere:

Fig. 1.2-Dati Northwind esportati

Ora sei pronto per caricare il nuovo file CSV su SpatialKey.

MS Access 2003:

La figura seguente mostra la versione precedente di MS Access 2003 del database Northwind. Esporterò la query salvata chiamata “Ordini Qry” a sinistra.

Fig. 1.3-MS Access 2003 Northwind Database

Ora sei pronto per caricare il nuovo file CSV su SpatialKey.

MySQL:

MySQL è un popolare database open source enterprise ready. Ci sono molti strumenti disponibili per interagire con questo database, ma come con la maggior parte dei database aziendali, la maggior parte degli accessi avviene tramite un linguaggio di query strutturato. Ci sono alcune opzioni per esportare i dati in CSV da MySQL. Alcuni richiedono strumenti di terze parti, mentre un altro utilizza uno strumento da riga di comando (utile su macchine Unix) e un’opzione finale tramite SQL. Diamo un’occhiata alle ultime due opzioni:

MySQL command line tool (su Unix):

mysql -u exampleuser -p letmein exampledb -B -e "select * from\'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Quindi cosa significa tutto questo?

• mysql : il file eseguibile (dovrebbe essere dove il MySQL è installato)
• -u exampleuser : un interruttore e opzione per il nome utente per eseguire SQL con
• -p letmein : un interruttore e l’opzione per la password dell’utente
• exampledb : il database per eseguire il codice SQL
• -B : indica che l’output deve essere delimitato da tabulazione (si converte virgole dopo il comando)
• -e “l’istruzione sql qui” : l’istruzione SQL per eseguire la restituzione di dati
• | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed è un processore di flusso Unix, essenzialmente consente trasformazioni in questo caso. Qui abbiamo quattro comandi sed che cambiano le schede in virgolette doppie, aggiunge virgolette all’inizio e alla fine di ogni riga e aggiunge un nuovo marcatore di riga alla fine di ogni riga.
• > nome file.csv: restituisce i risultati nel file denominato filename.csv

Ora sei pronto per caricare il nuovo file CSV su SpatialKey.

Sintassi SQL MySQL:

SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM test_table;

L’istruzione MySQL select utilizza un comando INTO OUTFILE per produrre i risultati di SQL select in un file (in questo caso/tmp / result.csv).

Un paio di note:

• In entrambi i casi, il file viene salvato in una cartella locale in cui il database è stato eseguito da (sulla stessa macchina, o di un supporto/guida della macchina)
• La riga di intestazione dovrà essere aggiunto a entrambi i file (che può essere fatto utilizzando qualsiasi editor di testo o un programma di foglio di calcolo)

Ora sei pronto per caricare il nuovo File CSV per SpatialKey.

Postgres:

Postgres è un altro popolare database open source enterprise ready. Simile in natura a MySQL, ci sono molti strumenti di terze parti (tra cui PGAdmin da Postgres) per la gestione e l’amministrazione del database. La soluzione migliore per esportare i dati CSV da Postgres è usare un comando SQL specifico di Postgres chiamato COPY:

COPY (SELECT column_a,column_b FROM whatever) TO '/tmp/dump.csv' WITH CSV HEADER

Questo comando è estremamente veloce e produce un file CSV formattato correttamente che include le intestazioni.

Ora sei pronto per caricare il nuovo file CSV su SpatialKey.

MS SQL Server:

SQL Server è un database disponibile in commercio da Microsoft (c’è una versione più piccola, meno descritto chiamato SQL Server Express, nonché che è disponibile per il download gratuito). SQL Server è un database enterprise ready utilizzato da molte aziende in tutto il mondo. Nel corso degli anni ci sono stati molti strumenti di gestione sia da terze parti e da Microsoft (Enterprise Manager, Management Studio, e attraverso MS Developer Studio, per esempio). Due opzioni per l’esportazione come CSV da un server MS SQL sono l’uso dello strumento a riga di comando BPC (Bulk Copy Program) o tramite servizi di trasformazione dei dati.

BCP:

Bulk Copy Program può essere eseguito da una riga di comando o tramite SQL.

• Riga di comando:
  • BCP<nome tabella >out < nome file.csv> -c -t, -U <user name> -P <password> -S<server name>
  • Switches:
    • -c : export as ASCII with a tab delimiter and carriage return/line feed line terminator
    • -t, : override the tab delimiter with a comma
    • -U : username
    • -P : password
    • -S : server to connect to
• SQL:
  • MS SQL Server ha un comando Transact-SQL chiamato xp_cmdshell, questo comando consente di eseguire eseguibili da riga di comando da SQL
  • xp_cmdshell BCP <nome tabella> out <nome del file.csv> -c -t-T -S<nome del server>
    • Nota che ho cambiato l’-U e – P a -T che utilizza una connessione trusted come siamo probabilmente l’autenticazione di SQL server macchina già per eseguire il comando SQL.

DTS:
I pacchetti DTS possono essere creati utilizzando script SQL o tramite un’interfaccia utente MS.

1. Da Enterprise Manager scegliere > Wizard > Trasformazione dei Dati > Esporta Dati
2. Scegli il tuo database di origine
3. Per la destinazione selezionare File di Testo e specificare il file di destinazione e il nome del file con un .estensione csv
4. Impostare la tabella o la query per estrarre i dati da
5. Impostare l’opzione che la prima riga contiene informazioni
6. Sullo schermo per selezionare il delimitatore, assicurarsi che la virgola è selezionato
7. Eseguire il pacchetto

Ora sei pronto per caricare il nuovo File CSV per SpatialKey.

Oracle:

Oracle è un database disponibile in commercio che ha un grande seguito nelle grandi aziende. Come SQL Server, Oracle è un database pronto per l’impresa. Le opzioni per l’estrazione in CSV utilizzando Oracle includono l’utilizzo di Oracle SQL Developer (uno strumento gratuito di Oracle) e l’utilizzo diretto di PL/SQL (generalmente creando una stored procedure in quanto è un po ‘ più basata sul codice rispetto alle altre soluzioni di database).

Oracle SQL Developer:

Questo è di gran lunga il modo più semplice per chi non ha familiarità con complesse stored procedure PL / SQL per esportare i dati.

Un modo per farlo è quello di creare un report in SQL Developer ed esportare i risultati

1. Una volta che il report è stato creato ed eseguito, fare clic destro sulla griglia dei risultati e scegliere “Esporta” dal menu contestuale
2. Nella schermata successiva selezionare CSV come formato e inserire il nome del file.csv e posizione

È anche possibile esportare una singola tabella:

1. Fare clic con il pulsante destro del mouse sul nome della tabella nella vista ad albero degli oggetti.
2. Selezionare Esporta.
3. Seleziona CSV. Viene visualizzata la finestra Esporta dati.
4. Fare clic sulla scheda Formato.
5. Seleziona Formato come: CSV
6. Immettere il nome e la posizione di un file.
7. Fare clic sulla scheda Colonne.
8. Controllare le colonne che si desidera esportare
9. Fare clic su Dove tab e aggiungere eventuali criteri necessari per filtrare i dati
10. Fare clic su Applica.

PL/SQL:

Il modo migliore per esportare i dati tramite PL / SQL è creare una stored procedure che utilizza il comando UTL_FILE.

Per un ottimo esempio di stored procedure per questo, si prega di consultare questo articolo a oracle.com. Si noti che è consigliata una modifica alla stored procedure originale situata nei commenti (gestisce correttamente la citazione dei dati con caratteri CSV riservati).

Ora sei pronto per caricare il nuovo file CSV su SpatialKey.

The Enterprise Data Publisher:

Per i nostri clienti aziendali che dispongono di grandi quantità di dati da importare o necessitano di dati aggiornati frequentemente in SpatialKey, offriamo un prodotto aggiuntivo chiamato Enterprise Data Publisher. Si tratta di un prodotto basato su servizi che viene eseguito dietro le quinte (e il firewall) che può essere programmato per inviare automaticamente i dati a SpatialKey dai database. Se siete interessati a saperne di più sulle capacità e il costo del Data Publisher aziendale vi preghiamo di inviarci una e-mail andando alla nostra pagina Contatti. Saremo lieti di aiutarvi con le vostre esigenze di dati!

In conclusione, SpatialKey ha bisogno dei tuoi dati per brillare! Produrre dati da un database esistente non deve essere un compito arduo come mostrano i passaggi sopra elencati ed è un primo passo importante per sbloccare veramente i tuoi dati!