I have a flat text file that is an exported data dump. It unfortunately has
5 tables inside of it and no way to modify the how the data is given to me.
Each different table starts with an "!" and the table name. Comment lines
are ";" and data is delimited with ":" For example data would look like this:

![ExportedTime]01/01/2006 22:22
;
; Terminal export data
;
!General
Revision = 1
CurrentStatus = 1
ExpectNewData = 15
;
!Customer:
ID:JamesClark:StreetAddy:City:State:Zip:Phone1:Pho ne2:
;
!Transaction:
ID:Customerate:TransValue:
;
!Employee:
ID:Name:Login:Logoff:Terminal:
;
; END

I would need to get each section into its own table ie. General into General
and Customer into Customer. Note the General section has a different export
format as well, but would go into one data row. The length of each
section/table would vary, and the old table would have to be cleared before
each import ~ every 15 minutes. I don't need the comment lines at all. Is
there a simple way to do this?

Thanks...

Simple . . . maybe not but it is possible. If it were me I would do it all
in an ActiveX script using the File System Object to read each line of the
file one at a time and called a stored proc to insert each row. You would
have to check inside your look for the character that denotes a new table
and change your stored proc call occordinly. It would probably take a
couple hours to code and test if you're familiar with vbscript, the FSO and
ADO (command objects, connection objects, etc.) Below is an example of
using ADO to called procs and using the File system object:

' 246 (Begin Loop)
Option Explicit

Function Main()

Function ShouldILoop

File_Name = "none"

set oConn=CreateObject("ADODB.Connection")
set oCmd = CreateObject("ADODB.Command")
oConn.Open="Provider=SQLOLEDB;Trusted_Connection=y es; Data Source=nshddb01;
Initial Catalog=ets"

'to avoid updating the status on the 21st file I'll check the counter
before I call the procedure
if DTSGlobalVariables("gv_LoopCounter").Value <= 20 then
oCmd.CommandType = 4
oCmd.CommandText = "usp_BeginFileUpload"
Set oCmd.ActiveConnection = oConn
Set objParam = CreateObject("ADODB.Parameter")
Set objParam = oCmd.CreateParameter("filename", 200, &H0002,100,"abc")
oCmd.Parameters.Append objParam
Set objParam1 = CreateObject("ADODB.Parameter")
Set objParam1 = oCmd.CreateParameter("fileuploadid", 3, &H0002, , -99)
oCmd.Parameters.Append objParam1
oCmd.Execute
File_Name = oCmd.Parameters("filename").Value
File_ID = oCmd.Parameters("fileuploadid").Value
set oCmd = nothing
else
File_Name = "none"
File_ID = -99
end if

DTSGlobalVariables("gv_FileFullName").Value =
DTSGlobalVariables("gv_FileLocation").Value & File_Name
DTSGlobalVariables("gv_FileName").Value = File_Name
DTSGlobalVariables("gv_FileID").Value = File_ID

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation" ).Value)

counter = fold.files.count

if File_Name <> "none" and counter >= 1 and
DTSGlobalVariables("gv_LoopCounter").Value <= 20 then

'keep track of how many times we loop
DTSGlobalVariables("gv_LoopCounter").Value =
DTSGlobalVariables("gv_LoopCounter").Value + 1
ShouldILoop = CBool(True)

elseif File_Name <> "none" and counter < 1 then

'Here we have a file in the database table but no file in the directory
'Note that since we will exit the processing loop, if there are more
records in the fileupload
'table in the awaiting status they will not process until the next time
this package is run
set oCmd1 = CreateObject("ADODB.Command")
oCmd1.CommandType = 4
oCmd1.CommandText = "usp_UpdateFileUpload"
Set oCmd1.ActiveConnection = oConn
Set objParam2 = CreateObject("ADODB.Parameter")
Set objParam2 = oCmd1.CreateParameter("fileid", 3, &H0001,,File_ID)
oCmd1.Parameters.Append objParam2
Set objParam3 = CreateObject("ADODB.Parameter")
Set objParam3 = oCmd1.CreateParameter("status", 3, &H0001, , -100)
oCmd1.Parameters.Append objParam3
oCmd1.Execute
set oCmd1 = nothing

