Parsing a .txt file in ASP

[utahjzz98]

I have a .txt that has tab delimited values and I need to parse this data and add it to a table in an Access database. I can't think of an efficent way of doing this. I have tried using string manipulation functions to parse all the data out serpately, but that is not working the way I had hoped. Are there any easier options out there to take data from a text file and add it to a db?

Thanks

 

[ChrisHirst]

Assuming each line of the text file is going to be a database row, you could use the FSO to read a line at a time and then use Split(textline,vbTab) to put it into an array, insert the array elements to the db and loop around until EOF = True.




Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Genimuse]

If it's the same text file again and again (like, say, a log or something) and you have control of the server you can create a text DSN and read the file like any database (referring to the columns by number rather than name). Again with the loop that Chris mentioned, but you're looping through the recordset... might be faster than parsing using FSO, but I've never compared them.

 

[utahjzz98]

Ok, I used the Split Function on the text file and that's working great. It places all of the information in a 1 dimensional array. However, now I am having a bit of trouble trying to take that information from the array and save it to the db. Basically, there are 7 columns of information in the text file and the db is set up the exact same way, but the array is only 1 dimensional, so I can't figure out a way to take that array and save it to the db. Any suggestions?

 

[ChrisHirst]

whichever you prefer,

you can use;

rs.AddNew
rs.fields("column1") = array(0)
rs.fields("column2") = array(1)
.
.
rs.update
rs.close

or build a SQL Insert command

strSQL = "INSERT INTO tablename (columnlist) VALUES ( '" & array(0) & "','" & array(1) .... & "');"
strConn.Execute(strSQL)

obviously create and open the dbConnection and RecordSet first and the insert command above assumes that the array values are all strings.



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[utahjzz98]

Please forgive me if this question seems stupid, but I am fairly new to programming. Wouldn't I need to create some sort of a loop that would say after every 7 elements in the array, begin a new row in the db?? For example, my array has around 500 elements and I wouldn't be able to do a rs.fields for each of these elements, because it wouldn't map out with the db correctly.

 

[ChrisHirst]

Of course, you will need to adapt it to however the text file is organised.
My code assumed each line of the text file would map into db rows.

off the top of my head you will need to have a loop using 7 as a step count

for i = 0 to ubound(array) step 7

rs.AddNew
rs.fields("column1") = array(0 + i)
rs.fields("column2") = array(1 + i)
.
.
rs.fields("column7") = array(6 + i)

rs.update
next

rs.close

I haven't tested this by the way!





Chris.

Indifference will be the downfall of mankind, but who cares?

 

[utahjzz98]

Ok, I tried the code above and it's only saving the 7th column to the database now. I guessing it's due to the step 7 I added to the loop. I tried to switch the step counter and nested loops but so far I haven't had any luck in getting it to work successfully. I appeciate your input thus far, I have learned a lot.

Thanks,
Corey

 

[ChrisHirst]

Post the code you have from opening through to closing the text file. If it is only inserting every 7th value to the db possibly your loop nesting is in the wrong order.




Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Tarwn]

If I have read the flow of conversation correctly, then I think the issue is that you have split the file on Tabs into a single 1-dimensional array.
It seem odd to me that a data file would be set up without rows...?
my guess is that you have 7 tab-delimited columns per row, and the rows end with a carriage return. If this is the case then you need to first split on the carriage return to get an array of rows, then further split on the Tab for each row to get the values for that row.

So basically something like:
1) Load File
2) read File into string myString
3) Split file into RowArray on vbCrLf to give you an array of rows
4) For Each row in that array
5) Split that row on vbTab to give you an array that has the column values
6) Add the new record to the db using the column array
7) next (I started writing goto, gah)

in code that would look something like:

Dim rs, fso, fil, contents
'
' pretend there is code that reads the text file and opens the recordset here
'

Dim rowArray
Dim i, arow

'split the file into rows
rowArray = Split(contents,vbCrlf)


'for each row, split it into it's columns
For i = 0 to UBound(myarray)
   arow = Split(rowArray (i),vbTab)

   'add it to the database as a new row
   rs.Addnew
   rs("field0") = arow(0)
   ...etc
Next

If I am incorrect in my assumption of the layout of the data, please post an example so we can see what we are working with.


