Question How to get a large .dat file into Access

[Kirkuleese]

Hey guys, this is my first post and any help would be very much appreciated.

I have a large .dat file which is run through an Access macro to produce reports. After a recent system change at work the format of the .dat has changed and now includes an additional bit of data which disrupts the macro.

I tried changing the extension of the file from dat to mdb to see if I could remove the additional column in access. I also tried changing it to a csv file as well but the file has a few hundred thousand lines and the csv file cuts most of it out.

Are there any other ways I can open this file in Access to remove this additional column of data?

 

[Pat Hartman]

Welcome Aboard Some file extensions have specific formats and others are custom. XLS, DOC, PST, MDB are standard file formats. TXT files are text but their layout is custom. A program determines the record length and field formats. Same with CSV files except those have a little more structure since fields are supposed to be separated with comas. DAT files are also custom. They are generally plain text but they don't have to be. You can't just change a file extension from DAT to CSV and expect to be able to read the file in CSV format. That is like changing the dust cover of a book from French to English and expecting English speakers to read the French text.

You will need the makers of the DAT file to tell you how records are formatted. You will then have to modify the definition used by the Access application.

 

[Kirkuleese]

Hi Pat, thanks for the explanation. My next question would be could I use the TransferText function in VBA in Access?

 

[Pat Hartman]

You will need to rename the .dat extension to .txt since in its infinite wisdom, MS declared .dat as a "dangerous" file type some years ago (personally causing me a crisis with a client app that was sold to the public). As long as the .dat is actually a plain text file with a single fixed or delimited record format just renaming it will allow you to define an import spec that you can save and then reuse in code. Start by importing the file ONCE using the import wizard. You will probably have to use the "advanced" button to call up certain features that you don't see in the standard dialog. Then you will need to name and save the spec. Once that's done, you can reference the import spec in the TransferText Method to automate the import.