Update darabase from text file

[vc1881]

Hello,
Hello,

I have a text file with data for certain fileds that are in certain position, for example data for field1 is in yhe firts 5 spaces in each line, data for field2 is
from the 7th space up to the 10th space, field3 from the 11th space to the 19 space etc.....

I only know how to open and read the entire text into a form control, how do you read line by line and extrat the data using the "inset" command to populate the ACCESS table.

Thanks in advance

Victor

 

[dhookom]

Have you tried the import wizard? Your file is a fixed width text file.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

This can be done programmatically with the File System & Text Stream objects. The idea is to read a line of text data into a string variable, then parse out the string (using Mid(), Left(), Right(), etc.) and insert the data where it is needed with an SQL INSERT statement. Example:

Dim fts As TextStream
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File

Set fso = CreateObject("Scripting.FileSystemObject")
Set fil = fso.GetFile(YourFileName)
Set fts = fil.OpenAsTextStream(ForReading)
strYourString = hts.ReadLine
YopurInsertVariable = Mid(strYourSting, 8, 8)

...Perform SQL here


hts.Close
Set fts = Nothing
Set fso = Nothing
Set fil = Nothing

If more than one line, loop thru the code until out of data.
Don't forget to set a reference to the MS Scripting Runtime library.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[vc1881]

Hello,

This problem was resolved.

Thanks for the code.

Victor