Générer des instructions Insert SQL à partir d'un fichier csv
j'ai besoin d'importer un fichier csv dans Firebird et j'ai passé quelques heures à essayer quelques outils et aucun adapter à mes besoins.
le problème principal est que tous les outils que j'ai essayé comme EMS Data Import et Firebird Data Wizard s'attendre à ce que mon csv fichier contient toutes les informations nécessaires à ma Table.
j'ai besoin d'écrire quelques personnalisé SQL dans l'instruction insert, par exemple, j'ai un fichier cvs avec le nom de la ville, mais comme ma base de données a déjà toutes les villes dans une autre table (normalisée), j'ai besoin d'écrire une sous-sélection dans l'instruction insert de recherche pour la ville et écrire son ID, aussi, j'ai une procédure stockée pour cread GUID.
mon Insert serait quelque chose comme ceci:
INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES((SELECT NEW_GUID FROM CREATE_GUID), :NAME, (SELECT CITY_ID FROM CITY WHERE NAME = :CITY_NAME)
je sais qu'il est très facile d'écrire une application pour le faire, mais je n'aime pas de réinventer la roue, et je suis sûr qu'il y a quelques outils pour le faire.
vous pouvez me donner un conseil?
14 réponses
C'est un peu grossier - mais pour un seul travail, J'utilise parfois Excel.
si vous importez le fichier CSV dans Excel, vous pouvez créer une formule qui crée une instruction INSERT en utilisant la concaténation chaîne dans la formule. Donc-si votre fichier CSV a 3 colonnes qui apparaissent dans les colonnes A, B et c dans Excel, vous pouvez écrire une formule comme...
= " INSERT in MyTable (Col1, Col2, Col3) VALUES ("&A1 & ", " & B1 & ", " & C1 & ")"
alors vous pouvez répliquer la formule dans toutes vos lignes, et copier et coller la réponse dans un fichier texte pour exécuter contre votre base de données.
comme je dis - c'est grossier - mais il peut être tout à fait un moyen "rapide et sale" de faire un travail fait!
j'ai parfois l'utilisation de Le Monde est plus Simple Générateur de Code (Javascript édition) . C'est en ligne, mais c'est juste du javascript - vos données ne vont nulle part. Il y a aussi une version asp cependant, avec plus de fonctionnalités.
bien, si c'est un CSV, et il s'agit d'un processus unique, ouvrez le fichier dans Excel, puis écrivez des formules pour peupler vos données de n'importe quelle façon que vous désirez, puis écrivez une formule Concat simple pour construire votre SQL, et puis copiez cette formule pour chaque ligne. Vous obtiendrez un grand nombre d'instructions SQL que vous pouvez exécuter n'importe où.
Fabio,
j'ai fait ce que Vaibhav a fait de nombreuses fois, et c'est un bon moyen "rapide et sale" pour obtenir des données dans une base de données.
si vous avez besoin de faire ceci quelques fois, ou sur un certain type de programme, alors une façon plus fiable est de charger les données CSV" telles quelles " dans une table de travail (I. e customer_dataload) et ensuite utiliser des instructions SQL standard pour remplir les champs manquants.
(Je ne connais pas la syntaxe Firebird - mais quelque chose comme...)
UPDATE person
SET id = (SELECT newguid() FROM createguid)
UPDATE person
SET cityid = (SELECT cityid FROM cities WHERE person.cityname = cities.cityname)
etc.
Généralement, il est beaucoup plus rapide (et plus fiable) pour obtenir les données dans la base de données et ensuite corriger les données que d'essayer de corriger les données pendant le téléchargement. Vous obtenez également l'avantage des transactions pour vous permettre de revenir en arrière si cela ne fonctionne pas!!
vous pouvez importer le fichier CSV dans une table telle quelle, puis écrire une requête SQL qui fait toutes les transformations nécessaires sur la table importée et insère le résultat dans la table cible.
Donc quelque chose comme:
<(charger le fichier CSV dans temp_table - n, city_name)>
insérer dans target_table
sélectionner T. n, c. city_id as city
de l' temp_table t, villes c
où T. city_name = C. nom de la ville
bon conseil sur L'utilisation D'Excel, mais je suggère aussi de se familiariser avec un langage de script comme Python, parce que pour certaines tâches, il est plus facile d'écrire un script python rapide pour faire le travail que d'essayer de trouver la fonction dont vous avez besoin dans Excel ou un outil pré-fait qui fait le travail.
j'utilise une légère variation sur le ballon Excel technique .
I highly je recommande le téléchargement du free ASAP Utilities plug-in pour Excel. L'un des nombreux outils d'économie de temps qu'ils comprennent sont insérer avant la valeur actuelle et insérer après la valeur actuelle options.
ceux - là devraient vous laisser trouvez une solution plus rapide en vous aidant à construire vos instructions insert.
utilisez le fichier csv comme une table externe. Ensuite, vous pouvez utiliser SQL pour copier les données de la table externe vers votre table de destination - avec toutes les possibilités de SQL. Voir http://www.firebirdsql.org/index.php?op=useful&id=netzka
vient de terminer ce script VBA qui pourrait être pratique à cet effet. Tout ce qu'il faut faire, c'est Modifier L'instruction Insert pour inclure le tableau en question et la liste des colonnes (évidemment dans le même ordre qu'elles apparaissent dans le fichier Excel).
Function CreateInsertStatement()
'Output file location and start of the insert statement
SQLScript = "C:\Inserts.sql"
cStart = "Insert Into Holidays (HOLIDAY_ID, NAT_HOLDAY_DESC, NAT_HOLDAY_DTE) Values ("
'Open file for output
Open SQLScript For Output As #1
Dim LoopThruRows As Boolean
Dim LoopThruCols As Boolean
nCommit = 1 'Commit Count
nCommitCount = 100 'The number of rows after which a commit is performed
LoopThruRows = True
nRow = 1 'Current row
While LoopThruRows
nRow = nRow + 1 'Start at second row - presuming there are headers
nCol = 1 'Reset the columns
If Cells(nRow, nCol).Value = Empty Then
Print #1, "Commit;"
LoopThruRows = False
Else
If nCommit = nCommitCount Then
Print #1, "Commit;"
nCommit = 1
Else
nCommit = nCommit + 1
End If
cLine = cStart
LoopThruCols = True
While LoopThruCols
If Cells(nRow, nCol).Value = Empty Then
cLine = cLine & ");" 'Close the SQL statement
Print #1, cLine 'Write the line
LoopThruCols = False 'Exit the cols loop
Else
If nCol > 1 Then 'add a preceeding comma for all bar the first column
cLine = cLine & ", "
End If
If Right(Left(Cells(nRow, nCol).Value, 3), 1) = "/" Then 'Format for dates
cLine = cLine & "TO_DATE('" & Cells(nRow, nCol).Value & "', 'dd/mm/yyyy')"
ElseIf IsNumeric(Left(Cells(nRow, nCol).Value, 1)) Then 'Format for numbers
cLine = cLine & Cells(nRow, nCol).Value
Else 'Format for text, including apostrophes
cLine = cLine & "'" & Replace(Cells(nRow, nCol).Value, "'", "''") & "'"
End If
nCol = nCol + 1
End If
Wend
End If
Wend
Close #1
End Function
vous pouvez utiliser le gratuit csvsql pour ce faire.
- installez-le en utilisant ces instructions
-
exécutez maintenant une commande comme so pour importer vos données dans votre base de données. Plus de détails sur les liens ci-dessus, mais ce serait quelque chose comme:
csvsql --db firebase:///d=mydb --insert mydata.csv
-
ce qui suit fonctionne avec sqlite, et est ce que j'utilise pour convertissez les données dans un format de requête facile
csvsql --db sqlite:///dump.db --insert mydata.csv
option 1: 1 - Avez-vous essayé IBExert? IBExpert \ Tools \ Import Data (version D'essai ou version client).
option 2: 2-Téléchargez votre fichier csv sur une table temporaire avec F_BLOBLOAD. 3-Créer une procédure stockée, qui utilise 3 fonctions (f_stringlength, f_strcopy, f_MID) vous croisez toute votre corde, tirant vos champs pour construire votre INSERT.
liens: 2: http://freeadhocudf.org/documentation_english/dok_eng_file.html 3: http://freeadhocudf.org/documentation_english/dok_eng_string.html
je le ferais avec awk .
par exemple, si vous aviez cette information dans un fichier CSV:
Bob,New York
Jane,San Francisco
Steven,Boston
Marie,Los Angeles
la commande suivante vous donnera ce que vous voulez, exécutez dans le même répertoire que votre fichier CSV (nommé name-city.csv
dans cet exemple).
$ awk -F, '{ print "INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES ((SELECT NEW_GUID FROM CREATE_GUID), '\''""'\'', (SELECT CITY_ID FROM CITY WHERE NAME = '\''""'\''))" }' name-city.csv
Type awk --help
pour plus d'information.