Parsing data from a text file to a database?????

[kanejone]

I have a load of text files that contains one record per file . Each field data is separated by as follows !!:number:Field data!!. I need to be able to read each line (record), set a variable to each field, and then insert a new reocrd into an access database using the variables. How can I set my variables to their corresponding values from the text by searhing for !!:number:Field data!! ?? I appreciate any help! I know that it has to do with parsing the text files, but I just amn't sure how to go about doing it!!!

Thanks a million,
JK

[Edited by kanejone on 11-29-2000 at 05:10 AM]

[Gaffer]

This is a rough stab in the dark, as I haven't thought about the actual parsing of the data yet, but would it not be worth trying the import text wizard in Access? You can set the field delimiters (to !), and if multiple delimiters occur (i.e. !!), this can be take into consideration. The only problem I see with this is that I cannot see any sign of a record separator (e.g. an ENTER key) - it looks like it could be the colon...

Post back if it doesn't work - we'll have to parse it then (nasty...)

[kanejone]

Thanks a million for the reply. The problem that I have is that there are about 10,000 of these files that are all stored in different folders and more files come in on a daily basis. So what I need to do is be able to update the database as well as developing a user web interface to the database.
Your help is greatly appreciated

[jbart]

If you are using VB6, you can use the SPLIT function to separate your fields. It will allow you to use a delimiter of "!!" to place each field into a separate element in an array.

Example:

Private Sub Command1_Click()
    Dim strTest As String
    Dim strResult As String
    Dim strArray() As String
    Dim x As Integer
    
    strTest = "this!!is!!a!!test!!string."
    strArray = Split(strTest, "!!")
    
    For x = 0 To UBound(strArray)
        strResult = strResult & strArray(x) & vbCrLf
    Next x
    
    MsgBox strResult
End Sub

[ender_pete]

ive done the same thing your trying to do
a few questions for you then i can give you the best advice i have.

do all of these text files fall in the same format?
if not do all of the text files have the same delimiters?

if you want you can just email me at work
[email protected]
or at home
[email protected]

and ill give you the best advice i have.

[kanejone]

Hi ender_pete
Thanks. In response to your questions, all the files follow the exact same format with the same deliminaters. They are generated by a system which sends them to me in this exact format.
Thanks again
JK

[ender_pete]

jbart hit exactly what i was gonna say.
like his code shows use the split function on a string to split the seperate fields up.

but first you have to load the string.
if your files are over 2 billion characters you won't be able to do this beacause a string only holds about
2 billion characters, any more and you'll get an overflow error. here is the way i access files, i use the FileSystemObject and the Textstream object.
first create the FileSystemObject then the textstream object
fso opens the files as text stream so it would go like this

Dim fsoDataFile As New FileSystemObject
Dim objText As TextStream
Dim strData as String

Set objText = fsoDataFile.OpenTextFile("C:\temp\whatever.txt", ForReading)

strData = objText.ReadAll

then all you have to do is use the split function load it into an array, then the fields will be in order and its just a matter of looping through the array and populating the database.. now in the code above the file name where i put C:\temp\whatever.txt does not have to be static like that you can use a string with the filename in it that way you can just make this a function an pass the filename to it. then you just have to be able to search the hard drive to find the files..
hope this helps.

[kanejone]

Thanks for the help!! I'll give it a go.
Kind Regards
JK

[ender_pete]

no problem
if programmers don't help each other, who will?
no one else is crazy enough....

take care