ShouldILoop = CBool(False)
else
ShouldILoop = CBool(False)
End if

End Function



"Greg V" <GregV (AT) discussions (DOT) microsoft.com> wrote

Walter, thanks... I think it makes sense but I'm not upto speed in ActiveX
scripting. Is there a good reference manual out there. It's been years
since I've coded.

"Walter Mallon" wrote:

This is for the vbscript language reference:
http://msdn.microsoft.com/library/de...0296ced968.asp

Also, here is a copy of a vbscript that I run to check for the proper amount
of columns (by adding up the delimeters in each row) that I use before I
import a particular text file. It shows how to open a file and read each
row in vbscript. Along with the example from before this should be a good
start.

dim fso, f1, TextStream, File, folder

'declare this constant for code readability.
Const OpenFileForReading = 1
Const adStateOpen = &H00000001

'create necessary file system objects
set fso = CreateObject("Scripting.FileSystemObject")
set File = fso.GetFile("c:\parse\original exmplar_full_email_list.txt")
set GoodFile = fso.CreateTextFile("c:\parse\text_good.txt")
set BadFile = fso.CreateTextFile("c:\parse\text_bad.txt")

Set TextStream = File.OpenAsTextStream(OpenFileForReading)

ctr = 0
Do While Not TextStream.AtEndOfStream
S = TextStream.ReadLine
hold = s

i = 0
n = 0
n = instr(1,s,"|",1)
s = mid(s,n+1,len(s))
if N > 0 then
i = 1
end if

do while n <> 0

n = instr(1,s,"|",1)
s = mid(s,n+1,len(s))
if N > 0 then
i = i + 1
end if

loop

if i <> 10 then
BadFile.WriteLine hold
else GoodFile.WriteLine
hold
end if

'msgbox(i)

ctr = ctr + 1
if ctr = 200000 then
msgbox("200000 done")
elseif ctr = 300000 then
msgbox("300000 done")
elseif ctr = 500000 then
msgbox("500000 done")
end if


Loop
TextStream.Close
msgbox("Process Complete")


"Greg V" <GregV (AT) discussions (DOT) microsoft.com> wrote

Here's another vbscript. This one parses an IIS log file and imports it
into the database. I actually used the Windows AT scheduler for this one.
Hope this helps.

Walter


'************************************************* **************************************************
'Walter Mallon 2/18/2003
'This script will take an IIS log file, parse it out, and write it to a
database table.
'The database name is IsOperations.
'The database uer id is 'iislog' and the Password is also *******
'The development database server is sql3.
'The production database server is sql1.
'The code uses the stored procedure 'usp_add_log_entry' to insert into
the table.
'
'I will look into the current log file directory. I will import each
file whose file date is less
'then the current system date. I don't want to import the current file
to avoid duplicate entries
'into the database.
'
'To avoid reading a file twice, I will first get a distinct list of
previous file names that have
'been imorted and check each file name against this list. If the file
has already been imported,
'I will not re-import it.
'
'The code opens a text strean on the IIS log file. It then reads each
line and inserts the contents
'of that line into the database one line at a time.
'************************************************* **************************************************


'I should probably make the drive and filename variables but I keep
getting errors when I
'do that so I'm not going to waste any more time.

'file stuff
dim fso, f1, TextStream, File, folder

'declare this constant for code readability.
Const OpenFileForReading = 1
Const adStateOpen = &H00000001

'column variables to use to pass to the stored procedure.
dim tx_date, tx_time, tx_user, tx_station, tx_ip, tx_radius, i,
bol_found_file
dim oCmd, oRst, oConn

set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB;Data Source=wsql1;User
ID=iislog;Password=********;"