_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[utahjzz98]

Here is the function I am using to parse the array

<%
Public Function Parse()
	Dim strAryWords
	Dim strValue
        Dim rs

        Set rs = CreateObject(ADODB.Recordset)
        strValue = vbtab
	strValue = strValue & Request.Form("txtBlob")
	strAryWords = Split(strValue, vbtab)
	
	Dim i
        For i = 0 to Ubound(strAryWords) step 7
                rs.Addnew
                rs("field0") = strArtWords(0)
        Next
End Function
%>

 

[Tarwn]

It still seems odd to me that you would have a one line data file...?
If you have a bunch of rows of data, and each row has the 7 tab delimited values, then you will not got the right solution by simply splitting on the Tab because the last entry will be attached to the fist entry on the next line still. Splitting on the carriage return would give you each value set seperated as well as removing the carryover problem.

If your data is in one really long row, then the problem with your above code is that the first value you are inserting is going to be null because you are adding an initial Tab to the string. Example:
say txtBlob is equal to: myword<tab>my other word<tab>something else

If you add a Tab to the front of this and then split it, your string would look like:
yourString = vbTab & "myword" & vbTab & "my other word" & vbTab & "something else"
Which will then output the following array when split on vbTab:
yourArray(0) = ""
yourArray(1) = "myword"
yourArray(2) = "my other word"
yourArray(3) = "something else"

Since you have offset the first entry by adding that initial tab, your loop is going to be offset as well. So if the data is actually evenly divisable by 7 your array will lose the last 6 entries (since you added the extraneous one to the front). Plus you will be writing the data to the wrong fields, etc. If you want to leave the Tab in then start your loop at 1 instead of 0.

I still think we should see an examnple of this data. That would help in assisting you to solve this. I'm still thinking that the multiple line thing has been overlooked simply because putting all the data on one long tb delimited line doesn't make a lot of sense to me (though I guess it could be possible).

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[utahjzz98]

The text file has 7 columns of data and multiple rows. Here is an example of how the text file is formatted.

Hadrial 65 Bard M 03/20/04 Temple O
Tardar 65 Bard M 03/20/04 Temple L

 

[ChrisHirst]

in that case this should work

dim 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(Server.MapPath(TextFileName))

Do While Not objTextFile.AtEndOfStream

	line = objTextFile.ReadLine
        array = Split(line,vbTab)
'array should only have 7 elements
for i = 0 to ubound(array)

rs.AddNew
rs.fields("column1") = array(0 + i)
rs.fields("column2") = array(1 + i)
.add in col 3 to 6 
.
rs.fields("column7") = array(6 + i)

rs.update
next

loop

rs.close

[code]
as in my first post it should read in the text file a line at a time and write it to the db.



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Tarwn]

Yes, either Chris' method of reading one line at a time and splitting or my method of splitting twice is necessary. Splitting only on Tab will result in the errors i pointed above.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[ChrisHirst]

whoops! that's what happens when you copy and paste code!

this is wrong!
*********************************
rs.AddNew
rs.fields("column1") = array(0 + i)
rs.fields("column2") = array(1 + i)
.add in col 3 to 6 
.
rs.fields("column7") = array(6 + i)

**********************
should be

rs.AddNew
rs.fields("column1") = array(i)
rs.fields("column2") = array(i)
.add in col 3 to 6 
.
rs.fields("column7") = array(i)

Chris.

Indifference will be the downfall of mankind, but who cares?

 

[ChrisHirst]

whoops! that's what happens when you copy and paste code!
this is wrong!

for i = 0 to ubound(array)

rs.AddNew
rs.fields("column1") = array(0 + i)
rs.fields("column2") = array(1 + i)
.add in col 3 to 6 
.
rs.fields("column7") = array(6 + i)

rs.update
next

should be

rs.AddNew
rs.fields("column1") = array(0)
rs.fields("column2") = array(1)
.add in col 3 to 6 
.
rs.fields("column7") = array(6)

rs.update

no need for the loop any more

or if you leave the loop in this should work

for i = 0 to ubound(array)
rs.AddNew
rs.fields.item(i) = array(i)

rs.update
next

Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Tarwn]

Chris: Heh, if I could count how many times that happened to me Good show on adding the corrections,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/