Parsing a text file to input into a database.

Parsing a text file to input into a database.

Post by Kidd » Sat, 12 Jun 2004 01:47:10


I am tyring to parse the file at the bottom and input it into a database. There are approximately 785 files that are in the same format as the bottom shows. I have been trying to work on this but have not been able to open the file so that I can make an array out of the information on the right of the Colon. I want the information on the right of the colon until the end of the line to be inputted into the database. Any help with this would be appreciated.

Thanks,

############################################

'Create our filesystem object
Set objFS = CreateObject("Scripting.FileSystemObject")
'Get this folder
FolderPath="MYPATH"
Set objFolder = objFS.GetFolder(FolderPath)
Set objFiles = objFolder.Files

MsgBox objFiles.Count


For Each objFile in objFiles

Dim MyString, MyArray, Msg
MyString = objFS.OpenTextFile("" & objFile & "", 1)
MyArray = Split(MyString, ":", +1, 1)

Dim con, sql_insert, data_source

data_source = "DATABASE"
sql_insert = "insert into TABLE ('first_name', 'last_name', 'hotel_code', 'ticket_number', 'ticket_date', 'title', 'analyst', 'overall_service_level', 'overall_service_comments', 'listening_skills', 'listening_skills_comments', 'analyst_satisfaction', 'analyst_satisfaction_comments', 'additonal_comments', 'email_addy', 'date_time' ) values ('" & MyArray(0) & "','" & MyArray(1) & "','" & MyArray(2) & "','" & MyArray(3) & "','" & MyArray(4) & "','" & MyArray(5) & "','" & MyArray(6) & "','" & MyArray(7) & "','" & MyArray(8) & "','" & MyArray(9) & "','" & MyArray(10) & "','" & MyArray(11) & "','" & MyArray(12) & "','" & MyArray(13) & "','none','" & DateAdd("d", -150, Date) & "')"

Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source

con.Execute sql_insert

con.Close
Set con = Nothing

Next

########################################

Here is the txt file I am trying to parse.

########################################

First Name: NAME
Last Name: NAME
Hotel Code: CODE
Ticket Number: 1234567
Ticket Date: 6/10/04
Title: TITLE
Analyst ID: ANALYSTID
Overall Service Levels: 5
Overall Service Levels Comments:
Listening Skills Of the Analyst: 5
Listening Skills Of the Analyst Comments:
Your overall satisfaction with the individual support analyst: 5
Your overall satisfaction with the individual support analyst Comments:
Additional Comments: Service has always been great!
Users computer IP address is: XXX.XXX.XXX.XXX
 
 
 

Parsing a text file to input into a database.

Post by Mike » Sat, 12 Jun 2004 03:17:43

You would probably do well to split the file on the VbCrLf first, then split each element of the array on
the colon, assigning the values to the recordset by index like:

'**Complete Air Code Just for conceptual purposes**

dim RS
dim arRecord()
dim i
'Set a reference to your recordset then


ReDim arRecord(RS.Fields.Count - 1)

RS.AddNew
for i = 0 to RS.Fields.Count - 1

RS(i) = Split(Array(i), ":")

next i

RS.Update



files that are in the same format as the bottom shows. I have been trying to work on this but have not
been able to open the file so that I can make an array out of the information on the right of the Colon.
I want the information on the right of the colon until the end of the line to be inputted into the
database. Any help with this would be appreciated.
'ticket_date', 'title', 'analyst', 'overall_service_level', 'overall_service_comments',
'listening_skills', 'listening_skills_comments', 'analyst_satisfaction', 'analyst_satisfaction_comments',
'additonal_comments', 'email_addy', 'date_time' ) values ('" & MyArray(0) & "','" & MyArray(1) & "','" &
MyArray(2) & "','" & MyArray(3) & "','" & MyArray(4) & "','" & MyArray(5) & "','" & MyArray(6) & "','" &
MyArray(7) & "','" & MyArray(8) & "','" & MyArray(9) & "','" & MyArray(10) & "','" & MyArray(11) & "','"
& MyArray(12) & "','" & MyArray(13) & "','none','" & DateAdd("d", -150, Date) & "')"