Solved

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

Posted on 2009-05-05
21
1,683 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 11

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 11

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
 
LVL 11

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 11

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 11

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 11

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

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 11

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 11

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 11

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 11

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 11

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 11

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now