VBA Import Text Files

[aeddipa]

I know there is a way to write VBA code that will import text files into an access database. I'm just not sure how to do this.

I've found this, but my data is not delimited. It is by spaces.

DoCmd.TransferText(acImportDelim,, "MyTable", "Path to text file")

What would I change the acImport to? Or would I leave it blank?

Thanks in advance!

 

[james_lankford]

in the text file, are the columns the same width ?
I don't mean each column, but each individual column
so each the data for column starts at the same place each row
here, age always starts at 17 spaces from the front
hometown always starts at 29 spaces from the front

name            age          hometown
Bill            27           Paris
Tommy           43           New York
Joe             14           Hong Kong
Maximilian      55           Mexico City

is that how your data is ?

 

[Joe4]

If you go to the Macro section, you select the TransferText command, and see all the arguments (sounds like you are looking at a Fixed Width text file). You can fill out all the arguments here to create a Macro to import a text file.

If you need it in VBA, there is an option to convert your Macros to VBA using the Macros/VBA menu.

Note that in importing text file through Macros or VBA, you will need to create and save the Import Specification, as that is one of the arguments. To do this, just manually go through the process of importing the text file (using the Wizard), but just before you click Finish, click the Advanced button, then click "SaveAs" and give the Import Specification a name (and note that name, so you can use it in the imports).

 

[aeddipa]

Yes, my data is fixed with. And starts in same spot each time for the multiple columns.

in the text file, are the columns the same width ?
I don't mean each column, but each individual column
so each the data for column starts at the same place each row
here, age always starts at 17 spaces from the front
hometown always starts at 29 spaces from the front

name            age          hometown
Bill            27           Paris
Tommy           43           New York
Joe             14           Hong Kong
Maximilian      55           Mexico City

is that how your data is ?

 

[aeddipa]

Joe, is this part of Access 2003 or 2007? I am working on 2003. I know how to use VBA for the most part, but haven't done a ImportData before.

Not sure how to do the Macro TransferText command.

If you go to the Macro section, you select the TransferText command, and see all the arguments (sounds like you are looking at a Fixed Width text file). You can fill out all the arguments here to create a Macro to import a text file.

If you need it in VBA, there is an option to convert your Macros to VBA using the Macros/VBA menu.

Note that in importing text file through Macros or VBA, you will need to create and save the Import Specification, as that is one of the arguments. To do this, just manually go through the process of importing the text file (using the Wizard), but just before you click Finish, click the Advanced button, then click "SaveAs" and give the Import Specification a name (and note that name, so you can use it in the imports).

 

[Joe4]

It is part of ALL access programs.

Just click on Macros (create new Macro).
Then from the Action box, select Transfer Text. All the arguments are pretty much self-explanatory, and youc an even press F1 for more help/information.

It is very easy (you do not need to know any VBA), give it a try!

 

[aeddipa]

Is there another way to do that. I don't have access to record the macro in Access and do that.

I feel like ive seen a 1 liner VBA code before that's easy, but can't recall.

Sub Test()
Docmd.TransferText(acImportFixed,,"Day1","Z:\FTPRoot\CFS\CashActuals\test\ACTUALS060.TXT")
End Sub

It's giving me an error. What should I do from here?

 

[boblarson]

1. Access doesn't have a macro recorder.

2. Are you absolutely sure you have a fixed width file and not just a tab delimited one?

3. What is the error it is giving you?

 

[Joe4]

Bob is right. Macros in Access do not work there same way as they do in Excel. There is no Macro Recorder. You need to build the Macro, but it is very easy.

If you look at the Objects on the left hand column, you see Tables, Queries, Forms, ..., Macros, and Modules. Just highlight Macros and click the New icon and it will open up the Macro pane. Then the instructions should follow what I gave you.

 

[Phildaburn]

When you import fixed width text, you have to use a specification file. According to the Access help file:

For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

When you have the import spec file created, change your code:

Docmd.TransferText(acImportFixed,"ImportSpecName","Day1","Z:\FTPRoot\CFS\CashActuals\test\ACTUALS060.TXT")

Hope this helps.
Phil...