How to Parse - Parsing Flat File Data

darinsee · May 21st, 2004, 04:14 PM

Hello, and thanks in advance for considering this post...

What I have is a directory, called 'things', that has 200 files in it.

Each file contains tab separated data...

What I want to do is have a script that parses each file in the 'things' directory, and have the script add a record in a database that has data in two fields"

Field one: the piece of data in the file
Field two: the name of the file currently being parsed

Does this make sense? I can get data into databases okay, but loading, parsing, and getting the filename of each file in the 'things' directory is the piece I'm missing - Though I'm likely missing more :)

Thanks!!!

Darin

happygv · May 21st, 2004, 10:34 PM

You can check for help on "Scripting.FileSystemObject" to deal with File handling operations like reading from files.

You can do all that at short, something like...

For each FILENAME in THINGS_directory_FILES_COLLECTION
   Open_the_File
   WHILE NOT END_OF_FILE
       Read Line by Line
       Insert Date in to Database -- Data and FileName_in_Use
   END_OF_WHILE
   CLOSE_THE_FILE
NEXT

This will do the trick. I have just given the algorithm, you can check for the right syntax and steps to get there. You can always post here if you are struck up in the middle.

Let us know if any clarifications needed.
Hope that Helps.
Cheers!

-Vijay G

bmains · May 22nd, 2004, 08:23 AM

Hello,

The File object also has name and path parameters to reference the name and path of the file. In addition, you can access a file from the method FileSystemObject.OpenTextFile(...).

Brian

darinsee · May 22nd, 2004, 12:22 PM

Howdy Vijay & Brian - thanks for the tips - to be honest, I did have the pseudo code figured out, I'm just not sure of what object will help me to process these files... (I should have posted that with my first post) - So Scripting.FileSystem is what I need to use?

The part for me that I don't understand is the:

For each FILE('I don't know what the filenames are) in THINGS_directory_FILES_COLLECTION

What would this line of code actually look like? With that, I think I'd have my solution... And thank you quite a bit for considering this little problem!!!

Darin

bmains · May 22nd, 2004, 09:44 PM

Yeah,

I believe the code would look something like this:

set FSO = Server.CreateObject("Scripting.FileSystemObject")
set Folder = FSO.GetFolder("..path..")

for each File in Folder.Files
  'File.Path and File.Name reference the properties you need
  'for writing the DB entry
  set txt = fso.OpenTextFile(File.Path, 1, true)
  'read the lines from the text file
next

darinsee · May 24th, 2004, 04:11 PM

Howdy Y'all

Thanks an incredible lot for the help gents... below is the code that ended up doing the job perfectly (minus the db connection string):

Code:

<%
Dim objRS
Dim objFSO
Dim objFolder
Dim objText
Dim strDelim
Dim strLine
Dim arrForDb
Dim strLineCounter

strDelim = " "

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("Path_to_Folder_Holding_files_to_be_Parsed")

objRS.Open "DB_TABLE", strConnect, adOpenStatic, adLockOptimistic, adCmdTable

For Each File In objFolder.Files
    set objText = objFSO.OpenTextFile(File.Path, 1, true)
    strLineCounter = 0
    Do Until objText.AtEndOfStream
        strLine = objText.ReadLine
        If strLine <> "" Then
            arrForDb = Split(strLine,strDelim)
            If uBound(arrForDb) = 1 Then
                'Response.Write(arrForDb(0) & ", " & arrForDb(1) & "<BR>")
                objRS.addNew
                    objRS("field1")=arrForDb(0)
                    objRS("field2")=arrForDb(1)
                    objRS("field3")=File.Name
                objRS.Update
                strLineCounter = strLineCounter + 1
            Else
                'Response.Write(arrForDb(0) & ", " & "Nothing in the second position!<BR>")
                objRS.addNew
                    objRS("field1")=arrForDb(0)
                    objRS("field2")="sometextfordb"
                    objRS("field3")=File.Name
                objRS.Update
                strLineCounter = strLineCounter + 1
            End If
        End If
    Loop
    Response.Write(File.Name & " has been processed successfully.<BR>")
    Response.Write(strLineCounter & " users were added successfully.<BR><BR>")
    objText.Close
    Set objText=Nothing
Next

objRS.Close
Set objRS=Nothing
Set objFSO=Nothing
Set objFolder=Nothing
%>

Darin See