Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to open .dat file before importing

How to open .dat file before importing

How to open .dat file before importing

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


RE: How to open .dat file before importing

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!


RE: How to open .dat file before importing

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?


RE: How to open .dat file before importing


  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("AField") = strLine
Close theFile
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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close