bulk import double quotes in some columns, not in first row
PROBLEM:
my bulk import skip first line after the header, because it think it is part of the header dû to the separators in the FMT file
plz do not suggest other way around, other than completly rewriting my CSV file with a header that include the same separators
any other suggestions will be answered by a VERY COMPLICATED explanation as why i cannot do it your with rowset, BCP, etc etc
SITUATION:
as a start you can read this question, it is similar to mine, but his data file does not have a HEADER line
https://www.experts-exchange.com/questions/23377319/BULK-INSERT-comma-delimited-file-Need-to-ignore-commas-in-quotations.html?sfQueryTermInfo=1+10+begin+bulk+doe+first+import+row+where
so my data look like this (code snippet)
my query is this:
BULK INSERT [mag_expeditions_aef].dbo.
FROM 'c:\aef\entete.csv'
WITH (FORMATFILE = 'C:\aef\entete.Fmt', CODEPAGE ='RAW', DATAFILETYPE = 'char', FIRSTROW=2)
my FMT file is like this:
9.0
15
1 SQLCHAR 0 12 ";\"" 1 CODEINT ""
2 SQLCHAR 0 32 "\";\"" 2 LANGUE French_CI_AS
3 SQLCHAR 0 32 "\";\"" 3 TITRE French_CI_AS
4 SQLCHAR 0 32 "\";\"" 4 NOMCSV French_CI_AS
5 SQLCHAR 0 32 "\";\"" 5 NOMRPT French_CI_AS
6 SQLCHAR 0 32 "\";\"" 6 DIVISION French_CI_AS
7 SQLCHAR 0 0 "\";\"" 7 PARTEX1 French_CI_AS
8 SQLCHAR 0 12 "\";\"" 8 CODEDEB ""
9 SQLCHAR 0 32 "\";\"" 9 CODEFIN French_CI_AS
10 SQLCHAR 0 32 "\";\"" 10 DATEDEB French_CI_AS
11 SQLCHAR 0 32 "\";\"" 11 DATEFIN French_CI_AS
12 SQLCHAR 0 0 "\";" 12 PARTEX2 French_CI_AS
13 SQLCHAR 0 12 ";" 13 PARNUM1 ""
14 SQLCHAR 0 12 ";" 14 PARNUM2 ""
15 SQLCHAR 0 12 "\r\n" 15 PARNUM3 ""
my bulk import skip first line after the header, because it think it is part of the header dû to the separators in the FMT file
plz do not suggest other way around, other than completly rewriting my CSV file with a header that include the same separators
any other suggestions will be answered by a VERY COMPLICATED explanation as why i cannot do it your with rowset, BCP, etc etc
SITUATION:
as a start you can read this question, it is similar to mine, but his data file does not have a HEADER line
https://www.experts-exchange.com/questions/23377319/BULK-INSERT-comma-delimited-file-Need-to-ignore-commas-in-quotations.html?sfQueryTermInfo=1+10+begin+bulk+doe+first+import+row+where
so my data look like this (code snippet)
my query is this:
BULK INSERT [mag_expeditions_aef].dbo.
FROM 'c:\aef\entete.csv'
WITH (FORMATFILE = 'C:\aef\entete.Fmt', CODEPAGE ='RAW', DATAFILETYPE = 'char', FIRSTROW=2)
my FMT file is like this:
9.0
15
1 SQLCHAR 0 12 ";\"" 1 CODEINT ""
2 SQLCHAR 0 32 "\";\"" 2 LANGUE French_CI_AS
3 SQLCHAR 0 32 "\";\"" 3 TITRE French_CI_AS
4 SQLCHAR 0 32 "\";\"" 4 NOMCSV French_CI_AS
5 SQLCHAR 0 32 "\";\"" 5 NOMRPT French_CI_AS
6 SQLCHAR 0 32 "\";\"" 6 DIVISION French_CI_AS
7 SQLCHAR 0 0 "\";\"" 7 PARTEX1 French_CI_AS
8 SQLCHAR 0 12 "\";\"" 8 CODEDEB ""
9 SQLCHAR 0 32 "\";\"" 9 CODEFIN French_CI_AS
10 SQLCHAR 0 32 "\";\"" 10 DATEDEB French_CI_AS
11 SQLCHAR 0 32 "\";\"" 11 DATEFIN French_CI_AS
12 SQLCHAR 0 0 "\";" 12 PARTEX2 French_CI_AS
13 SQLCHAR 0 12 ";" 13 PARNUM1 ""
14 SQLCHAR 0 12 ";" 14 PARNUM2 ""
15 SQLCHAR 0 12 "\r\n" 15 PARNUM3 ""
CODEINT;LANGUE;TITRE;NOMCSV;NOMRPT;DIVISION;PARTEX1;CODEDEB;CODEFIN;DATEDEB;DATEFIN;PARTEX2;PARNUM1;PARNUM2;PARNUM3
1;"Français";"Statistiques ventes";"StatsVentesProd2";"StatsVentesProd";"StasChic";"Prod";"";"ZZZZZZZ";"2007/09/01";"2009/05/05";"Client";0;7;1
2;"Français";"Statistiques ventes";"StatsVentesProd2";"StatsVentesProd";"StasChic";"Prod";"";"ZZZZZZZ";"2007/09/01";"2009/05/05";"Client";0;7;2
3;"Français";"Statistiques ventes";"StatsVentesProd2";"StatsVentesProd";"StasChic";"Prod";"";"ZZZZZZZ";"2007/09/01";"2009/05/05";"Client";0;7;1
4;"Français";"Statistiques ventes";"StatsVentesProd2";"StatsVentesProd";"StasChic";"Prod";"";"ZZZZZZZ";"2007/09/01";"2009/05/05";"Client";0;7;2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
and here is my new sql 2005 bulk import script
(you have to read how it work in the original script link or you will be lost in space)
https://www.experts-exchange.com/questions/23931018/Converting-Excel-Document-Values-to-be-ready-for-MySQL-Import.html?sfQueryTermInfo=1+10+csv+wildboy85
(you have to read how it work in the original script link or you will be lost in space)
https://www.experts-exchange.com/questions/23931018/Converting-Excel-Document-Values-to-be-ready-for-MySQL-Import.html?sfQueryTermInfo=1+10+csv+wildboy85
'=== main program procedure:.
'=== main program procedure:.
' 1. read the sql formats: csv_to_sql_import_formats.csv
' 2. read the list of files to import: csv_to_sql_liste_fichier_a_importer.txt
' 3. open sql database with admin password and dns adress
' 4. read a file with the name of the CSV + "_schema" and use it to know the format of columns to create sql database
' you must create this file manually before importation
' in this file, there is a column format number wich is +1000, its destined to be the main key for database
' (i did not add the sql code to define the main key)
' 5. if database does not exist, we create it
' 6. if column for main key does not exist, we create it
' 7. search in main key for a key identical, if not found, create the record, if found, update record
test=0 '=== 0 write in database id test=1
dim upd_ii, sql1, dummy, iq
iq=1
'=== take name of the sql server from a window variable
sql1=environ("stassql1")
upd_ii=10 '=== update frequency for writing in report log database_log.txt
upd_ii=10 '=== update frequency for writing in report log database_log.txt
ecmax=10 '=== nbr of max try to connect to database
Dim objFSO, file_02, ligne,a
Set objshe = CreateObject("WScript.Shell")
Set objEnv = objshe.Environment("PROCESS")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'=== actual drive, actual directory, and "\"
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir = left(thepath,p)
filnam = right(thepath,len(thepath)-p)
'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
a64 = windir & "\syswow64\wscript.exe"
if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
'=== 64 bits system
a = """" & a64 & """ """ & basedir & filnam & """"
objshe.Run a,0, false
wscript.quit
end if
'=== reactivate security chek for zone
if mid(basedir,1,2)="c:" then local=1 else local=0
base_dir = basedir
b = mid(basedir,1,len(basedir)-1)
p = instrRev(b,"\")
basename = right(b,len(b)-p)
'=== restart in 32 bits if in 64 mode
'=== windows dir
'msgbox(p & " --- " & basename & " --- " & basedir)
'=== _liste_fichier_a_importer.txt (this file contain the list of file to import)
' contient:
' 01 name of database (is also the name of directory where to get the csv)
' 02 root directory
' 03 file name (is also "_" + the name of the database, cause what i import is for magica)
sqlprefix = "sp_configure 'show advanced options',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO " & vbcrlf
sqlprefix = sqlprefix & "sp_configure 'Ad Hoc Distributed Queries',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO "
sqlprefix = sqlprefix & "SET ANSI_NULLS OFF"
sqlprefix = sqlprefix & "GO"
catfile=basedir & "csv_to_sql_liste_fichier_a_importer.txt"
if objfso.fileexists(catfile) then
Set file_02 = objFSO.OpenTextFile(catfile, 1, 0)
else
msgbox("ERREUR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "n'existe pas, on ne peux pas commencer")
' msgbox("ERROR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "list of file to import does not exist, cannot start import")
wscript.quit
end if
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objfso.OpenTextFile(base_dir & a, 2, true)
If Err.number <> 0 Then
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
on error goto 0
set fso=nothing
'=== LOOP for folders ================================================
objOutputFile02.WriteLine date & " " & time & " DEBUT"
timertotal=timer
delaistotal=0
'=== read database name
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\")=0 and instr(ligne,".")=0 Then
db_main=ligne
End If
end if
'=== read directory where the csv is
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\") Then
ligne =lcase(ligne)
if ligne = "m:\temp" then
ligne = "\\corp.stas.local\stas\Magica\Temp"
end if
if local=0 then
db_dir = ligne & "\" & db_main
else
'=== if we are local there is no subdir
db_dir = ligne
end if
End If
eND if
'=== add "mag_" at the start of database name, cause the importation is for a software called "magica"
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
Dim con_02
Set con_02 = CreateObject("ADODB.Connection")
set tag = CreateObject ("ADODB.Recordset")
con_02.ConnectionString = "Driver={SQL Server};Server=sql.corp.stas.local"
errdel=20
con_02.connectiontimeout=errdel
on error resume next
con_02.Open
i=0
do while err<>0 and i<10
con_02.Open
err.clear
i=i+1
if i>10 then
'objOutputFile.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.end
end if
loop
on error goto 0
'=== expiration de requete sql en secondes
'=== 1 2 3 4 5 (min)
'=== 60,120,180,240,300 (sec)
con_02.commandtimeout=300
'=== DATABASE === cherche la database, pour voir si elle existe
'=== verify is database exist, in the table containing all database name for all sql server
'=== the name of this table might differ for a my_sql server
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db & "'"
set tag = con_02.execute(sql)
'=== db create
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db & "]"
set tag = con_02.execute(sql)
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
else
'msgbox("la database existe déjà, pas supposé")
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db
end if
'=== create a temporary database for bulk import
nom_db2 = nom_db & "_aef"
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db2 & "]"
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db2
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
on error resume next
set tag = con_02.execute(sql)
if err<>0 then
a = err.description
end if
on error goto 0
if instr(a,"model") then
'=== chek if model database is locked by a bugged application
'=== a count of more than 0 results = something locked the database
objOutputFile02.WriteLine date & " " & time & " model is locked by another application - killing all SPID that lock MODEL"
s = "select spid, hostname, program_name from master..sysprocesses where db_name(dbid) = 'model'"
set tag = con_02.execute(s)
while not(tag.eof)
objOutputFile02.WriteLine date & " " & time & " killing: " & tag.fields(0) & " " & tag.fields(1) & " " & tag.fields(2)
'=== kill the process that lock MODEL
s = "kill " & tag.fields(0)
set tag02 = con_02.execute(s)
tag.movenext
wend
set tag = con_02.execute(sql)
else
objOutputFile02.WriteLine date & " " & time & "ERROR with the first request on the database server" & vbcrlf & a & vbcrlf & "QUITTING PROGRAM"
wscript.quit
end if
else
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db2
sql = "drop DATABASE [" & nom_db2 & "]"
set tag = con_02.execute(sql)
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been dropped " & nom_db2
else
objOutputFile02.WriteLine date & " " & time & " ERROR cannot drop database " & nom_db2
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
end if
sql = "CREATE DATABASE [" & nom_db2 & "]"
set tag = con_02.execute(sql)
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") <> 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been created " & nom_db2
end if
end if
'=== be sure to "use" this database just in case i forgot to specifie database name in a command
sql = "USE " & nom_db
Do While file_02.AtEndOfStream <> True
'=== read first table
ligne = file_02.Readline
do while mid(ligne,1,1)="'"
ligne = file_02.Readline
loop
'=== copy the csv locally, or else we cannot import, because the service sql must access the file, not the actual user
sou = db_dir & "\" & ligne
des = "c:\aef\" & left(ligne,len(ligne)-4) & "_aef.csv"
objFSO.copyFile sou, des, TRUE
if not objfso.fileexists(des) then
objOutputFile02.WriteLine date & " " & time & " fichier pas copie: " & des
objOutputFile02.WriteLine date & " " & time & " script arrete "
fatal01=1
end if
a = "z_csv_to_sql_log_" & nom_table & basename & ".txt"
on error resume next
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
nom_table_suf = "_schema" '=== ajoute _schema pour le fichier de quel type sont chaque table
'=== change fichier (table)
'=== change file (the name of file is also the name of the table)
'=== name of table, remove the ".csv" part
nom_table = mid(ligne,1, len(ligne) -(len(ligne) -(instr(ligne,".")-1)))
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " debut importation " & db_dir
dim sql
'=== open a log file, create it if not there
'=== 8 = append === 2 = newfile
'on error resume next
on error goto 0
objOutputFile02.WriteLine date & " " & time & " ouverture log table " & base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt"
Set objOutputFile = objfso.OpenTextFile(base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt", 2, true)
If Err.number <> 0 Then
Set objOutputFile = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
objOutputFile.WriteLine date & " " & time & " " & "debut"
on error goto 0
'=== CSV connexion - input data
'=== FUTUR: verifier si fichier existe avant de l'ouvrir
Set Con_01 = CreateObject("ADODB.Connection")
on error resume next
Con_01.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & db_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
if err<>0 then
on error goto 0
msgbox("error fatal cannot open directory" & vbcrlf & db_dir)
wscript.quit
end if
'=== erreur si communication marche pas
on error resume next
Set RS_01 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir la connection vers tous les csv " & db_dir
MSGBOX("ERREUR FATALE" & vbcrlf & "pas capable d'ouvrir un csv en lecture" & vbcrlf & db_Dir)
end if
on error goto 0
'=== table de format sql à importer pour stas numérotés de 0 à 22 et +
'=== table containing the sql format as number for faster creation of "_schema" files
Set Con_03 = CreateObject("ADODB.Connection")
Con_03.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
Set RS_03 = CreateObject ("ADODB.Recordset")
sql = "SELECT * from csv_to_sql_import_formats.csv"
'=== reset fatal error counter
fatal01=0
set rs_03 = con_03.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
fatal01=1
end if
'=== _schema . csv
'=== import schema, contain number for format
Set Con_04 = CreateObject("ADODB.Connection")
'====== test
Con_04.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'HDR=YES;FMT=Delimited
'Con_04.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & base_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
on error resume next
Set RS_04 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== _schema . CSV === ouvre la table des types de colonnes à créer
sql = "SELECT * from " & nom_table & nom_table_suf & ".csv"
on error resume next
set rs_04 = con_04.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== SQL
'=== crée une database par rapport
'=== crée une table par fichier csv (voir csv_to_sql_liste_fichier_a_importer.txt, dans le dossier d'origine)
'=== will create one database for every directory to import
'=== will create one table for every file to import
'=== rs = record set = contient un record, une ligne de database
Set RS = CreateObject ("ADODB.Recordset")
Set SQLStmt = CreateObject("ADODB.Command")
'================================================================ sql ======================================
'=== connexion serveur sql, sans se connecter sur une database
'=== MAIN SQL DATABASE CONNECTION
'=== if there was anny fatal error with the csv files, this part is not executed
if fatal01=0 then
'=== read CSV file
sql = "SELECT * from " & nom_table & ".csv"
on error resume next
'=== colonne
set rs_01 = con_01.execute(sql)
'=== data csv
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & rs_01(table_clef).name
objOutputFile02.WriteLine date & " " & time & " " & db_main & " csv vide de contenu "
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
End If
on error goto 0
'=== primary key column (format >999 + format type)
table_clef=-1
i=0
while i<RS_04.fields.count
'msgbox(rs_04(i) & vbcrlf & i)
'msgbox(RS_04(i))
if rs_04(i)>999 then
table_clef=i
'msgbox(table_clef)
end if
i=i+1
wend
'=== did not find any primary key - stopping import
if table_clef=-1 then
'=== stop program is there is no master key
a="vous avez oublié de spécifier une clef de base dans votre base de donnée soit un format +1000"
'msgbox(a)
objOutputFile.WriteLine date & " " & time & a & nom_db
objOutputFile02.WriteLine date & " " & time & " pas de table clef -- le script est arrêté!!!"
Wscript.quit
end if
'=== verify if table exist, if not create it with FIRST OCOLUMN, not primary key anymore (bulk insert requirement)
if rs_04(0)>999 then
format01 = rs_04(0)-1000
else
format01 = rs_04(0)
end if
dummy = VER_CRE_table(nom_db, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== CREATE TABLES
'=== verify if table exist, if not create it with primary key
objOutputFile02.WriteLine date & " " & time & " CREATION table " & nom_table
'=== create AEF (to delete) table for importations
dummy = VER_CRE_table(nom_db2, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== > verify if number of columns in CSV are same that in SCHEMA
exactcolumn =0
if exactcolumn =1 then
if RS_04.fields.count>RS_01.fields.count then
A=" le nombre de colonnes dans le csv d'origine est < que celui du schema"
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & " " & a
a=" " & RS_04.fields.count-RS_01.fields.count & " colonne(s) en trop sera(ont) crée(s) "
objOutputFile.WriteLine date & " " & time & " " & a & nom_db
a=""
b=""
i=0
y=0
while i<RS_04.fields.count
a = a + " " + rs_04(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + string(len(rs_01(y).name),"_")
else
b = b + " " + rs_01(y).name
y=y+1
end if
err.clear
If Err <> 0 Then
b = b + space(len(rs_04(i)))
err.clear
End If
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & b
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 1 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== < verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count<RS_01.fields.count then
'=== rs_01 = csv === rs_04 = schema
A = " le nombre de colonnes dans le csv d'origine est > que celui du schema"
a = a & vbcrlf & RS_04.fields.count &" "& RS_01.fields.count& " "
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & a & nom_db
a = " impossible de continuer car il vous manquera des données "
objOutputFile.WriteLine date & " " & time & a & nom_db
a=""
b=""
I=0
y=0
while i<RS_01.fields.count
a = a + " " + cstr(i) + " " + rs_01(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + " " & string(len(rs_01(y).name),"_")
else
b = b + " " + cstr(y) + " " + rs_04(y).name
y=y+1
end if
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 2 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
'end if
'=== = verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count=RS_01.fields.count then
a=""
b=""
I=0
y=0
tag=0
while i<RS_01.fields.count
a = a + " " + rs_01(i).name
b = b + " " + rs_04(y).name
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
tag=1
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
end if
on error goto 0
i=i+1
y=y+1
wend
if tag=1 then
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 3 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== column creation
i=0
while i<RS_04.fields.count
'=== clef primaire = 0 ou 1
a=rs_04(i)
on error resume next
if a>999 then
a=a-1000
end if
'=== make a new column (at this point, the table and key column is already done)
dummy = VER_CRE_col(nom_db, nom_table, rs_03(a), rs_04(i).name, 0)
'=== make a new column (at this point, the table and key column is already done)
'***************************************************************************************************
dummy = VER_CRE_col(nom_db2, nom_table, rs_03(a), rs_04(i).name, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " database..: -> " & nom_db & " <- nomfisrtcolumn..: ->" & rs_04(i).name & "<- numero clef unique..: "
objOutputFile.WriteLine date & " " & time & " -i- " & i & " -a- " & a & " -count- " & RS_04.fields.count
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 4 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
i=i+1
on error goto 0
wend
'=== define primary key
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
'not null
'ALTER TABLE supplier
'MODIFY supplier_name varchar2(100) not null;
s = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
'=== table [employés] is a mix of ldap and csv, so we dont have any primary key
if nom_db <> "mag_employés" and nom_table <>"personnes" then
s = "use " & nom_db & vbcrlf
'objOutputFile.WriteLine date & " " & time & " " & s
'b = retsql(s,"count table clef")
s = s & "select count(c.COLUMN_NAME) as col01 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, "
s = s & "INFORMATION_SCHEMA.KEY_COLUMN_USAGE c " & vbcrlf
s = s & "where pk.TABLE_NAME = '" & nom_table & "' "
s = s & "and CONSTRAINT_TYPE = 'PRIMARY KEY' "
s = s & "and c.TABLE_NAME = pk.TABLE_NAME "
s = s & "and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME "
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"count table clef")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " number of primary key: " & recimp
if recimp=0 then
'=== no primary key, we create one
s = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
end if
if nom_db2 = "mag_employés_aef" and nom_table ="personnes" then
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [codeint] int not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([codeint])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
else
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
ii=0
'=== new bulk import here
'=== copy file locally on sql server (stas-host-01), strangely, it work only locally
fil01 = db_dir & "\" & nom_table & ".csv"
fil01 = nom_table & ".csv"
TABaef = nom_table
nom_db2 = nom_db & "_aef"
'=== empty the AEF table to be sure
sql = "SELECT COUNT(*) As 'DBCount' FROM [" & nom_db2 & "].dbo.[" & tabaef & "]"
set tag = con_02.execute(sql)
if tag("DBCount") > 0 Then
objOutputFile02.WriteLine date & " " & time & " database is not empty, drop failed " & nom_db2
objOutputFile02.WriteLine date & " " & time & " countermesures: delete all item in it " & nom_db2
sql = "delete from [" & nom_db2 & "].dbo.[" & tabaef & "]"
objOutputFile02.WriteLine date & " " & time & sql
set tag = con_02.execute(sql)
end if
's = sqlprefix & "" & vbcrlf
s = ""
's = s & "INSERT INTO " & nom_db2 & ".dbo.[" & tabaef & "] " & vbcrlf
'=============== ----------- ============== -------------- =============== -------------
'=== new technique with objects
'//This is the connection string to connect to your csv file
'string strConString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\; Extended Properties=\ Text;HDR=YES;\ ";
'// open connection
'OleDbConnection oCon = new OleDbConnection(strConString);
'oCon.Open();
'// fill data set
'//csvfile should be present in c:
'string strSql = SELECT * FROM csvfile.csv ;
'OleDbDataAdapter oDA = new OleDbDataAdapter(strSql, oCon);
'DataSet oData = new DataSet();
'oDA.Fill(oData, ABC );
'GridView1.DataSource = oData;
'GridView1.DataBind();
'oCon.Close();
'LOAD DATA INFILE 'c:/abc.csv' INTO TABLE stu
'FIELDS TERMINATED BY '\t'
'LINES TERMINATED BY '\n';
' Set Con_05 = CreateObject("ADODB.Connection")
' Con_05.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\aef\; Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'BULK INSERT mag_employés_Aef.dbo.personnes FROM 'c:\aef\personnes.csv' WITH (FIRSTROW=2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')
'go
's = s & "SET QUOTED_IDENTIFIER OFF" & vbcrlf
's = s & "go" & vbcrlf
'DATAFILETYPE = 'char',
's = s & "SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & VBCRLF
'S = S & "'Text;Database=C:\AEF\;HDR=YES;FMT=Delimited;IMEX=1','SELECT " & s2 & " FROM " & fil01 & "')" & VBCRLF
'if nom_db2 = "mag_employés_aef" and tabaef="personnes" then
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL) AND (STATDESA = '' OR STATDESA IS NULL) AND (nom IS NOT NULL)"
'else
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL)"
'end if
'=== FMT format file
'9.0
'12
'1 SQLCHAR 0 12 ";\"" 1 CODEINT ""
'2 SQLCHAR 0 32 "\";\"" 2 DIM1TXT French_CI_AS
'3 SQLCHAR 0 32 "\";" 3 DIM2TXT French_CI_AS
'4 SQLCHAR 0 30 ";" 4 SETUP ""
'5 SQLCHAR 0 30 ";\"" 5 UNIT ""
'6 SQLCHAR 0 32 "\";\"" 6 SETUPTXT French_CI_AS
'7 SQLCHAR 0 32 "\";\"" 7 UNITTXT French_CI_AS
'8 SQLCHAR 0 32 "\";" 8 VENTFIN French_CI_AS
'9 SQLCHAR 0 30 ";\"" 9 UNITLOC ""
'10 SQLCHAR 0 0 "\";\"" 10 COMMENTA French_CI_AS
'11 SQLCHAR 0 128 "\";\"" 11 ALPHA French_CI_AS
'12 SQLCHAR 0 0 "\"\r\n" 12 DIVERS1 French_CI_AS
a = tabaef & ".fmt"
'=== bcp generate first FMT automatically
a = "bcp [" & nom_db2 & "].dbo.[" & tabaef & "] format nul -c -t; -f c:\aef\" & tabaef & "_aef.Fmt -T -S stas-sql-01"
objOutputFile.WriteLine date & " " & time & vbcrlf & vbcrlf & a & vbcrlf
objshe.run a,0, true
on error resume next
'=== read original FMT (_aef)
Set objfil03 = objFSO.OpenTextFile("c:\aef\" & tabaef & "_aef.Fmt", 1, 0)
'=== read CSV separate columns = lines for FMT file
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & tabaef & "_aef.csv", 1, 0)
'=== skip column name
a = objfil04.readline
'=== line to find separators, double quote for char, nothing for numbers
'=== must read char by char, until we met char(13) and char(10)
'c = objfil04.readline
'=== tagret = if we meet chr(13) and chr(10), it will equal to 2
tagret = 0
'=== chr(10) is not a end of line, just a change of line inside a string
'=== chr(13) and chr(10) is a END OF LINE
c = ""
while tagret < 2
if not objFil04.AtEndOfStream then
a = objFil04.Read(1)
if tagret=1 then
if a=chr(10) then
tagret = tagret + 1
end if
else
tagret = 0
end if
if a<>chr(13) then
c = c & a
else
tagret = tagret + 1
end if
end if
wend
objfil04.close
'=== write new FMT file with good separators
Set objfil05 = objfso.OpenTextFile("c:\aef\" & tabaef & ".fmt", 2, true)
If Err.number <> 0 Then
'=== cannot open fmt destination file
end if
on error goto 0
'=== sql version
a = objFil03.readLine
objFil05.WriteLine a
'=== number of columns
a = objFil03.readLine
objFil05.WriteLine a
i=0
b = RS_04.fields.count
'=== loop all line from original FMT file
'=== at the same time we analyse the variable C (second line from data file) to find all separators
inside = 0 '=== we are inside "" ignore the ;
sep = "" '=== separator between this field and the next
end01 = ""
end02 = ";"
end03 = ""
'=== dummy string with all the good separators to replace first line (column titles) in csv
'=== if we do not replace this line, first REAL data line is skipped
dum01 = ""
objOutputFile.WriteLine date & " " & time & " line to analyse: " & c & vbcrlf
while i<b
a = objFil03.readLine
'=== loop in C chain to find any ";" outside "" and all the ""
if mid(c,1,1)="""" then
inside = 1
end01 = "\"""
c = right(c,len(c)-instr(c,""";")-1)
'objOutputFile.WriteLine date & " " & time & " line to analys2: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
else
c = right(c,len(c)-instr(c,";"))
'objOutputFile.WriteLine date & " " & time & " line to analys3: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
end if
if instr(a,""";""") then
a = replace(a,""";""","""" & end01 & end02 & end03 & """")
dum01 = dum01 & end01 & end02 & end03
dum01= replace(dum01,"\","")
elseif instr(a,"\r\n") then
a = replace(a,"""\r\n""","""" & end01 & "\r\n" & """")
end04 = replace(end01,"\","")
end05 = replace(end02,"\","")
end06 = replace(end03,"\","")
dum01 = dum01 & end04
end if
end01 = ""
end03 = ""
objFil05.WriteLine a
i = i + 1
wend
objfil03.close
objfil05.close
objOutputFile.WriteLine date & " " & time & " inserting a dummy line in place of the columns title line in the CSV" & vbcrlf
objOutputFile.WriteLine date & " " & time & " " & dum01 & vbcrlf
'sou = db_dir & "\" & ligne
'des = "c:\aef\" & left(ligne,len(ligne)-4) & "_aef.csv"
'objFSO.copyFile sou, des, TRUE
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & tabaef & "_aef.csv", 1, 0)
Set objfil05 = objfso.OpenTextFile("c:\aef\" & tabaef & ".csv", 2, true)
a = objFil04.readLine
objFil05.Write dum01 & chr(13) & chr(10)
objOutputFile.WriteLine date & " " & time & " START binary read" & vbcrlf
Do Until objFil04.AtEndOfStream
a = objFil04.Read(1)
objfil05.write a
Loop
objOutputFile.WriteLine date & " " & time & " END binary read" & vbcrlf
objfil05.close
objfil04.close
s = s & "BULK INSERT [" & nom_db2 & "].dbo.[" & tabaef & "] FROM 'c:\aef\" & fil01 & "' WITH (FORMATFILE = 'C:\aef\" & tabaef & ".Fmt', CODEPAGE ='RAW', DATAFILETYPE = 'char', FIRSTROW=2)"
objOutputFile.WriteLine date & " " & time & " " & " bulk insertion of all the CSV file, with a dynamic MFT" &vbcrlf & vbcrlf & s & vbcrlf
'msgbox("next: " & nom_db2 & vbcrlf & tabaef)
b = retsql(s,"bulk")
'=== REMOVE ALL QUOTES FROM EVERY COLUMNS IN THE TABLE WE JUST IMPORTED IN _AEF
'UPDATE "table_name"
'SET column_1 = [value1], column_2 = [value2]
'WHERE {condition}
s = "update [" & nom_db2 & "].dbo.[" & tabaef & "] set "
a = "[" & rs_04(0).name & "]"
objOutputFile.WriteLine date & " " & time & " clean up the double quote from first column, first char" & vbcrlf & vbcrlf & s & vbcrlf
'=== remove double quotes ""
'=== cannot use the remove double quote method, because there can be a column separator inside the double quotes , or ;
'b = retsql(s,"cleanup")
'=== verify importation (count elements in new table)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count import")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " " & recimp & " records from csv to " & nom_db2 & ".dbo.[" & TABAEF & "]"
'=== count records that will be updated
s = "SELECT count(*) "
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t1," & nom_db & ".dbo.[" & nom_table & "] t2"
s = s & " " & vbcrlf & "WHERE t1." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
b = retsql(s,"count update")
recUPD = b(0)
objOutputFile.WriteLine date & " " & time & " " & recupd & " UPDATES in " & nom_db & ".dbo.[" & nom_table & "]"
'=== query update
i=0
s = "UPDATE " & nom_db & ".dbo.[" & nom_table & "] "
s = s & vbcrlf & "SET "
b = RS_04.fields.count
while i<b
if i<>table_clef then
s = s & "[" & rs_04(i).name & "]"& "=t2.[" & rs_04(i).name & "]"
s = s & ","
end if
i = i + 1
wend
s = left(s,len(s)-1)
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 "
s = s & " " & vbcrlf & "WHERE " & nom_db & ".dbo.[" & nom_table & "]." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " update query" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== delete all updates done in aef table (temp)
s = "DELETE " & nom_db2 & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 " & vbcrlf
s = s & "INNER JOIN " & nom_db & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "ON " & nom_db & ".dbo.[" & TABAEF & "]." & rs_04(table_clef).name & " = t2." & rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " delete query (delete updated elements to insert the rest)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== count records left in temp table (will be inserts quantities)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count insert")
recins = b(0)
objOutputFile.WriteLine date & " " & time & " " & recins & " INSERT in " & nom_db & ".dbo.[" & nom_table & "]"
'=== insert all the rest
i=0
s2=""
b = RS_04.fields.count
while i<b
s2 = s2 & "[" & rs_04(i).name & "]"
i = i + 1
s2 = s2 & ","
wend
s2 = left(s2,len(s2)-1)
s = "INSERT INTO " & nom_db & ".dbo.[" & nom_table & "] " & vbcrlf
s = s & "(" & s2 & ") "
s = s & "SELECT "
s = s & s2
s = s & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] "
objOutputFile.WriteLine date & " " & time & " insert query (insert the remaining elements)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== fin condition fatal error
end if
'=========================== end of main loop ===========================================
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
' count=count+1
'loop
'=== efface elements
'sql = "DELETE FROM cds_cat WHERE catid = 'test'"
'set bine = con_02.Execute(sql)
'readfile.close
set readfile=nothing
set fs=nothing
'rs.close
set rs=nothing
con_01.close
set con_01=nothing
con_03.close
set con_03=nothing
con_04.close
set con_04=nothing
objOutputFile.WriteLine date & " " & time & " " & ii & " record(s). end of importations"
'=== ferme le fichier text de log file
objOutputFile.Close
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recimp & " from CSV"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recupd & " UPDATED"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recins & " INSERTED"
if ii=0 then
objOutputFile02.WriteLine date & " " & time & " ----------------------------------------------------------------------"
end if
'=== delete csv after import, it was copied locally just for the import
des = "c:\aef\" & ligne
objFSO.deleteFile(des),TRUE
objFSO.deleteFile("c:\aef\" & tabaef & ".fmt"),TRUE
objFSO.deleteFile("c:\aef\" & tabaef & "_aef.fmt"),TRUE
Loop
con_02.close
set con_02 = nothing
SET TAG = NOTHING
objOutputFile02.Close
'=== database finie === on crée un fichier bidon pour indiquer que toute la database est fini d'importer
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objFso.OpenTextFile(base_dir & a, 8, true)
If Err.number <> 0 Then
err.clear
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 8, true)
end if
on error goto 0
objOutputFile02.WriteLine date & " " & time & " fini TOUTES LES TABLES "
objOutputFile02.WriteLine date & " " & time & " temp total (sec, avec delais): " & timer - timertotal
objOutputFile02.WriteLine date & " " & time & " temp total (sec, SANS delais): " & (timer - timertotal)- delaistotal
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FIN "
objOutputFile02.Close
'if local=1 then
objEnv("SEE_MASK_NOZONECHECKS") = 1
script01 = "wscript.exe \\corp.stas.local\stas\NetLogon\users\notification_balloon.vbs"
b=""
c=int(timer - timertotal)
if c>60 then
b=int(c/60) & " min"
else
b=c & " sec"
end if
a=" "" FIN "" ""MAJ " & db_main & "`nTemps: " & b & """"
objshe.Run script01 & a & " 30 1+16", , False
objEnv.Remove ("SEE_MASK_NOZONECHECKS")
'end if
Set objfso = Nothing
'==========================================================================================
'=== SUB fonction pour extraire une variable d'environnement du dos
Function Environ(VarName)
Dim wss, env
Set wss = CreateObject("WScript.Shell")
Set env = wss.environment("process")
Environ = env(VarName)
If Environ = "" Then
Set env = wss.environment("system")
Environ = env(VarName)
End If
End Function
'===========================================================================================
'=== fonction verify if table exist, if not create it with a first column
function VER_CRE_TABle(db, table, col_clef_form, col_clef_name, primaire)
sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"
tag=retsql(sql,"CREE TABLE")
'========== la table n'existe pas, on la crée avec une colonne de base
if tag("tableCount") = 0 Then
'msgbox(table_clef_name)
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
sql = "CREATE TABLE " & db & ".dbo." & table & "(" & col_clef_name & " " & col_clef_form & ")"
'msgbox(sql)
objOutputFile.WriteLine date & " " & time & " DEBUT table CREATE table " & db & vbcrlf & sql & vbcrlf & col_clef_form & " " & col_clef_name
set tag = con_02.execute(sql)
objOutputFile.WriteLine date & " " & time & " FINI table CREATE table " & db
'msgbox("SQL TABLE ajoutée")
end if
if lcase(db)="mag_inventaire" and lcase(table)="etatv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="itemv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="revv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
end function
function VER_CRE_col(db, table, col_clef_form, col_clef_name, primaire)
on error goto 0
'=== compte le nombre de colonne avec le nom qu'on cherche
'=== count the number of columns that have a certain name in the database
sql = "SELECT COUNT(*) As tableCount FROm " & db & ".dbo.syscolumns WHERE ID = (SELECT ID from " & db & ".dbo.sysobjects WHERE name='" & table & "') AND name = '" & col_clef_name & "';"
tag=retsql(sql,"CREE COLONNE")
'msgbox(tag("tableCount") & " " & sql)
if tag("tableCount") = 0 then
'=== on ajoute une colonne de données
'=== add a column in database
on error resume next
'=== colonne
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " table manquante " & col_clef_name
err.clear
wscript.quit
End If
'=== ajoute colonne
sql = "ALTER TABLE " & db & ".dbo." & table & " ADD [" & col_clef_name & "] " & col_clef_form
'=== commentaire quand on ajoute une colonne
objOutputFile.WriteLine date & " " & time & " " & "ajout d'une colonne " & sql
on error resume next
if test=0 then
tag=retsql(sql,"CREE COLONNE")
end if
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " erreur ajout de colonne " & col_clef_name
err.clear
wscript.quit
End If
on error goto 0
else
objOutputFile.WriteLine date & " " & time & " " & ii
end if
end function
function retsql(sql,bb)
iq=1
do
'on error resume next
set retsql = con_02.execute(sql)
if err.number<>0 then
if instr(err.description,"Ce serveur SQL n'existe pas ou son acc")<>0 or _
instr(err.description,"lai d'attente expir")<>0 or _
instr(err.description,"Erreur réseau générale. Consultez la documentation relative à votre réseau.") then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " did not respond in "& errdel & " sec " & err.description
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa
elseif instr(err.description,"Violation de la contrainte PRIMARY KEY")<>0 or instr(err.description,"Violation of PRIMARY KEY constraint")<>0 then
'=== already existed so its normal error for an insert
if iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working"
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
iq=251
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " this line already existed "
wscript.quit
elseif instr(err.description,"Impossible trouver la ligne dans sysobjects pour") then
objOutputFile02.WriteLine date & " " & time & " ERROR: " & err.description
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR Impossible trouver la ligne dans sysobjects pour"
'objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
'wscript.quit
elseif instr(err.description,"chec de la liaison de communication") then
'msgbox("echec liaison comm")
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR FATALE echec liaison comm"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
else
aa= date & " " & time & " " & db_main & " " & nom_table & " " & bb & " FATAL ERROR " & timer-a & " " & vbcrlf & vbcrlf & err.description & vbcrlf & vbcrlf
objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
end if
elseif iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working "
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
iq=iq+1
loop until iq>250 or err.number=0
end functionASKER
good, i would have liked the code, but i did not say wich language so you get an A hehe
Looks good... have tagged the page for future reference :)
Wow, have just finished reading it. Might need to check out that other link :)
ASKER
i am proud of it
will be glad if someone just understand it enough to use it one day ;)
delete temp db if it exist
i create the temp database, the temp columns according to schemas
also create the destination database if it does not exist
generate a FMT (format file) with BCP.EXE
analyse the separators in the CSV
modify the FMT file, to match the separators in the CSV file
import the CSV as bulk in temp db
then update from temp db to the permanent db
delete row updated in temporary db
insert the rest
futur problem:
there will be a " (ddouble quote) left at the start of the first column if the format of this column is a string, and use " as separator
i need to add a update on the first column to remove the "
mainly because FMT (format file) manage separator as column separator, not string starting and ending separator
will repost later when i add the sql update to remove the double quote in first column (just in case this column is not an integer)
will be glad if someone just understand it enough to use it one day ;)
delete temp db if it exist
i create the temp database, the temp columns according to schemas
also create the destination database if it does not exist
generate a FMT (format file) with BCP.EXE
analyse the separators in the CSV
modify the FMT file, to match the separators in the CSV file
import the CSV as bulk in temp db
then update from temp db to the permanent db
delete row updated in temporary db
insert the rest
futur problem:
there will be a " (ddouble quote) left at the start of the first column if the format of this column is a string, and use " as separator
i need to add a update on the first column to remove the "
mainly because FMT (format file) manage separator as column separator, not string starting and ending separator
will repost later when i add the sql update to remove the double quote in first column (just in case this column is not an integer)
Or, in your format file, maybe describe an extra single field just for the leading " - have done that type of thing before...
Will be keen to see it, can understand why you are deservedly proud.
Will be keen to see it, can understand why you are deservedly proud.
ASKER
very nice
i did not think of that
it's perfect since i already manipulate my FMT file, adding a new line with a dummy will be pice of cake
thx
i did not think of that
it's perfect since i already manipulate my FMT file, adding a new line with a dummy will be pice of cake
thx
ASKER
bulk insert csv2sql, for sql 2005
tested and working on sql 2005 server 64 bits
be sure to have all requirements (files in same directory as script)
chek this post for requirements:
https://www.experts-exchange.com/questions/23931018/Converting-Excel-Document-Values-to-be-ready-for-MySQL-Import.html?sfQueryTermInfo=1+10+csv+wildboy85
script can run from any sub directory, once for each database
run the script from a sql server
this script copy csv from a network path in c:\aef on a sql 2005 server
it analyse the first row of the CSV to determine wich column use " as separators
it use BCP.EXE to generate a FMT file (format for import as bulk insert)
it modify the FMT file to put the separator found in the CSV for each columns
it modify the FMT file to add a dummy column at the start if the first column separator is " (double quotes)
if chek two table names that i know have a bad last line, and insert a dummy last line in CSV
if create a SQL table with "_AEF" at the end, with a _schema.csv file (custom)
it use bulk insert to insert the csv in the _AEF sql table
bulk insert will tolerate 10 import error before stopping
if update from sql table_AEF table to table (that was tthe main goal)
it delete all updated items in _AEF table
it insert all the row left in _AEF table in sql table
it write logs with counts of all the updated rows and inserted rows
dont be shy if you want to use it, and ask how it work
change log:
i change the FMT to add a fake column when i have a " at the start of my first column (to remove it)
i change the FMT to add a fake line at the end when the last line is bad (2 tables name only)
(bulk insert support errors, but not a bad last line in a csv(bad end of file error, nothing import))
added a tolerance for 10 error in bulk import (bad lines, like cut in half), logging them in log file
when more than 10 error, the script will stop
this is a VBS/WSH file
the file autorestart itself in 32 bit mode if the server is 64 bits, to have access to 32 bits odbc drivers
this file must be run locally on sql server
tested and working on sql 2005 server 64 bits
be sure to have all requirements (files in same directory as script)
chek this post for requirements:
https://www.experts-exchange.com/questions/23931018/Converting-Excel-Document-Values-to-be-ready-for-MySQL-Import.html?sfQueryTermInfo=1+10+csv+wildboy85
script can run from any sub directory, once for each database
run the script from a sql server
this script copy csv from a network path in c:\aef on a sql 2005 server
it analyse the first row of the CSV to determine wich column use " as separators
it use BCP.EXE to generate a FMT file (format for import as bulk insert)
it modify the FMT file to put the separator found in the CSV for each columns
it modify the FMT file to add a dummy column at the start if the first column separator is " (double quotes)
if chek two table names that i know have a bad last line, and insert a dummy last line in CSV
if create a SQL table with "_AEF" at the end, with a _schema.csv file (custom)
it use bulk insert to insert the csv in the _AEF sql table
bulk insert will tolerate 10 import error before stopping
if update from sql table_AEF table to table (that was tthe main goal)
it delete all updated items in _AEF table
it insert all the row left in _AEF table in sql table
it write logs with counts of all the updated rows and inserted rows
dont be shy if you want to use it, and ask how it work
change log:
i change the FMT to add a fake column when i have a " at the start of my first column (to remove it)
i change the FMT to add a fake line at the end when the last line is bad (2 tables name only)
(bulk insert support errors, but not a bad last line in a csv(bad end of file error, nothing import))
added a tolerance for 10 error in bulk import (bad lines, like cut in half), logging them in log file
when more than 10 error, the script will stop
this is a VBS/WSH file
the file autorestart itself in 32 bit mode if the server is 64 bits, to have access to 32 bits odbc drivers
this file must be run locally on sql server
'=== main program procedure:.
'=== main program procedure:.
' 1. read the sql formats: csv_to_sql_import_formats.csv
' 2. read the list of files to import: csv_to_sql_liste_fichier_a_importer.txt
' 3. open sql database with admin password and dns adress
' 4. read a file with the name of the CSV + "_schema" and use it to know the format of columns to create sql database
' you must create this file manually before importation
' in this file, there is a column format number wich is +1000, its destined to be the main key for database
' (i did not add the sql code to define the main key)
' 5. if database does not exist, we create it
' 6. if column for main key does not exist, we create it
' 7. search in main key for a key identical, if not found, create the record, if found, update record
test=0 '=== 0 write in database id test=1
dim upd_ii, sql1, dummy, iq
iq=1
'=== take name of the sql server from a window variable
sql1=environ("stassql1")
upd_ii=10 '=== update frequency for writing in report log database_log.txt
upd_ii=10 '=== update frequency for writing in report log database_log.txt
ecmax=10 '=== nbr of max try to connect to database
Dim objFSO, file_02, ligne,a
Set objshe = CreateObject("WScript.Shell")
Set objEnv = objshe.Environment("PROCESS")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'=== actual drive, actual directory, and "\"
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir = left(thepath,p)
filnam = right(thepath,len(thepath)-p)
'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
a64 = windir & "\syswow64\wscript.exe"
if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
'=== 64 bits system
a = """" & a64 & """ """ & basedir & filnam & """"
objshe.Run a,0, false
wscript.quit
end if
'=== reactivate security chek for zone
if mid(basedir,1,2)="c:" then local=1 else local=0
base_dir = basedir
b = mid(basedir,1,len(basedir)-1)
p = instrRev(b,"\")
basename = right(b,len(b)-p)
'=== restart in 32 bits if in 64 mode
'=== windows dir
'msgbox(p & " --- " & basename & " --- " & basedir)
'=== _liste_fichier_a_importer.txt (this file contain the list of file to import)
' contient:
' 01 name of database (is also the name of directory where to get the csv)
' 02 root directory
' 03 file name (is also "_" + the name of the database, cause what i import is for magica)
sqlprefix = "sp_configure 'show advanced options',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO " & vbcrlf
sqlprefix = sqlprefix & "sp_configure 'Ad Hoc Distributed Queries',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO "
sqlprefix = sqlprefix & "SET ANSI_NULLS OFF"
sqlprefix = sqlprefix & "GO"
catfile=basedir & "csv_to_sql_liste_fichier_a_importer.txt"
if objfso.fileexists(catfile) then
Set file_02 = objFSO.OpenTextFile(catfile, 1, 0)
else
msgbox("ERREUR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "n'existe pas, on ne peux pas commencer")
' msgbox("ERROR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "list of file to import does not exist, cannot start import")
wscript.quit
end if
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objfso.OpenTextFile(base_dir & a, 2, true)
If Err.number <> 0 Then
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
on error goto 0
set fso=nothing
'=== LOOP for folders ================================================
objOutputFile02.WriteLine date & " " & time & " DEBUT"
timertotal=timer
delaistotal=0
'=== read database name
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\")=0 and instr(ligne,".")=0 Then
db_main=ligne
End If
end if
'=== read directory where the csv is
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\") Then
ligne =lcase(ligne)
if ligne = "m:\temp" then
ligne = "\\corp.stas.local\stas\Magica\Temp"
end if
if local=0 then
db_dir = ligne & "\" & db_main
else
'=== if we are local there is no subdir
db_dir = ligne
end if
End If
eND if
'=== add "mag_" at the start of database name, cause the importation is for a software called "magica"
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
Dim con_02
Set con_02 = CreateObject("ADODB.Connection")
set tag = CreateObject ("ADODB.Recordset")
con_02.ConnectionString = "Driver={SQL Server};Server=sql.corp.stas.local"
errdel=20
con_02.connectiontimeout=errdel
on error resume next
con_02.Open
i=0
do while err<>0 and i<10
con_02.Open
err.clear
i=i+1
if i>10 then
'objOutputFile.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.end
end if
loop
on error goto 0
'=== expiration de requete sql en secondes
'=== 1 2 3 4 5 (min)
'=== 60,120,180,240,300 (sec)
con_02.commandtimeout=300
'=== DATABASE === cherche la database, pour voir si elle existe
'=== verify is database exist, in the table containing all database name for all sql server
'=== the name of this table might differ for a my_sql server
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db & "'"
set tag = con_02.execute(sql)
'=== db create
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db & "]"
set tag = con_02.execute(sql)
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
else
'msgbox("la database existe déjà, pas supposé")
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db
end if
'=== create a temporary database for bulk import
nom_db2 = nom_db & "_aef"
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db2 & "]"
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db2
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
on error resume next
set tag = con_02.execute(sql)
if err<>0 then
a = err.description
end if
on error goto 0
if instr(a,"model") then
'=== chek if model database is locked by a bugged application
'=== a count of more than 0 results = something locked the database
objOutputFile02.WriteLine date & " " & time & " model is locked by another application - killing all SPID that lock MODEL"
s = "select spid, hostname, program_name from master..sysprocesses where db_name(dbid) = 'model'"
on error resume next
set tag = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " ERROR query" & vbcrlf & s & vbcrlf
end if
on error goto 0
while not(tag.eof)
objOutputFile02.WriteLine date & " " & time & " killing: " & tag.fields(0) & " " & tag.fields(1) & " " & tag.fields(2)
'=== kill the process that lock MODEL
s = "kill " & tag.fields(0)
on error resume next
set tag02 = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " ERROR query" & vbcrlf & s & vbcrlf
end if
on error goto 0
tag.movenext
wend
set tag = con_02.execute(sql)
else
objOutputFile02.WriteLine date & " " & time & "ERROR with the first request on the database server" & vbcrlf & a & vbcrlf & "QUITTING PROGRAM"
wscript.quit
end if
else
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db2
sql = "drop DATABASE [" & nom_db2 & "]"
set tag = con_02.execute(sql)
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been dropped " & nom_db2
else
objOutputFile02.WriteLine date & " " & time & " ERROR cannot drop database " & nom_db2
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
end if
sql = "CREATE DATABASE [" & nom_db2 & "]"
set tag = con_02.execute(sql)
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") <> 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been created " & nom_db2
end if
end if
'=== be sure to "use" this database just in case i forgot to specifie database name in a command
sql = "USE " & nom_db
Do While file_02.AtEndOfStream <> True
'=== read first table
ligne = file_02.Readline
do while mid(ligne,1,1)="'"
ligne = file_02.Readline
loop
on error resume next
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
nom_table_suf = "_schema" '=== ajoute _schema pour le fichier de quel type sont chaque table
'=== change fichier (table)
'=== change file (the name of file is also the name of the table)
'=== name of table, remove the ".csv" part
nom_table = mid(ligne,1, len(ligne) -(len(ligne) -(instr(ligne,".")-1)))
'=== copy the csv locally, or else we cannot import, because the service sql must access the file, not the actual user
sou = db_dir & "\" & ligne
des = "c:\aef\" & nom_db & "_" & left(ligne,len(ligne)-4) & "_aef.csv"
objFSO.copyFile sou, des, TRUE
if not objfso.fileexists(des) then
objOutputFile02.WriteLine date & " " & time & " fichier pas copie: " & des
objOutputFile02.WriteLine date & " " & time & " script arrete "
fatal01=1
end if
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " debut importation " & db_dir
dim sql
'=== open a log file, create it if not there
'=== 8 = append === 2 = newfile
'on error resume next
on error goto 0
objOutputFile02.WriteLine date & " " & time & " ouverture log table " & base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt"
Set objOutputFile = objfso.OpenTextFile(base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt", 2, true)
If Err.number <> 0 Then
Set objOutputFile = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
objOutputFile.WriteLine date & " " & time & " " & "debut"
on error goto 0
'=== CSV connexion - input data
'=== FUTUR: verifier si fichier existe avant de l'ouvrir
Set Con_01 = CreateObject("ADODB.Connection")
on error resume next
Con_01.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & db_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
if err<>0 then
on error goto 0
msgbox("error fatal cannot open directory" & vbcrlf & db_dir)
wscript.quit
end if
'=== erreur si communication marche pas
on error resume next
Set RS_01 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir la connection vers tous les csv " & db_dir
MSGBOX("ERREUR FATALE" & vbcrlf & "pas capable d'ouvrir un csv en lecture" & vbcrlf & db_Dir)
end if
on error goto 0
'=== table de format sql à importer pour stas numérotés de 0 à 22 et +
'=== table containing the sql format as number for faster creation of "_schema" files
Set Con_03 = CreateObject("ADODB.Connection")
Con_03.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
Set RS_03 = CreateObject ("ADODB.Recordset")
sql = "SELECT * from csv_to_sql_import_formats.csv"
'=== reset fatal error counter
fatal01=0
set rs_03 = con_03.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
fatal01=1
end if
'=== _schema . csv
'=== import schema, contain number for format
Set Con_04 = CreateObject("ADODB.Connection")
'====== test
Con_04.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'HDR=YES;FMT=Delimited
'Con_04.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & base_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
on error resume next
Set RS_04 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== _schema . CSV === ouvre la table des types de colonnes à créer
sql = "SELECT * from " & nom_table & nom_table_suf & ".csv"
on error resume next
set rs_04 = con_04.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== SQL
'=== crée une database par rapport
'=== crée une table par fichier csv (voir csv_to_sql_liste_fichier_a_importer.txt, dans le dossier d'origine)
'=== will create one database for every directory to import
'=== will create one table for every file to import
'=== rs = record set = contient un record, une ligne de database
Set RS = CreateObject ("ADODB.Recordset")
Set SQLStmt = CreateObject("ADODB.Command")
'================================================================ sql ======================================
'=== connexion serveur sql, sans se connecter sur une database
'=== MAIN SQL DATABASE CONNECTION
'=== if there was anny fatal error with the csv files, this part is not executed
if fatal01=0 then
'=== read CSV file
fatal02 = 0
sql = "SELECT * from " & nom_table & ".csv"
on error resume next
'=== colonne
set rs_01 = con_01.execute(sql)
'=== data csv
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & rs_01(table_clef).name
objOutputFile02.WriteLine date & " " & time & " " & db_main & " csv vide de contenu SKIPPING"
'objOutputFile02.WriteLine date & " " & time & " "
'wscript.quit
fatal02 = 1
End If
on error goto 0
if fatal02 = 0 then
'=== primary key column (format >999 + format type)
table_clef=-1
i=0
while i<RS_04.fields.count
'msgbox(rs_04(i) & vbcrlf & i)
'msgbox(RS_04(i))
if rs_04(i)>999 then
table_clef=i
'msgbox(table_clef)
end if
i=i+1
wend
'=== did not find any primary key - stopping import
if table_clef=-1 then
'=== stop program is there is no master key
a="vous avez oublié de spécifier une clef de base dans votre base de donnée soit un format +1000"
'msgbox(a)
objOutputFile.WriteLine date & " " & time & a & nom_db
objOutputFile02.WriteLine date & " " & time & " pas de table clef -- le script est arrêté!!!"
Wscript.quit
end if
'=== verify if table exist, if not create it with FIRST OCOLUMN, not primary key anymore (bulk insert requirement)
if rs_04(0)>999 then
format01 = rs_04(0)-1000
else
format01 = rs_04(0)
end if
dummy = VER_CRE_table(nom_db, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== CREATE TABLES
'=== verify if table exist, if not create it with primary key
objOutputFile02.WriteLine date & " " & time & " CREATION table " & nom_table
'=== create AEF (to delete) table for importations
dummy = VER_CRE_table(nom_db2, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== > verify if number of columns in CSV are same that in SCHEMA
exactcolumn =0
if exactcolumn =1 then
if RS_04.fields.count>RS_01.fields.count then
A=" le nombre de colonnes dans le csv d'origine est < que celui du schema"
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & " " & a
a=" " & RS_04.fields.count-RS_01.fields.count & " colonne(s) en trop sera(ont) crée(s) "
objOutputFile.WriteLine date & " " & time & " " & a & nom_db
a=""
b=""
i=0
y=0
while i<RS_04.fields.count
a = a + " " + rs_04(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + string(len(rs_01(y).name),"_")
else
b = b + " " + rs_01(y).name
y=y+1
end if
err.clear
If Err <> 0 Then
b = b + space(len(rs_04(i)))
err.clear
End If
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & b
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 1 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== < verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count<RS_01.fields.count then
'=== rs_01 = csv === rs_04 = schema
A = " le nombre de colonnes dans le csv d'origine est > que celui du schema"
a = a & vbcrlf & RS_04.fields.count &" "& RS_01.fields.count& " "
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & a & nom_db
a = " impossible de continuer car il vous manquera des données "
objOutputFile.WriteLine date & " " & time & a & nom_db
a=""
b=""
I=0
y=0
while i<RS_01.fields.count
a = a + " " + cstr(i) + " " + rs_01(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + " " & string(len(rs_01(y).name),"_")
else
b = b + " " + cstr(y) + " " + rs_04(y).name
y=y+1
end if
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 2 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
'end if
'=== = verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count=RS_01.fields.count then
a=""
b=""
I=0
y=0
tag=0
while i<RS_01.fields.count
a = a + " " + rs_01(i).name
b = b + " " + rs_04(y).name
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
tag=1
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
end if
on error goto 0
i=i+1
y=y+1
wend
if tag=1 then
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 3 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== column creation
i=0
while i<RS_04.fields.count
'=== clef primaire = 0 ou 1
a=rs_04(i)
on error resume next
if a>999 then
a=a-1000
end if
'=== make a new column (at this point, the table and key column is already done)
dummy = VER_CRE_col(nom_db, nom_table, rs_03(a), rs_04(i).name, 0)
'=== make a new column (at this point, the table and key column is already done)
'***************************************************************************************************
dummy = VER_CRE_col(nom_db2, nom_table, rs_03(a), rs_04(i).name, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " database..: -> " & nom_db & " <- nomfisrtcolumn..: ->" & rs_04(i).name & "<- numero clef unique..: "
objOutputFile.WriteLine date & " " & time & " -i- " & i & " -a- " & a & " -count- " & RS_04.fields.count
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 4 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
i=i+1
on error goto 0
wend
'=== define primary key
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
'not null
'ALTER TABLE supplier
'MODIFY supplier_name varchar2(100) not null;
's = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
'objOutputFile.WriteLine date & " " & time & " " & s
'b = retsql(s,"table clef")
'=== table [employés] is a mix of ldap and csv, so we dont have any primary key
if nom_db <> "mag_employés" and nom_table <>"personnes" then
s = "use " & nom_db & vbcrlf
'objOutputFile.WriteLine date & " " & time & " " & s
'b = retsql(s,"count table clef")
s = s & "select count(c.COLUMN_NAME) as col01 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, "
s = s & "INFORMATION_SCHEMA.KEY_COLUMN_USAGE c " & vbcrlf
s = s & "where pk.TABLE_NAME = '" & nom_table & "' "
s = s & "and CONSTRAINT_TYPE = 'PRIMARY KEY' "
s = s & "and c.TABLE_NAME = pk.TABLE_NAME "
s = s & "and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME "
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"count table clef")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " number of primary key: " & recimp
if recimp=0 then
'=== no primary key, we create one
s = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
end if
if nom_db2 = "mag_employés_aef" and nom_table ="personnes" then
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [codeint] int not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([codeint])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
else
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
ii=0
'=== new bulk import here
'=== copy file locally on sql server (stas-host-01), it work only locally cause you need a network logon to work networked
fil01 = db_dir & "\" & nom_table & ".csv"
fil01 = nom_table & ".csv"
TABaef = nom_table
nom_db2 = nom_db & "_aef"
'=== basic filename for all .FMT et .CSV
fil02 = nom_db & "_" & tabaef
fil01 = fil02 & ".csv"
'=== empty the AEF table to be sure
sql = "SELECT COUNT(*) As 'DBCount' FROM [" & nom_db2 & "].dbo.[" & tabaef & "]"
set tag = con_02.execute(sql)
if tag("DBCount") > 0 Then
objOutputFile02.WriteLine date & " " & time & " database is not empty, drop failed " & nom_db2
objOutputFile02.WriteLine date & " " & time & " countermesures: delete all item in it " & nom_db2
sql = "delete from [" & nom_db2 & "].dbo.[" & tabaef & "]"
objOutputFile02.WriteLine date & " " & time & sql
set tag = con_02.execute(sql)
end if
's = sqlprefix & "" & vbcrlf
s = ""
's = s & "INSERT INTO " & nom_db2 & ".dbo.[" & tabaef & "] " & vbcrlf
'=============== ----------- ============== -------------- =============== -------------
'=== new technique with objects
'//This is the connection string to connect to your csv file
'string strConString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\; Extended Properties=\ Text;HDR=YES;\ ";
'// open connection
'OleDbConnection oCon = new OleDbConnection(strConString);
'oCon.Open();
'// fill data set
'//csvfile should be present in c:
'string strSql = SELECT * FROM csvfile.csv ;
'OleDbDataAdapter oDA = new OleDbDataAdapter(strSql, oCon);
'DataSet oData = new DataSet();
'oDA.Fill(oData, ABC );
'GridView1.DataSource = oData;
'GridView1.DataBind();
'oCon.Close();
'LOAD DATA INFILE 'c:/abc.csv' INTO TABLE stu
'FIELDS TERMINATED BY '\t'
'LINES TERMINATED BY '\n';
' Set Con_05 = CreateObject("ADODB.Connection")
' Con_05.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\aef\; Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'BULK INSERT mag_employés_Aef.dbo.personnes FROM 'c:\aef\personnes.csv' WITH (FIRSTROW=2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')
'go
's = s & "SET QUOTED_IDENTIFIER OFF" & vbcrlf
's = s & "go" & vbcrlf
'DATAFILETYPE = 'char',
's = s & "SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & VBCRLF
'S = S & "'Text;Database=C:\AEF\;HDR=YES;FMT=Delimited;IMEX=1','SELECT " & s2 & " FROM " & fil01 & "')" & VBCRLF
'if nom_db2 = "mag_employés_aef" and tabaef="personnes" then
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL) AND (STATDESA = '' OR STATDESA IS NULL) AND (nom IS NOT NULL)"
'else
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL)"
'end if
'=== FMT format file
'9.0
'12
'1 SQLCHAR 0 12 ";\"" 1 CODEINT ""
'2 SQLCHAR 0 32 "\";\"" 2 DIM1TXT French_CI_AS
'3 SQLCHAR 0 32 "\";" 3 DIM2TXT French_CI_AS
'4 SQLCHAR 0 30 ";" 4 SETUP ""
'5 SQLCHAR 0 30 ";\"" 5 UNIT ""
'6 SQLCHAR 0 32 "\";\"" 6 SETUPTXT French_CI_AS
'7 SQLCHAR 0 32 "\";\"" 7 UNITTXT French_CI_AS
'8 SQLCHAR 0 32 "\";" 8 VENTFIN French_CI_AS
'9 SQLCHAR 0 30 ";\"" 9 UNITLOC ""
'10 SQLCHAR 0 0 "\";\"" 10 COMMENTA French_CI_AS
'11 SQLCHAR 0 128 "\";\"" 11 ALPHA French_CI_AS
'12 SQLCHAR 0 0 "\"\r\n" 12 DIVERS1 French_CI_AS
a = fil02 & ".fmt"
'=== bcp generate first FMT automatically
a = "bcp [" & nom_db2 & "].dbo.[" & tabaef & "] format nul -c -t; -f c:\aef\" & fil02 & "_aef.Fmt -T -S stas-sql-01"
objOutputFile.WriteLine date & " " & time & vbcrlf & vbcrlf & a & vbcrlf
objshe.run a,0, true
'=== read original FMT (_aef)
on error resume next
Set objfil03 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.Fmt", 1, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open original FMT file" & vbcrlf & fil02 & "_aef.fmt" & vbcrlf
end if
on error goto 0
'=== read CSV separate columns = lines for FMT file
on error resume next
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.csv", 1, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open original CSV file" & vbcrlf & fil02 & "_aef.CSV" & vbcrlf
end if
on error goto 0
'=== skip column name
a = objfil04.readline
'=== line to find separators, double quote for char, nothing for numbers
'=== must read char by char, until we met char(13) and char(10)
'c = objfil04.readline
'=== tagret = if we meet chr(13) and chr(10), it will equal to 2
tagret = 0
'=== chr(10) is not a end of line, just a change of line inside a string
'=== chr(13) and chr(10) is a END OF LINE
c = ""
while tagret < 2
if not objFil04.AtEndOfStream then
a = objFil04.Read(1)
if tagret=1 then
if a=chr(10) then
tagret = tagret + 1
end if
else
tagret = 0
end if
if a<>chr(13) then
c = c & a
else
tagret = tagret + 1
end if
end if
wend
objfil04.close
'=== write new FMT file with good separators
on error resume next
Set objfil05 = objfso.OpenTextFile("c:\aef\" & fil02 & "_aef2.fmt", 2, true)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open destination FMT file" & vbcrlf & fil02 & "_aef2.FMT" & vbcrlf
end if
on error goto 0
'=== sql version
a = objFil03.readLine
objFil05.WriteLine a
'=== number of columns
a = objFil03.readLine
objFil05.WriteLine a
i=0
b = RS_04.fields.count
'=== loop all line from original FMT file
'=== at the same time we analyse the variable C (second line from data file) to find all separators
inside = 0 '=== we are inside "" ignore the ;
sep = "" '=== separator between this field and the next
end01 = ""
end02 = ";"
end03 = ""
'=== dummy string with all the good separators to replace first line (column titles) in csv
'=== if we do not replace this line, first REAL data line is skipped
dum01 = ""
objOutputFile.WriteLine date & " " & time & " line to analyse: " & c & vbcrlf
'=== this loop scan the FMT and at the same time all the columns of the CSV (field count of the csv)
'=== obviously, it should be the same number of line(fmt)/columns(csv), because the FMT is generated
'=== with the sql database(temp-aef) that was created by the CSV schema
while i<b
a = objFil03.readLine
'=== loop in C chain to find any ";" outside "" and all the ""
if mid(c,1,1)="""" then
inside = 1
end01 = "\"""
c = right(c,len(c)-instr(c,""";")-1)
'objOutputFile.WriteLine date & " " & time & " line to analys2: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
else
c = right(c,len(c)-instr(c,";"))
'objOutputFile.WriteLine date & " " & time & " line to analys3: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
end if
if instr(a,""";""") then
a = replace(a,""";""","""" & end01 & end02 & end03 & """")
dum01 = dum01 & end01 & end02 & end03
dum01= replace(dum01,"\","")
elseif instr(a,"\r\n") then
a = replace(a,"""\r\n""","""" & end01 & "\r\n" & """")
end04 = replace(end01,"\","")
end05 = replace(end02,"\","")
end06 = replace(end03,"\","")
dum01 = dum01 & end04
end if
end01 = ""
end03 = ""
objFil05.WriteLine a
i = i + 1
wend
objfil03.close
objfil05.close
'=== manipulate the FMT again if the first char is a "
'=== create a false column with " as terminator in FMT
'=== change the numbers for all the columnns in the FMT since we have one more at the start
sou = "c:\aef\" & fil02 & "_Aef2.fmt"
des = "c:\aef\" & fil02 & ".fmt"
if mid(dum01,1,1) = """" then
objOutputFile.WriteLine date & " " & time & " FMT making a new FMT because the first column in the CSV have a "" separator" & vbcrlf
'=== remove the " from the first fmt line (the dummy line replacing the column names)
dum01 = right(dum01, len(dum01)-1)
Set objfil04 = objFSO.OpenTextFile(sou, 1, 0)
Set objfil05 = objfso.OpenTextFile(des, 2, true)
'=== sql version
a = objFil04.readLine
objFil05.WriteLine a
'=== number of columns
a = objFil04.readLine
'=== add 1 to the number of line
'=== the first column separator will be ", basically, it will end at the ", and have no content at all
'=== but it will remove the " from my data in first column bulk import
b = cint(a)
'=== add one line to FMT (1 column to CSV)
b = b + 1
a = trim(cstr(b))
objFil05.WriteLine a
colnum = 1
'=== new line as first column finish by "
objFil05.WriteLine "1 SQLCHAR 0 255 ""\"""" 0 DummyField SQL_Latin1_General_CP1_CI_AS"
colnum = colnum + 1
Do Until objFil04.AtEndOfStream
a = objFil04.readLine
'=== now i must find all the columns number and upgrade them +1
ara01 = split(a," ")
b=""
i=1
'8.0
'4
'1 SQLCHAR 0 255 "\"" 0 DummyField SQL_Latin1_General_CP1_CI_AS
'2 SQLCHAR 0 255 "\",\"" 1 Fieldname1 SQL_Latin1_General_CP1_CI_AS
'3 SQLCHAR 0 255 "\",\"" 2 FieldName2 SQL_Latin1_General_CP1_CI_AS
'4 SQLCHAR 0 255 "\"\r\n" 4 FieldName3 SQL_Latin1_General_CP1_CI_AS
for each c in ara01
if len(trim(c))>0 then
if i=1 then
c = colnum
objOutputFile.WriteLine date & " " & time & " FMT before: " & c & " After: " & colnum & vbcrlf
end if
if i=6 then
c = colnum - 1
objOutputFile.WriteLine date & " " & time & " FMT before: " & c & " After: " & colnum & vbcrlf
end if
b=b & c & " "
i=i+1
end if
next
colnum = colnum + 1
objFil05.WriteLine b
loop
objfil05.close
objfil04.close
else
'=== no change in first column of fmt, simple copy the second copy (_Aef2.fmt) to be the original for the bulk import (.fmt)
objOutputFile.WriteLine date & " " & time & " no change in FMT" & vbcrlf
objFSO.copyFile sou, des, TRUE
end if
objOutputFile.WriteLine date & " " & time & " inserting a dummy line in place of the columns title line in the CSV" & vbcrlf
objOutputFile.WriteLine date & " " & time & " " & dum01 & vbcrlf
'sou = db_dir & "\" & ligne
'des = "c:\aef\" & left(ligne,len(ligne)-4) & "_aef.csv"
'objFSO.copyFile sou, des, TRUE
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.csv", 1, 0)
Set objfil05 = objfso.OpenTextFile("c:\aef\" & fil02 & ".csv", 2, true)
a = objFil04.readLine
objFil05.Write dum01 & chr(13) & chr(10)
objOutputFile.WriteLine date & " " & time & " START binary read" & vbcrlf
Do Until objFil04.AtEndOfStream
a = objFil04.Read(1)
objfil05.write a
Loop
objOutputFile.WriteLine date & " " & time & " END binary read" & vbcrlf
'=== last line in one of my CSV is cut in half
'=== bluk insert can tolerate many error, but in this case nothing will be imported
'=== so i add a fake line at the end of the file
if lcase(nom_db2) = "mag_fournisseurs_aef" and lcase(tabaef) = "personnes" then
objFil05.Write dum01 & chr(13) & chr(10)
'"WARNING data conversion failed in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " this table have a fake line inserted" & vbcrlf
objOutputFile.WriteLine date & " " & time & " there will be at least one data conversion failed" & vbcrlf
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " this table have a fake line inserted (to prevent bad end of file)"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " there will be at least one data conversion failed"
end if
objfil05.close
objfil04.close
s = s & "BULK INSERT [" & nom_db2 & "].dbo.[" & tabaef & "] FROM 'c:\aef\" & fil01 & "' WITH (FORMATFILE = 'C:\aef\" & fil02 & ".Fmt', CODEPAGE ='RAW', DATAFILETYPE = 'char', FIRSTROW=2, MAXERRORS=10)"
objOutputFile.WriteLine date & " " & time & " " & " bulk insertion of all the CSV file, with a dynamic MFT" &vbcrlf & vbcrlf & s & vbcrlf
'on error resume next
b = retsql(s,"bulk")
'on error goto 0
'=== verify importation (count elements in new table)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count import")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " " & recimp & " records from csv to " & nom_db2 & ".dbo.[" & TABAEF & "]"
'=== count records that will be updated
s = "SELECT count(*) "
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t1," & nom_db & ".dbo.[" & nom_table & "] t2"
s = s & " " & vbcrlf & "WHERE t1." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
b = retsql(s,"count update")
recUPD = b(0)
objOutputFile.WriteLine date & " " & time & " " & recupd & " UPDATES in " & nom_db & ".dbo.[" & nom_table & "]"
'=== query update
i=0
s = "UPDATE " & nom_db & ".dbo.[" & nom_table & "] "
s = s & vbcrlf & "SET "
b = RS_04.fields.count
while i<b
if i<>table_clef then
s = s & "[" & rs_04(i).name & "]"& "=t2.[" & rs_04(i).name & "]"
s = s & ","
end if
i = i + 1
wend
s = left(s,len(s)-1)
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 "
s = s & " " & vbcrlf & "WHERE " & nom_db & ".dbo.[" & nom_table & "]." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " update query" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== delete all updates done in aef table (temp)
s = "DELETE " & nom_db2 & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 " & vbcrlf
s = s & "INNER JOIN " & nom_db & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "ON " & nom_db & ".dbo.[" & TABAEF & "]." & rs_04(table_clef).name & " = t2." & rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " delete query (delete updated elements to insert the rest)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== count records left in temp table (will be inserts quantities)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count insert")
recins = b(0)
objOutputFile.WriteLine date & " " & time & " " & recins & " INSERT in " & nom_db & ".dbo.[" & nom_table & "]"
'=== insert all the rest
i=0
s2=""
b = RS_04.fields.count
while i<b
s2 = s2 & "[" & rs_04(i).name & "]"
i = i + 1
s2 = s2 & ","
wend
s2 = left(s2,len(s2)-1)
s = "INSERT INTO " & nom_db & ".dbo.[" & nom_table & "] " & vbcrlf
s = s & "(" & s2 & ") "
s = s & "SELECT "
s = s & s2
s = s & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] "
objOutputFile.WriteLine date & " " & time & " insert query (insert the remaining elements)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== fin condition fatal error
end if 'fatal02
end if 'fatal01
'=========================== end of main loop ===========================================
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
' count=count+1
'loop
'=== efface elements
'sql = "DELETE FROM cds_cat WHERE catid = 'test'"
'set bine = con_02.Execute(sql)
'readfile.close
set readfile=nothing
set fs=nothing
'rs.close
set rs=nothing
con_01.close
set con_01=nothing
con_03.close
set con_03=nothing
con_04.close
set con_04=nothing
objOutputFile.WriteLine date & " " & time & " " & ii & " record(s). end of importations"
'=== ferme le fichier text de log file
objOutputFile.Close
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recimp & " from CSV"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recupd & " UPDATED"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recins & " INSERTED"
if ii=0 then
objOutputFile02.WriteLine date & " " & time & " ----------------------------------------------------------------------"
end if
'=== delete csv after import, it was copied locally just for the import
des = "c:\aef\" & ligne
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & ".csv"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef.csv"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & ".fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef.fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef2.fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
Loop
con_02.close
set con_02 = nothing
SET TAG = NOTHING
objOutputFile02.Close
'=== database finie === on crée un fichier bidon pour indiquer que toute la database est fini d'importer
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objFso.OpenTextFile(base_dir & a, 8, true)
If Err.number <> 0 Then
err.clear
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 8, true)
end if
on error goto 0
objOutputFile02.WriteLine date & " " & time & " fini TOUTES LES TABLES "
objOutputFile02.WriteLine date & " " & time & " temp total (sec, avec delais): " & timer - timertotal
objOutputFile02.WriteLine date & " " & time & " temp total (sec, SANS delais): " & (timer - timertotal)- delaistotal
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FIN "
objOutputFile02.Close
'if local=1 then
objEnv("SEE_MASK_NOZONECHECKS") = 1
script01 = "wscript.exe \\corp.stas.local\stas\NetLogon\users\notification_balloon.vbs"
b=""
c=int(timer - timertotal)
if c>60 then
b=int(c/60) & " min"
else
b=c & " sec"
end if
a=" "" FIN "" ""MAJ " & db_main & "`nTemps: " & b & """"
objshe.Run script01 & a & " 30 1+16", , False
objEnv.Remove ("SEE_MASK_NOZONECHECKS")
'end if
Set objfso = Nothing
'==========================================================================================
'=== SUB fonction pour extraire une variable d'environnement du dos
Function Environ(VarName)
Dim wss, env
Set wss = CreateObject("WScript.Shell")
Set env = wss.environment("process")
Environ = env(VarName)
If Environ = "" Then
Set env = wss.environment("system")
Environ = env(VarName)
End If
End Function
'===========================================================================================
'=== fonction verify if table exist, if not create it with a first column
function VER_CRE_TABle(db, table, col_clef_form, col_clef_name, primaire)
sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"
tag=retsql(sql,"CREE TABLE")
'========== la table n'existe pas, on la crée avec une colonne de base
if tag("tableCount") = 0 Then
'msgbox(table_clef_name)
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
sql = "CREATE TABLE " & db & ".dbo." & table & "(" & col_clef_name & " " & col_clef_form & ")"
'msgbox(sql)
objOutputFile.WriteLine date & " " & time & " DEBUT table CREATE table " & db & vbcrlf & sql & vbcrlf & col_clef_form & " " & col_clef_name
set tag = con_02.execute(sql)
objOutputFile.WriteLine date & " " & time & " FINI table CREATE table " & db
'msgbox("SQL TABLE ajoutée")
end if
if lcase(db)="mag_inventaire" and lcase(table)="etatv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="itemv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="revv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
end function
function VER_CRE_col(db, table, col_clef_form, col_clef_name, primaire)
on error goto 0
'=== compte le nombre de colonne avec le nom qu'on cherche
'=== count the number of columns that have a certain name in the database
sql = "SELECT COUNT(*) As tableCount FROm " & db & ".dbo.syscolumns WHERE ID = (SELECT ID from " & db & ".dbo.sysobjects WHERE name='" & table & "') AND name = '" & col_clef_name & "';"
tag=retsql(sql,"CREE COLONNE")
'msgbox(tag("tableCount") & " " & sql)
if tag("tableCount") = 0 then
'=== on ajoute une colonne de données
'=== add a column in database
on error resume next
'=== colonne
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " table manquante " & col_clef_name
err.clear
wscript.quit
End If
'=== ajoute colonne
sql = "ALTER TABLE " & db & ".dbo." & table & " ADD [" & col_clef_name & "] " & col_clef_form
'=== commentaire quand on ajoute une colonne
objOutputFile.WriteLine date & " " & time & " " & "ajout d'une colonne " & sql
on error resume next
if test=0 then
tag=retsql(sql,"CREE COLONNE")
end if
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " erreur ajout de colonne " & col_clef_name
err.clear
wscript.quit
End If
on error goto 0
else
objOutputFile.WriteLine date & " " & time & " " & ii
end if
end function
function retsql(sql,bb)
iq=1
err01 = 0
do
on error resume next
set retsql = con_02.execute(sql)
err01 = err.number
err02 = err.description
on error goto 0
if err01<>0 then
if instr(err02,"Ce serveur SQL n'existe pas ou son acc")<>0 or _
instr(err02,"lai d'attente expir")<>0 or _
instr(err02,"Erreur réseau générale. Consultez la documentation relative à votre réseau.") then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " did not respond in "& errdel & " sec " & err02
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa
elseif instr(err02,"Violation de la contrainte PRIMARY KEY")<>0 or instr(err02,"Violation of PRIMARY KEY constraint")<>0 then
'=== already existed so its normal error for an insert
if iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working"
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
iq=251
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " this line already existed "
wscript.quit
elseif instr(err02,"Impossible trouver la ligne dans sysobjects pour") then
objOutputFile02.WriteLine date & " " & time & " ERROR: " & err02
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR Impossible trouver la ligne dans sysobjects pour"
'objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
'wscript.quit
elseif instr(err02,"chec de la liaison de communication") then
'msgbox("echec liaison comm")
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR FATALE echec liaison comm"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
elseif instr(err02,"Erreur de conversion des donn") then
depmax=0
set SQLerrors = con_02.errors
'=== string with all the defect lines
errcon = ""
for each SQLerror in SQLerrors
a = lcase(sqlerror)
if instr(a,"passement du nombre maximal") then
depmax=1
end if
if instr(a,"ligne ") then
errcon = errcon & date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed: " & right(a,len(a)-instr(a,"ligne ")+1) & vbcrlf
end if
next
if depmax=0 then
'objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed in bulk insert (less than 10)"
'=== display only the bad lines numbers, not the error, cause another script manage all my error and i consider less than 10 error as a warning
objOutputFile02.WriteLine errcon
else
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " ERROR data conversion failed in bulk insert (more than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " ERROR data conversion failed in bulk insert (more than 10)"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
end if
'=== no retry for this error, its tolerated
err01=0
elseif instr(err02,"une fin de fichier inattendue") then
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING bad end of file in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING bad end of file in bulk insert (less than 10)"
'=== no retry for this error, its tolerated
err01=0
else
aa= date & " " & time & " " & db_main & " " & nom_table & " " & bb & " FATAL ERROR " & vbcrlf & vbcrlf & err02 & vbcrlf & vbcrlf
objOutputFile02.WriteLine aa
objOutputFile.WriteLine date & " " & time & " ERROR FATAL in query:"
objOutputFile.WriteLine aa & sql
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile02.WriteLine date & " " & time & " " & err02
wscript.quit
end if
elseif iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working "
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
iq=iq+1
loop until iq>250 or err01=0
end functionASKER
since i am not a complete bad guy, here is the file required in the same directory as tthe original script:
first the list of csv to import:
csv_to_sql_liste_fichier_a
clients is the sql DB name at same time is is the directory name the csv will be copied from
first the list of csv to import:
csv_to_sql_liste_fichier_a
clients is the sql DB name at same time is is the directory name the csv will be copied from
clients
m:\temp
Personnes.csv
Adresses.csv
ContactV2.csvASKER
a schema file should look like this:
andn the 1000+ tag is actually a 0, (int type) but +1000 indicate a master key fro sql
andn the 1000+ tag is actually a 0, (int type) but +1000 indicate a master key fro sql
CODEINT;ENTETE;CODE;NOM;STATDESA;GENRE;MARCHE;PERSREL2;LANGUE;NOMLEGAL;DATEOUVE;DIVISION
1000;0;23;24;23;23;0;0;23;25;23;0
ASKER
the schema file have numbers in it (i was lazy to type all thoses format)
here is the file the script use to convert theses numbers into SQL type:
filename:
csv_to_sql_import_formats.
in last post i forgot filename:
ContactV2_schema.csv (table name + _schema)
here is the file the script use to convert theses numbers into SQL type:
filename:
csv_to_sql_import_formats.
in last post i forgot filename:
ContactV2_schema.csv (table name + _schema)
0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25
integer;int;smallint;tinyint;real;float;decimal;numeric;char(255);varchar(255);datetime;smalldatetime;timestamp;bit;binary(255);varbinary(255);text;image;money;smallmoney;varchar(1);varchar(8);varchar(16);varchar(32);varchar(64);varchar(128)
Wow, that is great !
Very comprehensive and sure to be of benefit to the community. Thanks so very much for sharing it all with us...
You have a swag of points, less than 8000 to go to get Master in VB Script, why not sign up ? Keep answering questions and that effectively pays your way. Check it out...
Very comprehensive and sure to be of benefit to the community. Thanks so very much for sharing it all with us...
You have a swag of points, less than 8000 to go to get Master in VB Script, why not sign up ? Keep answering questions and that effectively pays your way. Check it out...
ASKER
i am signed up, i answered questions last year, for a few months
my way was paid already, i just lost it cause i stopped answering questions ;)
i just wish expert exchange will try again to do a "article section" so we can refer answer to an article we write about a script
my way was paid already, i just lost it cause i stopped answering questions ;)
i just wish expert exchange will try again to do a "article section" so we can refer answer to an article we write about a script
They are...
There are rules...
Check in the top right hand corner - you might have an Articles tab...
There are rules...
Check in the top right hand corner - you might have an Articles tab...
ASKER
Problem:
visual studio express bugged and locked one of my AEF (temp) database (indefinitly, or until restart of server, wich we dont do often, it's in production)
Solution:
i use a SPID kill on database system model, when another application lock it (sygate backup exec always bug and lock model database), i kill it's process (sql process)
i will add this "killing of whoever use the database" on the AEF (temp) database
will post update shortly
visual studio express bugged and locked one of my AEF (temp) database (indefinitly, or until restart of server, wich we dont do often, it's in production)
Solution:
i use a SPID kill on database system model, when another application lock it (sygate backup exec always bug and lock model database), i kill it's process (sql process)
i will add this "killing of whoever use the database" on the AEF (temp) database
will post update shortly
you need to wrap that in cotton wool with huge "becareful" signs everwhere !!
ASKER
becareful sign might not be necessary
here is the deal:
i create a database
the create error happen after 30 sec delay (timout on sql command)
i decide to kill SPID (sql processes) that lock my model database
now why would i put a huge "becareful" sign when i procede with caution like this?
i try to create without killing the bad process that lock model
only if i fail for 30 sec i will kill them
now the deal is: no process should EVER lock the model database for 30 sec or more, its only used to create a database (copy, paste the model actually)
here is my latest script, that will kill SPID locking my AEF databases and locking the model too
(after 1 try, and only if an error occur :P)
here is the deal:
i create a database
the create error happen after 30 sec delay (timout on sql command)
i decide to kill SPID (sql processes) that lock my model database
now why would i put a huge "becareful" sign when i procede with caution like this?
i try to create without killing the bad process that lock model
only if i fail for 30 sec i will kill them
now the deal is: no process should EVER lock the model database for 30 sec or more, its only used to create a database (copy, paste the model actually)
here is my latest script, that will kill SPID locking my AEF databases and locking the model too
(after 1 try, and only if an error occur :P)
'=== main program procedure:.
'=== main program procedure:.
' 1. read the sql formats: csv_to_sql_import_formats.csv
' 2. read the list of files to import: csv_to_sql_liste_fichier_a_importer.txt
' 3. open sql database with admin password and dns adress
' 4. read a file with the name of the CSV + "_schema" and use it to know the format of columns to create sql database
' you must create this file manually before importation
' in this file, there is a column format number wich is +1000, its destined to be the main key for database
' (i did not add the sql code to define the main key)
' 5. if database does not exist, we create it
' 6. if column for main key does not exist, we create it
' 7. search in main key for a key identical, if not found, create the record, if found, update record
test=0 '=== 0 write in database id test=1
dim upd_ii, sql1, dummy, iq
iq=1
'=== take name of the sql server from a window variable
sql1=environ("stassql1")
upd_ii=10 '=== update frequency for writing in report log database_log.txt
upd_ii=10 '=== update frequency for writing in report log database_log.txt
ecmax=10 '=== nbr of max try to connect to database
Dim objFSO, file_02, ligne,a
Set objshe = CreateObject("WScript.Shell")
Set objEnv = objshe.Environment("PROCESS")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'=== actual drive, actual directory, and "\"
thepath=WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir = left(thepath,p)
filnam = right(thepath,len(thepath)-p)
'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
a64 = windir & "\syswow64\wscript.exe"
if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
'=== 64 bits system
a = """" & a64 & """ """ & basedir & filnam & """"
objshe.Run a,0, false
wscript.quit
end if
'=== reactivate security chek for zone
if mid(basedir,1,2)="c:" then local=1 else local=0
base_dir = basedir
b = mid(basedir,1,len(basedir)-1)
p = instrRev(b,"\")
basename = right(b,len(b)-p)
'=== restart in 32 bits if in 64 mode
'=== windows dir
'msgbox(p & " --- " & basename & " --- " & basedir)
'=== _liste_fichier_a_importer.txt (this file contain the list of file to import)
' contient:
' 01 name of database (is also the name of directory where to get the csv)
' 02 root directory
' 03 file name (is also "_" + the name of the database, cause what i import is for magica)
sqlprefix = "sp_configure 'show advanced options',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO " & vbcrlf
sqlprefix = sqlprefix & "sp_configure 'Ad Hoc Distributed Queries',1 " & vbcrlf
sqlprefix = sqlprefix & "RECONFIGURE " & vbcrlf
sqlprefix = sqlprefix & "GO "
sqlprefix = sqlprefix & "SET ANSI_NULLS OFF"
sqlprefix = sqlprefix & "GO"
catfile=basedir & "csv_to_sql_liste_fichier_a_importer.txt"
if objfso.fileexists(catfile) then
Set file_02 = objFSO.OpenTextFile(catfile, 1, 0)
else
msgbox("ERREUR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "n'existe pas, on ne peux pas commencer")
' msgbox("ERROR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "list of file to import does not exist, cannot start import")
wscript.quit
end if
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objfso.OpenTextFile(base_dir & a, 2, true)
If Err.number <> 0 Then
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
on error goto 0
set fso=nothing
'=== LOOP for folders ================================================
objOutputFile02.WriteLine date & " " & time & " DEBUT"
timertotal=timer
delaistotal=0
'=== read database name
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\")=0 and instr(ligne,".")=0 Then
db_main=ligne
End If
end if
'=== read directory where the csv is
if file_02.atendofstream <> true then
ligne = file_02.Readline
If InStr(ligne,":\") Then
ligne =lcase(ligne)
if ligne = "m:\temp" then
ligne = "\\corp.stas.local\stas\Magica\Temp"
end if
if local=0 then
db_dir = ligne & "\" & db_main
else
'=== if we are local there is no subdir
db_dir = ligne
end if
End If
eND if
'=== add "mag_" at the start of database name, cause the importation is for a software called "magica"
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
Dim con_02
Set con_02 = CreateObject("ADODB.Connection")
set tag = CreateObject ("ADODB.Recordset")
con_02.ConnectionString = "Driver={SQL Server};Server=sql.corp.stas.local"
errdel=20
con_02.connectiontimeout=errdel
on error resume next
con_02.Open
i=0
do while err<>0 and i<10
con_02.Open
err.clear
i=i+1
if i>10 then
'objOutputFile.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " erreur à l'ouverture de la table - 10 try"
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.end
end if
loop
on error goto 0
'=== expiration de requete sql en secondes
'=== 1 2 3 4 5 (min)
'=== 60,120,180,240,300 (sec)
con_02.commandtimeout=300
'=== DATABASE === cherche la database, pour voir si elle existe
'=== verify is database exist, in the table containing all database name for all sql server
'=== the name of this table might differ for a my_sql server
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db & "'"
set tag = con_02.execute(sql)
'=== db create
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db & "]"
set tag = con_02.execute(sql)
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
else
'msgbox("la database existe déjà, pas supposé")
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db
end if
'=== create a temporary database for bulk import
nom_db2 = nom_db & "_aef"
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
'=== create the sql database
sql = "CREATE DATABASE [" & nom_db2 & "]"
objOutputFile02.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db2
objOutputFile02.WriteLine vbcrlf & sql & vbcrlf
on error resume next
set tag = con_02.execute(sql)
a=""
if err<>0 then
a = lcase(err.description)
end if
on error goto 0
'=== if the error message include "model" it mean model database was locked, preventing us from creating a new database
'=== this lock is often provoked by seagate backup exec (for us at least)
'=== i could kill the sql process using model before, but i prefer to wait to get an error, after all, we must ask kindly before using force
' and instr(a,"impossible de supprimer")
if instr(a,"model") then
'=== chek if model database is locked by a bugged application
'=== a count of more than 0 results = something locked the database
objOutputFile02.WriteLine date & " " & time & " model is locked by another application - killing all SPID that lock MODEL"
s = "select spid, hostname, program_name from master..sysprocesses where db_name(dbid) = 'model'"
on error resume next
set tag = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " WARNING query: " & s & " (probably trying to kill twice same process)"
end if
on error goto 0
while not(tag.eof)
objOutputFile02.WriteLine date & " " & time & " killing: " & tag.fields(0) & " " & tag.fields(1) & " " & tag.fields(2)
'=== kill the process that lock MODEL
s = "kill " & tag.fields(0)
on error resume next
set tag02 = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " WARNING query" & vbcrlf & s & vbcrlf
end if
on error goto 0
tag.movenext
wend
set tag = con_02.execute(sql)
else
objOutputFile02.WriteLine date & " " & time & "ERROR with the first request on the database server" & vbcrlf & a & vbcrlf & "QUITTING PROGRAM"
wscript.quit
end if
else
objOutputFile02.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db2
'=== drop the temporary database (aef = temp) the one with suffix "_aef" we created on last importation
sql = "drop DATABASE [" & nom_db2 & "]"
on error resume next
set tag = con_02.execute(sql)
a=""
if err<>0 then
a = lcase(err.description)
end if
on error goto 0
'=== if for any reason, some sql process prevent us from deleting our own old database (suffix: _aef), we kill thoses sql process
'=== change this for english --> "impossible de supprimer" --> cannot delete (or something like it)
if instr(a,nom_db2) and instr(a,"impossible de supprimer") then
'=== chek if model database is locked by a bugged application
'=== a count of more than 0 results = something locked the database
objOutputFile02.WriteLine date & " " & time & " " & nom_db2 & " is locked by another application - killing all SPID that lock " & nom_db2
'=== find them
s = "select spid, hostname, program_name from master..sysprocesses where db_name(dbid) = '" & nom_db2 & "'"
on error resume next
set tag = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " WARNING query: " & vbcrlf & s & vbcrlf
end if
on error goto 0
'=== and destroy them (like agent Smith would say hehe)
while not(tag.eof)
objOutputFile02.WriteLine date & " " & time & " killing SPID: " & tag.fields(0) & " " & tag.fields(1) & " " & tag.fields(2)
'=== kill the process that lock MODEL
s = "kill " & tag.fields(0)
on error resume next
set tag02 = con_02.execute(s)
if err<>0 then
objOutputFile02.WriteLine date & " " & time & " WARNING query" & vbcrlf & s & vbcrlf
end if
on error goto 0
tag.movenext
wend
set tag = con_02.execute(sql)
end if
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") = 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been dropped " & nom_db2
else
objOutputFile02.WriteLine date & " " & time & " ERROR cannot drop database " & nom_db2
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
end if
sql = "CREATE DATABASE [" & nom_db2 & "]"
set tag = con_02.execute(sql)
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db2 & "'"
set tag = con_02.execute(sql)
if tag("DBCount") <> 0 Then
objOutputFile02.WriteLine date & " " & time & " CONFIRMATION: database have been created " & nom_db2
end if
end if
'=== be sure to "use" this database just in case i forgot to specifie database name in a command
sql = "USE " & nom_db
Do While file_02.AtEndOfStream <> True
'=== read first table
ligne = file_02.Readline
do while mid(ligne,1,1)="'"
ligne = file_02.Readline
loop
on error resume next
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = lcase(nom_db_pre & nom_db_suf)
nom_table_suf = "_schema" '=== ajoute _schema pour le fichier de quel type sont chaque table
'=== change fichier (table)
'=== change file (the name of file is also the name of the table)
'=== name of table, remove the ".csv" part
nom_table = mid(ligne,1, len(ligne) -(len(ligne) -(instr(ligne,".")-1)))
'=== copy the csv locally, or else we cannot import, because the service sql must access the file, not the actual user
sou = db_dir & "\" & ligne
des = "c:\aef\" & nom_db & "_" & left(ligne,len(ligne)-4) & "_aef.csv"
objFSO.copyFile sou, des, TRUE
if not objfso.fileexists(des) then
objOutputFile02.WriteLine date & " " & time & " fichier pas copie: " & des
objOutputFile02.WriteLine date & " " & time & " script arrete "
fatal01=1
end if
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " debut importation " & db_dir
dim sql
'=== open a log file, create it if not there
'=== 8 = append === 2 = newfile
'on error resume next
on error goto 0
objOutputFile02.WriteLine date & " " & time & " ouverture log table " & base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt"
Set objOutputFile = objfso.OpenTextFile(base_dir & "z_csv_to_sql_log_" & nom_table & basename & ".txt", 2, true)
If Err.number <> 0 Then
Set objOutputFile = objfso.OpenTextFile("c:\_stas\logs\" & a, 2, true)
end if
objOutputFile.WriteLine date & " " & time & " " & "debut"
on error goto 0
'=== CSV connexion - input data
'=== FUTUR: verifier si fichier existe avant de l'ouvrir
Set Con_01 = CreateObject("ADODB.Connection")
on error resume next
Con_01.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & db_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
if err<>0 then
on error goto 0
msgbox("error fatal cannot open directory" & vbcrlf & db_dir)
wscript.quit
end if
'=== erreur si communication marche pas
on error resume next
Set RS_01 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir la connection vers tous les csv " & db_dir
MSGBOX("ERREUR FATALE" & vbcrlf & "pas capable d'ouvrir un csv en lecture" & vbcrlf & db_Dir)
end if
on error goto 0
'=== table de format sql à importer pour stas numérotés de 0 à 22 et +
'=== table containing the sql format as number for faster creation of "_schema" files
Set Con_03 = CreateObject("ADODB.Connection")
Con_03.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
Set RS_03 = CreateObject ("ADODB.Recordset")
sql = "SELECT * from csv_to_sql_import_formats.csv"
'=== reset fatal error counter
fatal01=0
set rs_03 = con_03.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir csv_to_sql_import_formats.csv"
fatal01=1
end if
'=== _schema . csv
'=== import schema, contain number for format
Set Con_04 = CreateObject("ADODB.Connection")
'====== test
Con_04.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & base_Dir & ";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'HDR=YES;FMT=Delimited
'Con_04.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & base_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
on error resume next
Set RS_04 = CreateObject ("ADODB.Recordset")
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, pas capable d'ouvrir le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== _schema . CSV === ouvre la table des types de colonnes à créer
sql = "SELECT * from " & nom_table & nom_table_suf & ".csv"
on error resume next
set rs_04 = con_04.execute(sql)
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
objOutputFile02.WriteLine date & " " & time & " " & "erreur, rien dans le CSV de schema " & nom_table & nom_table_suf & ".csv"
fatal01=1
end if
on error goto 0
'=== SQL
'=== crée une database par rapport
'=== crée une table par fichier csv (voir csv_to_sql_liste_fichier_a_importer.txt, dans le dossier d'origine)
'=== will create one database for every directory to import
'=== will create one table for every file to import
'=== rs = record set = contient un record, une ligne de database
Set RS = CreateObject ("ADODB.Recordset")
Set SQLStmt = CreateObject("ADODB.Command")
'================================================================ sql ======================================
'=== connexion serveur sql, sans se connecter sur une database
'=== MAIN SQL DATABASE CONNECTION
'=== if there was anny fatal error with the csv files, this part is not executed
if fatal01=0 then
'=== read CSV file
fatal02 = 0
sql = "SELECT * from " & nom_table & ".csv"
on error resume next
'=== colonne
set rs_01 = con_01.execute(sql)
'=== data csv
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & rs_01(table_clef).name
objOutputFile02.WriteLine date & " " & time & " " & db_main & " csv vide de contenu SKIPPING"
'objOutputFile02.WriteLine date & " " & time & " "
'wscript.quit
fatal02 = 1
End If
on error goto 0
if fatal02 = 0 then
'=== primary key column (format >999 + format type)
table_clef=-1
i=0
while i<RS_04.fields.count
'msgbox(rs_04(i) & vbcrlf & i)
'msgbox(RS_04(i))
if rs_04(i)>999 then
table_clef=i
'msgbox(table_clef)
end if
i=i+1
wend
'=== did not find any primary key - stopping import
if table_clef=-1 then
'=== stop program is there is no master key
a="vous avez oublié de spécifier une clef de base dans votre base de donnée soit un format +1000"
'msgbox(a)
objOutputFile.WriteLine date & " " & time & a & nom_db
objOutputFile02.WriteLine date & " " & time & " pas de table clef -- le script est arrêté!!!"
Wscript.quit
end if
'=== verify if table exist, if not create it with FIRST OCOLUMN, not primary key anymore (bulk insert requirement)
if rs_04(0)>999 then
format01 = rs_04(0)-1000
else
format01 = rs_04(0)
end if
dummy = VER_CRE_table(nom_db, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== CREATE TABLES
'=== verify if table exist, if not create it with primary key
objOutputFile02.WriteLine date & " " & time & " CREATION table " & nom_table
'=== create AEF (to delete) table for importations
dummy = VER_CRE_table(nom_db2, nom_table, rs_03(format01), rs_04(0).name, 1)
'=== > verify if number of columns in CSV are same that in SCHEMA
exactcolumn =0
if exactcolumn =1 then
if RS_04.fields.count>RS_01.fields.count then
A=" le nombre de colonnes dans le csv d'origine est < que celui du schema"
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & " " & a
a=" " & RS_04.fields.count-RS_01.fields.count & " colonne(s) en trop sera(ont) crée(s) "
objOutputFile.WriteLine date & " " & time & " " & a & nom_db
a=""
b=""
i=0
y=0
while i<RS_04.fields.count
a = a + " " + rs_04(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + string(len(rs_01(y).name),"_")
else
b = b + " " + rs_01(y).name
y=y+1
end if
err.clear
If Err <> 0 Then
b = b + space(len(rs_04(i)))
err.clear
End If
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & b
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 1 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== < verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count<RS_01.fields.count then
'=== rs_01 = csv === rs_04 = schema
A = " le nombre de colonnes dans le csv d'origine est > que celui du schema"
a = a & vbcrlf & RS_04.fields.count &" "& RS_01.fields.count& " "
'MSGBOX(A)
objOutputFile.WriteLine date & " " & time & a & nom_db
a = " impossible de continuer car il vous manquera des données "
objOutputFile.WriteLine date & " " & time & a & nom_db
a=""
b=""
I=0
y=0
while i<RS_01.fields.count
a = a + " " + cstr(i) + " " + rs_01(i).name
on error resume next
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
b = b + " " & string(len(rs_01(y).name),"_")
else
b = b + " " + cstr(y) + " " + rs_04(y).name
y=y+1
end if
on error goto 0
i=i+1
wend
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 2 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
'end if
'=== = verify if number of columns in CSV are same that in SCHEMA
if RS_04.fields.count=RS_01.fields.count then
a=""
b=""
I=0
y=0
tag=0
while i<RS_01.fields.count
a = a + " " + rs_01(i).name
b = b + " " + rs_04(y).name
IF lcase(rs_01(i).name) <> lcase(rs_04(y).name) then
tag=1
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
end if
on error goto 0
i=i+1
y=y+1
wend
if tag=1 then
objOutputFile.WriteLine date & " " & time & " ORIGINE " & a
objOutputFile.WriteLine date & " " & time & " DESTINATION" & B
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 3 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
end if
'=== column creation
i=0
while i<RS_04.fields.count
'=== clef primaire = 0 ou 1
a=rs_04(i)
on error resume next
if a>999 then
a=a-1000
end if
'=== make a new column (at this point, the table and key column is already done)
dummy = VER_CRE_col(nom_db, nom_table, rs_03(a), rs_04(i).name, 0)
'=== make a new column (at this point, the table and key column is already done)
'***************************************************************************************************
dummy = VER_CRE_col(nom_db2, nom_table, rs_03(a), rs_04(i).name, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " database..: -> " & nom_db & " <- nomfisrtcolumn..: ->" & rs_04(i).name & "<- numero clef unique..: "
objOutputFile.WriteLine date & " " & time & " -i- " & i & " -a- " & a & " -count- " & RS_04.fields.count
objOutputFile02.WriteLine date & " " & time & " " & db & " ERREUR de schema 4 -- voir log de la table " & nom_table
objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
wscript.quit
end if
i=i+1
on error goto 0
wend
'=== define primary key
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
'not null
'ALTER TABLE supplier
'MODIFY supplier_name varchar2(100) not null;
's = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
'objOutputFile.WriteLine date & " " & time & " " & s
'b = retsql(s,"table clef")
'=== table [employés] is a mix of ldap and csv, so we dont have any primary key
if nom_db <> "mag_employés" and nom_table <>"personnes" then
s = "use " & nom_db & vbcrlf
'objOutputFile.WriteLine date & " " & time & " " & s
'b = retsql(s,"count table clef")
s = s & "select count(c.COLUMN_NAME) as col01 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, "
s = s & "INFORMATION_SCHEMA.KEY_COLUMN_USAGE c " & vbcrlf
s = s & "where pk.TABLE_NAME = '" & nom_table & "' "
s = s & "and CONSTRAINT_TYPE = 'PRIMARY KEY' "
s = s & "and c.TABLE_NAME = pk.TABLE_NAME "
s = s & "and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME "
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"count table clef")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " number of primary key: " & recimp
if recimp=0 then
'=== no primary key, we create one
s = "ALTER TABLE [" & nom_db & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
end if
if nom_db2 = "mag_employés_aef" and nom_table ="personnes" then
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [codeint] int not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([codeint])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
else
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] alter column [" & rs_04(table_clef).name & "] " & rs_03(rs_04(table_clef)-1000) & " not null"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef 2")
s = "ALTER TABLE [" & nom_db2 & "].dbo.[" & nom_Table & "] ADD PRIMARY KEY ([" & rs_04(table_clef).name & "])"
objOutputFile.WriteLine date & " " & time & " " & s
b = retsql(s,"table clef")
end if
ii=0
'=== new bulk import here
'=== copy file locally on sql server (stas-host-01), it work only locally cause you need a network logon to work networked
fil01 = db_dir & "\" & nom_table & ".csv"
fil01 = nom_table & ".csv"
TABaef = nom_table
nom_db2 = nom_db & "_aef"
'=== basic filename for all .FMT et .CSV
fil02 = nom_db & "_" & tabaef
fil01 = fil02 & ".csv"
'=== empty the AEF table to be sure
sql = "SELECT COUNT(*) As 'DBCount' FROM [" & nom_db2 & "].dbo.[" & tabaef & "]"
set tag = con_02.execute(sql)
if tag("DBCount") > 0 Then
objOutputFile02.WriteLine date & " " & time & " database is not empty, drop failed " & nom_db2
objOutputFile02.WriteLine date & " " & time & " countermesures: delete all item in it " & nom_db2
sql = "delete from [" & nom_db2 & "].dbo.[" & tabaef & "]"
objOutputFile02.WriteLine date & " " & time & sql
set tag = con_02.execute(sql)
end if
's = sqlprefix & "" & vbcrlf
s = ""
's = s & "INSERT INTO " & nom_db2 & ".dbo.[" & tabaef & "] " & vbcrlf
'=============== ----------- ============== -------------- =============== -------------
'=== new technique with objects
'//This is the connection string to connect to your csv file
'string strConString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\; Extended Properties=\ Text;HDR=YES;\ ";
'// open connection
'OleDbConnection oCon = new OleDbConnection(strConString);
'oCon.Open();
'// fill data set
'//csvfile should be present in c:
'string strSql = SELECT * FROM csvfile.csv ;
'OleDbDataAdapter oDA = new OleDbDataAdapter(strSql, oCon);
'DataSet oData = new DataSet();
'oDA.Fill(oData, ABC );
'GridView1.DataSource = oData;
'GridView1.DataBind();
'oCon.Close();
'LOAD DATA INFILE 'c:/abc.csv' INTO TABLE stu
'FIELDS TERMINATED BY '\t'
'LINES TERMINATED BY '\n';
' Set Con_05 = CreateObject("ADODB.Connection")
' Con_05.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\aef\; Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1"""
'BULK INSERT mag_employés_Aef.dbo.personnes FROM 'c:\aef\personnes.csv' WITH (FIRSTROW=2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')
'go
's = s & "SET QUOTED_IDENTIFIER OFF" & vbcrlf
's = s & "go" & vbcrlf
'DATAFILETYPE = 'char',
's = s & "SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & VBCRLF
'S = S & "'Text;Database=C:\AEF\;HDR=YES;FMT=Delimited;IMEX=1','SELECT " & s2 & " FROM " & fil01 & "')" & VBCRLF
'if nom_db2 = "mag_employés_aef" and tabaef="personnes" then
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL) AND (STATDESA = '' OR STATDESA IS NULL) AND (nom IS NOT NULL)"
'else
' s = s & "WHERE (" & rs_04(table_clef).name & " IS not NULL)"
'end if
'=== FMT format file
'9.0
'12
'1 SQLCHAR 0 12 ";\"" 1 CODEINT ""
'2 SQLCHAR 0 32 "\";\"" 2 DIM1TXT French_CI_AS
'3 SQLCHAR 0 32 "\";" 3 DIM2TXT French_CI_AS
'4 SQLCHAR 0 30 ";" 4 SETUP ""
'5 SQLCHAR 0 30 ";\"" 5 UNIT ""
'6 SQLCHAR 0 32 "\";\"" 6 SETUPTXT French_CI_AS
'7 SQLCHAR 0 32 "\";\"" 7 UNITTXT French_CI_AS
'8 SQLCHAR 0 32 "\";" 8 VENTFIN French_CI_AS
'9 SQLCHAR 0 30 ";\"" 9 UNITLOC ""
'10 SQLCHAR 0 0 "\";\"" 10 COMMENTA French_CI_AS
'11 SQLCHAR 0 128 "\";\"" 11 ALPHA French_CI_AS
'12 SQLCHAR 0 0 "\"\r\n" 12 DIVERS1 French_CI_AS
a = fil02 & ".fmt"
'=== bcp generate first FMT automatically
a = "bcp [" & nom_db2 & "].dbo.[" & tabaef & "] format nul -c -t; -f c:\aef\" & fil02 & "_aef.Fmt -T -S stas-sql-01"
objOutputFile.WriteLine date & " " & time & vbcrlf & vbcrlf & a & vbcrlf
objshe.run a,0, true
'=== read original FMT (_aef)
on error resume next
Set objfil03 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.Fmt", 1, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open original FMT file" & vbcrlf & fil02 & "_aef.fmt" & vbcrlf
end if
on error goto 0
'=== read CSV separate columns = lines for FMT file
on error resume next
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.csv", 1, 0)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open original CSV file" & vbcrlf & fil02 & "_aef.CSV" & vbcrlf
end if
on error goto 0
'=== skip column name
a = objfil04.readline
'=== line to find separators, double quote for char, nothing for numbers
'=== must read char by char, until we met char(13) and char(10)
'c = objfil04.readline
'=== tagret = if we meet chr(13) and chr(10), it will equal to 2
tagret = 0
'=== chr(10) is not a end of line, just a change of line inside a string
'=== chr(13) and chr(10) is a END OF LINE
c = ""
while tagret < 2
if not objFil04.AtEndOfStream then
a = objFil04.Read(1)
if tagret=1 then
if a=chr(10) then
tagret = tagret + 1
end if
else
tagret = 0
end if
if a<>chr(13) then
c = c & a
else
tagret = tagret + 1
end if
end if
wend
objfil04.close
'=== write new FMT file with good separators
on error resume next
Set objfil05 = objfso.OpenTextFile("c:\aef\" & fil02 & "_aef2.fmt", 2, true)
if err<>0 then
objOutputFile.WriteLine date & " " & time & "ERROR cannot open destination FMT file" & vbcrlf & fil02 & "_aef2.FMT" & vbcrlf
end if
on error goto 0
'=== sql version
a = objFil03.readLine
objFil05.WriteLine a
'=== number of columns
a = objFil03.readLine
objFil05.WriteLine a
i=0
b = RS_04.fields.count
'=== loop all line from original FMT file
'=== at the same time we analyse the variable C (second line from data file) to find all separators
inside = 0 '=== we are inside "" ignore the ;
sep = "" '=== separator between this field and the next
end01 = ""
end02 = ";"
end03 = ""
'=== dummy string with all the good separators to replace first line (column titles) in csv
'=== if we do not replace this line, first REAL data line is skipped
dum01 = ""
objOutputFile.WriteLine date & " " & time & " line to analyse: " & c & vbcrlf
'=== this loop scan the FMT and at the same time all the columns of the CSV (field count of the csv)
'=== obviously, it should be the same number of line(fmt)/columns(csv), because the FMT is generated
'=== with the sql database(temp-aef) that was created by the CSV schema
while i<b
a = objFil03.readLine
'=== loop in C chain to find any ";" outside "" and all the ""
if mid(c,1,1)="""" then
inside = 1
end01 = "\"""
c = right(c,len(c)-instr(c,""";")-1)
'objOutputFile.WriteLine date & " " & time & " line to analys2: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
else
c = right(c,len(c)-instr(c,";"))
'objOutputFile.WriteLine date & " " & time & " line to analys3: " & c & vbcrlf
if mid(c,1,1)="""" then
end03 = "\"""
end if
end if
if instr(a,""";""") then
a = replace(a,""";""","""" & end01 & end02 & end03 & """")
dum01 = dum01 & end01 & end02 & end03
dum01= replace(dum01,"\","")
elseif instr(a,"\r\n") then
a = replace(a,"""\r\n""","""" & end01 & "\r\n" & """")
end04 = replace(end01,"\","")
end05 = replace(end02,"\","")
end06 = replace(end03,"\","")
dum01 = dum01 & end04
end if
end01 = ""
end03 = ""
objFil05.WriteLine a
i = i + 1
wend
objfil03.close
objfil05.close
'=== manipulate the FMT again if the first char is a "
'=== create a false column with " as terminator in FMT
'=== change the numbers for all the columnns in the FMT since we have one more at the start
sou = "c:\aef\" & fil02 & "_Aef2.fmt"
des = "c:\aef\" & fil02 & ".fmt"
if mid(dum01,1,1) = """" then
objOutputFile.WriteLine date & " " & time & " FMT making a new FMT because the first column in the CSV have a "" separator" & vbcrlf
'=== remove the " from the first fmt line (the dummy line replacing the column names)
dum01 = right(dum01, len(dum01)-1)
Set objfil04 = objFSO.OpenTextFile(sou, 1, 0)
Set objfil05 = objfso.OpenTextFile(des, 2, true)
'=== sql version
a = objFil04.readLine
objFil05.WriteLine a
'=== number of columns
a = objFil04.readLine
'=== add 1 to the number of line
'=== the first column separator will be ", basically, it will end at the ", and have no content at all
'=== but it will remove the " from my data in first column bulk import
b = cint(a)
'=== add one line to FMT (1 column to CSV)
b = b + 1
a = trim(cstr(b))
objFil05.WriteLine a
colnum = 1
'=== new line as first column finish by "
objFil05.WriteLine "1 SQLCHAR 0 255 ""\"""" 0 DummyField SQL_Latin1_General_CP1_CI_AS"
colnum = colnum + 1
Do Until objFil04.AtEndOfStream
a = objFil04.readLine
'=== now i must find all the columns number and upgrade them +1
ara01 = split(a," ")
b=""
i=1
'8.0
'4
'1 SQLCHAR 0 255 "\"" 0 DummyField SQL_Latin1_General_CP1_CI_AS
'2 SQLCHAR 0 255 "\",\"" 1 Fieldname1 SQL_Latin1_General_CP1_CI_AS
'3 SQLCHAR 0 255 "\",\"" 2 FieldName2 SQL_Latin1_General_CP1_CI_AS
'4 SQLCHAR 0 255 "\"\r\n" 4 FieldName3 SQL_Latin1_General_CP1_CI_AS
for each c in ara01
if len(trim(c))>0 then
if i=1 then
c = colnum
objOutputFile.WriteLine date & " " & time & " FMT before: " & c & " After: " & colnum & vbcrlf
end if
if i=6 then
c = colnum - 1
objOutputFile.WriteLine date & " " & time & " FMT before: " & c & " After: " & colnum & vbcrlf
end if
b=b & c & " "
i=i+1
end if
next
colnum = colnum + 1
objFil05.WriteLine b
loop
objfil05.close
objfil04.close
else
'=== no change in first column of fmt, simple copy the second copy (_Aef2.fmt) to be the original for the bulk import (.fmt)
objOutputFile.WriteLine date & " " & time & " no change in FMT" & vbcrlf
objFSO.copyFile sou, des, TRUE
end if
objOutputFile.WriteLine date & " " & time & " inserting a dummy line in place of the columns title line in the CSV" & vbcrlf
objOutputFile.WriteLine date & " " & time & " " & dum01 & vbcrlf
'sou = db_dir & "\" & ligne
'des = "c:\aef\" & left(ligne,len(ligne)-4) & "_aef.csv"
'objFSO.copyFile sou, des, TRUE
Set objfil04 = objFSO.OpenTextFile("c:\aef\" & fil02 & "_aef.csv", 1, 0)
Set objfil05 = objfso.OpenTextFile("c:\aef\" & fil02 & ".csv", 2, true)
a = objFil04.readLine
objFil05.Write dum01 & chr(13) & chr(10)
objOutputFile.WriteLine date & " " & time & " START binary read" & vbcrlf
Do Until objFil04.AtEndOfStream
a = objFil04.Read(1)
objfil05.write a
Loop
objOutputFile.WriteLine date & " " & time & " END binary read" & vbcrlf
'=== last line in one of my CSV is cut in half
'=== bluk insert can tolerate many error, but in this case nothing will be imported
'=== so i add a fake line at the end of the file
if lcase(nom_db2) = "mag_fournisseurs_aef" and lcase(tabaef) = "personnes" then
objFil05.Write dum01 & chr(13) & chr(10)
'"WARNING data conversion failed in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " this table have a fake line inserted" & vbcrlf
objOutputFile.WriteLine date & " " & time & " there will be at least one data conversion failed" & vbcrlf
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " this table have a fake line inserted (to prevent bad end of file)"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " there will be at least one data conversion failed"
end if
objfil05.close
objfil04.close
s = s & "BULK INSERT [" & nom_db2 & "].dbo.[" & tabaef & "] FROM 'c:\aef\" & fil01 & "' WITH (FORMATFILE = 'C:\aef\" & fil02 & ".Fmt', CODEPAGE ='RAW', DATAFILETYPE = 'char', FIRSTROW=2, MAXERRORS=10)"
objOutputFile.WriteLine date & " " & time & " " & " bulk insertion of all the CSV file, with a dynamic MFT" &vbcrlf & vbcrlf & s & vbcrlf
'on error resume next
b = retsql(s,"bulk")
'on error goto 0
'=== verify importation (count elements in new table)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count import")
recimp = b(0)
objOutputFile.WriteLine date & " " & time & " " & recimp & " records from csv to " & nom_db2 & ".dbo.[" & TABAEF & "]"
'=== count records that will be updated
s = "SELECT count(*) "
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t1," & nom_db & ".dbo.[" & nom_table & "] t2"
s = s & " " & vbcrlf & "WHERE t1." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
b = retsql(s,"count update")
recUPD = b(0)
objOutputFile.WriteLine date & " " & time & " " & recupd & " UPDATES in " & nom_db & ".dbo.[" & nom_table & "]"
'=== query update
i=0
s = "UPDATE " & nom_db & ".dbo.[" & nom_table & "] "
s = s & vbcrlf & "SET "
b = RS_04.fields.count
while i<b
if i<>table_clef then
s = s & "[" & rs_04(i).name & "]"& "=t2.[" & rs_04(i).name & "]"
s = s & ","
end if
i = i + 1
wend
s = left(s,len(s)-1)
s = s & " " & vbcrlf & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 "
s = s & " " & vbcrlf & "WHERE " & nom_db & ".dbo.[" & nom_table & "]." & rs_04(table_clef).name & "=t2."& rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " update query" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== delete all updates done in aef table (temp)
s = "DELETE " & nom_db2 & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "FROM " & nom_db2 & ".dbo.[" & TABAEF & "] t2 " & vbcrlf
s = s & "INNER JOIN " & nom_db & ".dbo.[" & TABAEF & "] " & vbcrlf
s = s & "ON " & nom_db & ".dbo.[" & TABAEF & "]." & rs_04(table_clef).name & " = t2." & rs_04(table_clef).name
objOutputFile.WriteLine date & " " & time & " delete query (delete updated elements to insert the rest)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== count records left in temp table (will be inserts quantities)
s = "select count(*) from " & nom_db2 & ".dbo.[" & TABAEF & "]"
b = retsql(s,"count insert")
recins = b(0)
objOutputFile.WriteLine date & " " & time & " " & recins & " INSERT in " & nom_db & ".dbo.[" & nom_table & "]"
'=== insert all the rest
i=0
s2=""
b = RS_04.fields.count
while i<b
s2 = s2 & "[" & rs_04(i).name & "]"
i = i + 1
s2 = s2 & ","
wend
s2 = left(s2,len(s2)-1)
s = "INSERT INTO " & nom_db & ".dbo.[" & nom_table & "] " & vbcrlf
s = s & "(" & s2 & ") "
s = s & "SELECT "
s = s & s2
s = s & " FROM " & nom_db2 & ".dbo.[" & TABAEF & "] "
objOutputFile.WriteLine date & " " & time & " insert query (insert the remaining elements)" & vbcrlf & vbcrlf & s & vbcrlf
b = retsql(s,"bulk")
'=== fin condition fatal error
end if 'fatal02
end if 'fatal01
'=========================== end of main loop ===========================================
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
' count=count+1
'loop
'=== efface elements
'sql = "DELETE FROM cds_cat WHERE catid = 'test'"
'set bine = con_02.Execute(sql)
'readfile.close
set readfile=nothing
set fs=nothing
'rs.close
set rs=nothing
con_01.close
set con_01=nothing
con_03.close
set con_03=nothing
con_04.close
set con_04=nothing
objOutputFile.WriteLine date & " " & time & " " & ii & " record(s). end of importations"
'=== ferme le fichier text de log file
objOutputFile.Close
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recimp & " from CSV"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recupd & " UPDATED"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " " & recins & " INSERTED"
if ii=0 then
objOutputFile02.WriteLine date & " " & time & " ----------------------------------------------------------------------"
end if
'=== delete csv after import, it was copied locally just for the import
des = "c:\aef\" & ligne
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & ".csv"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef.csv"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & ".fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef.fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
des = "c:\aef\" & fil02 & "_aef2.fmt"
if objFSO.fileEXISTS(des) then
objFSO.deleteFile(des),TRUE
end if
Loop
con_02.close
set con_02 = nothing
SET TAG = NOTHING
objOutputFile02.Close
'=== database finie === on crée un fichier bidon pour indiquer que toute la database est fini d'importer
a = "zz_csv_to_sql_log_fini_TOUT" & basename & ".txt"
on error resume next
Set objOutputFile02 = objFso.OpenTextFile(base_dir & a, 8, true)
If Err.number <> 0 Then
err.clear
Set objOutputFile02 = objfso.OpenTextFile("c:\_stas\logs\" & a, 8, true)
end if
on error goto 0
objOutputFile02.WriteLine date & " " & time & " fini TOUTES LES TABLES "
objOutputFile02.WriteLine date & " " & time & " temp total (sec, avec delais): " & timer - timertotal
objOutputFile02.WriteLine date & " " & time & " temp total (sec, SANS delais): " & (timer - timertotal)- delaistotal
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FIN "
objOutputFile02.Close
'if local=1 then
objEnv("SEE_MASK_NOZONECHECKS") = 1
script01 = "wscript.exe \\corp.stas.local\stas\NetLogon\users\notification_balloon.vbs"
b=""
c=int(timer - timertotal)
if c>60 then
b=int(c/60) & " min"
else
b=c & " sec"
end if
a=" "" FIN "" ""MAJ " & db_main & "`nTemps: " & b & """"
objshe.Run script01 & a & " 30 1+16", , False
objEnv.Remove ("SEE_MASK_NOZONECHECKS")
'end if
Set objfso = Nothing
'==========================================================================================
'=== SUB fonction pour extraire une variable d'environnement du dos
Function Environ(VarName)
Dim wss, env
Set wss = CreateObject("WScript.Shell")
Set env = wss.environment("process")
Environ = env(VarName)
If Environ = "" Then
Set env = wss.environment("system")
Environ = env(VarName)
End If
End Function
'===========================================================================================
'=== fonction verify if table exist, if not create it with a first column
function VER_CRE_TABle(db, table, col_clef_form, col_clef_name, primaire)
sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"
tag=retsql(sql,"CREE TABLE")
'========== la table n'existe pas, on la crée avec une colonne de base
if tag("tableCount") = 0 Then
'msgbox(table_clef_name)
'sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
sql = "CREATE TABLE " & db & ".dbo." & table & "(" & col_clef_name & " " & col_clef_form & ")"
'msgbox(sql)
objOutputFile.WriteLine date & " " & time & " DEBUT table CREATE table " & db & vbcrlf & sql & vbcrlf & col_clef_form & " " & col_clef_name
set tag = con_02.execute(sql)
objOutputFile.WriteLine date & " " & time & " FINI table CREATE table " & db
'msgbox("SQL TABLE ajoutée")
end if
if lcase(db)="mag_inventaire" and lcase(table)="etatv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="itemv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
if lcase(db)="mag_inventaire" and lcase(table)="revv3" then
objOutputFile02.WriteLine date & " " & time & " EFFACEMENT COMPLET db: " & db & " table:" & table
sql = ""
'tag = con_02.execute(sql)
end if
end function
function VER_CRE_col(db, table, col_clef_form, col_clef_name, primaire)
on error goto 0
'=== compte le nombre de colonne avec le nom qu'on cherche
'=== count the number of columns that have a certain name in the database
sql = "SELECT COUNT(*) As tableCount FROm " & db & ".dbo.syscolumns WHERE ID = (SELECT ID from " & db & ".dbo.sysobjects WHERE name='" & table & "') AND name = '" & col_clef_name & "';"
tag=retsql(sql,"CREE COLONNE")
'msgbox(tag("tableCount") & " " & sql)
if tag("tableCount") = 0 then
'=== on ajoute une colonne de données
'=== add a column in database
on error resume next
'=== colonne
If Err.number <> 0 Then
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " table manquante " & col_clef_name
err.clear
wscript.quit
End If
'=== ajoute colonne
sql = "ALTER TABLE " & db & ".dbo." & table & " ADD [" & col_clef_name & "] " & col_clef_form
'=== commentaire quand on ajoute une colonne
objOutputFile.WriteLine date & " " & time & " " & "ajout d'une colonne " & sql
on error resume next
if test=0 then
tag=retsql(sql,"CREE COLONNE")
end if
If Err.number <> 0 Then
objOutputFile.WriteLine date & " " & time & " " & ii
objOutputFile.WriteLine date & " " & time & " " & db & " " & table & " " & sql
objOutputFile.WriteLine date & " " & time & " " & err.description
objOutputFile.WriteLine date & " " & time & " " & col_clef_name
objOutputFile02.WriteLine date & " " & time & " " & db & " erreur ajout de colonne " & col_clef_name
err.clear
wscript.quit
End If
on error goto 0
else
objOutputFile.WriteLine date & " " & time & " " & ii
end if
end function
function retsql(sql,bb)
iq=1
err01 = 0
do
on error resume next
set retsql = con_02.execute(sql)
err01 = err.number
err02 = err.description
on error goto 0
if err01<>0 then
if instr(err02,"Ce serveur SQL n'existe pas ou son acc")<>0 or _
instr(err02,"lai d'attente expir")<>0 or _
instr(err02,"Erreur réseau générale. Consultez la documentation relative à votre réseau.") then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " did not respond in "& errdel & " sec " & err02
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa
elseif instr(err02,"Violation de la contrainte PRIMARY KEY")<>0 or instr(err02,"Violation of PRIMARY KEY constraint")<>0 then
'=== already existed so its normal error for an insert
if iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working"
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile.WriteLine date & " " & time & " " & db_main & " FATAL ERROR Violation de la contrainte PRIMARY KEY"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
iq=251
'objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " this line already existed "
wscript.quit
elseif instr(err02,"Impossible trouver la ligne dans sysobjects pour") then
objOutputFile02.WriteLine date & " " & time & " ERROR: " & err02
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR Impossible trouver la ligne dans sysobjects pour"
'objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
'wscript.quit
elseif instr(err02,"chec de la liaison de communication") then
'msgbox("echec liaison comm")
objOutputFile02.WriteLine date & " " & time & " " & db_main & " ERREUR FATALE echec liaison comm"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
elseif instr(err02,"Erreur de conversion des donn") then
depmax=0
set SQLerrors = con_02.errors
'=== string with all the defect lines
errcon = ""
for each SQLerror in SQLerrors
a = lcase(sqlerror)
if instr(a,"passement du nombre maximal") then
depmax=1
end if
if instr(a,"ligne ") then
errcon = errcon & date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed: " & right(a,len(a)-instr(a,"ligne ")+1) & vbcrlf
end if
next
if depmax=0 then
'objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING data conversion failed in bulk insert (less than 10)"
'=== display only the bad lines numbers, not the error, cause another script manage all my error and i consider less than 10 error as a warning
objOutputFile02.WriteLine errcon
else
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " ERROR data conversion failed in bulk insert (more than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " ERROR data conversion failed in bulk insert (more than 10)"
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
wscript.quit
end if
'=== no retry for this error, its tolerated
err01=0
elseif instr(err02,"une fin de fichier inattendue") then
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING bad end of file in bulk insert (less than 10)"
objOutputFile.WriteLine date & " " & time & " " & db_main & " " & nom_table & " WARNING bad end of file in bulk insert (less than 10)"
'=== no retry for this error, its tolerated
err01=0
else
aa= date & " " & time & " " & db_main & " " & nom_table & " " & bb & " FATAL ERROR " & vbcrlf & vbcrlf & err02 & vbcrlf & vbcrlf
objOutputFile02.WriteLine aa
objOutputFile.WriteLine date & " " & time & " ERROR FATAL in query:"
objOutputFile.WriteLine aa & sql
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " FATAL ERROR "
objOutputFile02.WriteLine date & " " & time & " " & db_main & " " & nom_table & " SCRIPT STOPPED "
objOutputFile02.WriteLine date & " " & time & " " & err02
wscript.quit
end if
elseif iq>1 then
aa=date & " " & time & " " & db_main & " " & nom_table & " try " & iq & " Working "
'objOutputFile02.WriteLine aa
objOutputFile.WriteLine aa & sql
end if
iq=iq+1
loop until iq>250 or err01=0
end function
I meant in the code. locks or timeouts could happen for a number of reasons. sometimes important to know why, other times it can be a matter of try again.
Reminds me of a time with my wife in the car closing the glovebox. first attempt didn't "catch", second attempt, same ting. third attempt was an almighty "slam" only to find the gift that was in the glovebox became a little "bent". In that case, it would have been easy to simply shuffle a little a bit.
Reminds me of a time with my wife in the car closing the glovebox. first attempt didn't "catch", second attempt, same ting. third attempt was an almighty "slam" only to find the gift that was in the glovebox became a little "bent". In that case, it would have been easy to simply shuffle a little a bit.
ASKER
line 237 kill the process that lock model database
you can remove that line if you dont want to kill something important locking model database
for the others spid kills, that lock my own databases, i know they should be free
my data importation are more important than backuping a temp database
there is effectively a flaw in my killing spray of the spid locking model database
i try to use model once, and after 30 sec, i get a "locked by other process" error
i assume that in the 30 sec, sql will not only try once, but many times
i could use he same loop i use for all my query, and try many times before flushing he SPIDs that lock it
but, i will do that if someone in the universe can confirm, that after 30 sec of timeout, sql only tried to create my database once
you can remove that line if you dont want to kill something important locking model database
for the others spid kills, that lock my own databases, i know they should be free
my data importation are more important than backuping a temp database
there is effectively a flaw in my killing spray of the spid locking model database
i try to use model once, and after 30 sec, i get a "locked by other process" error
i assume that in the 30 sec, sql will not only try once, but many times
i could use he same loop i use for all my query, and try many times before flushing he SPIDs that lock it
but, i will do that if someone in the universe can confirm, that after 30 sec of timeout, sql only tried to create my database once
ASKER
removing first row was the best solution (i put a dummy row)
here is my code:
i wanted to remove first row, but i was unable to in vbscript
i was using readline and writeline (in vbscript) to remake the CSV without the first row
chr(10) was playing tricks on me, readline consider it as a line separator, but in my csv, it's simply a change of line inside a string
the real separator being chr(13) and chr(10)
here is my code to remake the csv without the first row:
dum01 is actually a dummy first row that look like this:
;"";"";"";"";"";"";"";"";"
i could have put no row at all also
but i use this "fake" row to generate my FMT half dynamically (with BCP, and modifying after)
objfil04 is the source CSV
objfil05 is the CSV rewriten with a new first row
its faster than i tought, the script do like 200 char every second when is remake my csv
it's good enough for me
if anyone is interestedd in the full script, just say so
i already my _csv_to_sql.vbs script on another post
but this one is an adaptation to BULK import and sql 2005
here is my original script: (sql 2000)
https://www.experts-exchange.com/questions/23931018/Converting-Excel-Document-Values-to-be-ready-for-MySQL-Import.html?sfQueryTermInfo=1+10+csv+wildboy85
Open in new window