How to open .dat file before importing

[CptTom]

I receive files from the field with the exact same names but could be 4 different type of data. The first 3 characters of which will tell me the type of data contained and will then dictate the SpecName to use. Currently, we import all such files under all spec names and then filter out the garbage. There must be a simpler way. The logic would go something like:

Dim strType as String
Examine first 3 characters of abc.dat
Case Select
Case strType = A
import specName A
Case strType = B
import specName B
etc.

Larry

 

[JerichoJ]

Try opening the file directly for reading with some code like:

Dim theFile as Long
Dim strLine as String
Dim strType as String

'Get an open file handle
theFile = FreeFile

'Open the file read only
Open "C:\Input.dat" For Binary Access Read As theFile

'Read the first line into a variable
Line Input #theFile, strLine

'Close the file
Close theFile

strType = Left$(strLine,3)
'Your code can handle it from here! ;-)

Jericho

 

[CptTom]

Thanks! It works, HOWEVER, I was incorrect as to the data files. Whoever wrote the programs that runs on the machines in the fiels did some strange stuff. I need to evaluate the first 3 characters of each line to determine what type of report it is and then determine what size the fixed widths are.

What if I sucked the entire file into a temp table, using only one field of 100 width, then evaluated each line, transfering it into the correct file widths and a separate table and deleting the temp table?

Larry

 

[JerichoJ]

Larry,

Yes, you are on the right track. If you have to evaluate the first 3 characters of each line, I would do exactly as you suggested. Bring the entire file into a temp table, evaluate each line and move it to the appropriate place, then empty the temp table.

You can bypass the Access Import functions with this adaptation to the code in my previous post above:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim mssql as String

Set db = Currentdb()
theFile = FreeFile
Open "C:\Myfile.dat" For Binary Access Read As theFile
mssql = "SELECT * FROM tblTempImport;"
Set rst = db.OpenRecordset(mssql, dbOpenDynaset)
Do While Not VBA.EOF(theFile)
Line Input #theFile, strLine
rst.AddNew
rst("AField&quot = strLine
rst.Update
Loop
rst.close
Close theFile
db.close
Set rst = Nothing
Set db = Nothing

This will quickly read every line from the file and insert it into a Temp table. I would recommend that the Temp table has 2 fields. The first, Primary Key, as an Autonumber, and the second to hold the data. This way whenever you look at the table manually, you will be assured that the lines are in the same order they are in the original input file.

Jericho