VBA Import Text Files

aeddipa

Active Member
Joined
Jun 1, 2009
Messages
337
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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
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
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 1, 2009
Messages
337
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
Code:
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

Active Member
Joined
Jun 1, 2009
Messages
337

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 1, 2009
Messages
337

ADVERTISEMENT

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.

Code:
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2011
Messages
146
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...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top