Solved

bulk import double quotes in some columns, not in first row

Posted on 2009-05-05
21
1,701 Views
Last Modified: 2012-06-21
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

http://www.experts-exchange.com/Database/Miscellaneous/Q_23377319.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.[entete]
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

Open in new window

0
Comment
Question by:Serge Fournier
  • 13
  • 8
21 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24313192
Well, it is skipping the very first row because of those delimiters...  and then if you say firstrow = 1 it suddenly decides to look at the very first row. catch-22 and not very helpful.

Same seperators will not really help, because the first row are column names and do not match the actual columns, so think that there will still be problems.

Best to leave out the header altogether, you have the columns named in the format file, so it is not needed for the import. Or, put in a "dummy" row 1 for it to deliberately skip over. Or remove quote encapsulation. Think it is easiest to exclude the header row.

The file as is, you could also import using just the semicolon and the strip the quotes... But then we start to get into the realm of staging tables (best approach anyway).

What alternatives / constraints do we have to deal with or the options available to us ?  At the moment that format file and that structure will not import the first row.
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24326644
good answer!
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)
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23931018.html?sfQueryTermInfo=1+10+csv+wildboy85


      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

Open in new window

0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24326658
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)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23931018.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 function

Open in new window

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 12

Author Closing Comment

by:Serge Fournier
ID: 31578173
good, i would have liked the code, but i did not say wich language so you get an A hehe
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24326673
Looks good... have tagged the page for future reference :)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24326736
Wow, have just finished reading it. Might need to check out that other link :)
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24327110
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)

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24327409
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.

0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24328697
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
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24384560
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:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23931018.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 function

Open in new window

0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24384633
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_importer.txt

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.csv

Open in new window

0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24384643
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

CODEINT;ENTETE;CODE;NOM;STATDESA;GENRE;MARCHE;PERSREL2;LANGUE;NOMLEGAL;DATEOUVE;DIVISION
1000;0;23;24;23;23;0;0;23;25;23;0

Open in new window

0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24384666
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.csv

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)

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24386562
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...
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24387411
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



0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24387441
They are...

There are rules...

Check in the top right hand corner - you might have an Articles tab...
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24421285
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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24423283
you need to wrap that in cotton wool with huge "becareful" signs everwhere !!
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24450553
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)




 
'=== 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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24453171
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.
0
 
LVL 12

Author Comment

by:Serge Fournier
ID: 24492611
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question