'Get a distinct list of all the previous file names that have been
imported.
set oRst = CreateObject("ADODB.Recordset")
'oConn.usp_GetFileNames oRst

'create necessary file system objects
set fso = CreateObject("Scripting.FileSystemObject")
set folder = fso.GetFolder("\\wdc2kdns\c$\IAS Logs\")
set files = folder.files


'only perform the logic if log files are found.
if files.Count <> 0 then
for each file in files

'I will use bol_found_files to determine if the file has already
been imported
bol_found_file = false


'This is somewhat inefficient, but I'm going to refresh the
recordset for each
'file in the directory and loop through to see if that file has
already been imported.
oConn.usp_GetFileNames oRst

'make sure the recordset is open and populated to aviod errors
if oRst.state = adStateOpen then
if not (oRst.EOF and oRst.BOF) then
do while not oRst.EOF
if file.name = oRSt("tx_file") then
bol_found_file = true 'this will tell the code below not to
import this file
end if
oRst.MoveNext
loop
end if
oRst.close
end if

'Don't import the current, active file
'don't import the file if found in the recordset because it's
already been imported.
if file.DateLastModified < Date and bol_found_file = false then
sub_log_file(file)
end if
next
end if

'end of program logic - subs follow





sub sub_log_file(file)
Set TextStream = File.OpenAsTextStream(OpenFileForReading)

' set oConn = CreateObject("ADODB.Connection")
' oConn.Open "Provider=SQLOLEDB;Data Source=wdcntsql1;User
ID=iislog;Password=iislog;"


Do While Not TextStream.AtEndOfStream
S = TextStream.ReadLine

'trim the quotes out
s = replace(s,"""","")

i = 1

'I don't need any columns past column 20 so I use this figure
for the loop.
do while i <= 20

'find the first comma
n = instr(1,s,",",1)

'get the value for this column
hold = mid(s,1,n-1)



'********************************************
'I know the column positions of the fields
'I want. I pull the first column from the
'string each loop so I use the counter variable
'to know which columns I need. The pulled
'column is then cut from the string. So the
'for the next loop, the first column will
'then be the column that was in position 2
'on the previous iteration.
'********************************************


if i = 3 then
tx_date = hold
'msgbox(tx_date)
if tx_date = "" then
tx_date = " "
end if
elseif i = 4 then
tx_time = hold
if tx_time = "" then
tx_time = " "
end if
'msgbox(tx_time)
elseif i = 7 then
tx_user = hold
if tx_user = "" then
tx_user = " "
end if
'msgbox(tx_user)
elseif i = 9 then
tx_station = hold
if tx_station = "" then
tx_station = " "
end if
'msgbox(tx_station)
elseif i = 11 then
tx_ip = hold
if tx_ip = "" then
tx_ip = " "
end if
'msgbox(tx_ip)
elseif i = 17 then
tx_radius= hold
if tx_radius = "" then
tx_radius = " "
end if
'msgbox(tx_radius)
end if

'chop the part I just took off the front of the string
s = mid(s,n+1,len(s))
i = i + 1

loop

'concatinate date and time
tx_date = tx_date & " " & tx_time

'now lets go ahead and write this to the table
oConn.usp_add_log_entry file.name, tx_date, tx_user,
tx_station, tx_ip, tx_radius
Loop
TextStream.Close
end sub

'***** END OF FILE *****"Greg V" <GregV (AT) discussions (DOT) microsoft.com> wrote in
message news:28FBA6DE-595E-4AEE-91B9-751F54207751 (AT) microsoft (DOT) com...

I think we're getting closer. Maybe I take this file and do a few passes
over it each time. The file downloaded will always have the same name. The
only difference would be its date/time stamp and the first row having the
![exportedtime] field.

How about skipping to a section like "!Customer:" and reading in the rows
that follow it, then stopping when it sees the ";" ?



"Walter Mallon" wrote: