Import Text / .txt file to access table

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
my goal is to write code to import a statically named file to a tabel. i've tried usign the docmd.transfer text.

is this possible? I am getting an error with the txt header not matching my pre define field names.

here's my coding:

DoCmd.TransferText acImportDelim, , "RMT_Trns_UPLOAD", strFullTxtPath & "RMT_TXNS_CURRENT.txt", True

more on the error:
if i open the .txt the the first row has YYYYMM and then data below ...this needs to import to the first field of the table.....then there is a space in the txt file to the second header Ethnic....this needs to improt tot he second field of the table.

NOTE: the field names in my table match those int he .txt.

thnaks
tuk
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows
Be sure that not only do the field headers in your data file EXACTLY match the field names in your table, but that they also exactly match the field names in your Import Template ("RMT_Trns_UPLOAD").

Be sure that that the header row users the same delimiters as the actual data.

Be sure that there are no extra fields in your data.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
here is the error.

Error 2391.
Field YYYYMM_Ethnic_XXXXXXX to the end of the headers Doesn't exist in the table "RMT_Trns_UPLOAD".

How do i verify this:
"Be sure that that the header row users the same delimiters as the actual data."
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
the deliminators for the header row and actual data match.

that is, i manually imported the data to the table "RMT_trns_UPLOAD" using the Tab delimination and selected where the txt contains a header. this completed

BUT STILL NO GO USING THE CODE...hmmmmm. any other suggestions?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Error 2391.
Field YYYYMM_Ethnic_XXXXXXX to the end of the headers Doesn't exist in the table "RMT_Trns_UPLOAD".
This is telling you that the field names in the header and your import template do not match.

Maybe this field and all fields after do not have Tabs between them, so it recognizes it as one big long field instead of many different fields.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
understood on the one big file...

so i imported the file.....using the header names. then made a copy of it so it is the exactly header names from the .txt file. this is the one i am usign in the code above, "RMT_trns_UPLOAD".

how to i make sure the code states that the delimination is where the Tab is? would that solve it?

T
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Go through the manual Import Wizard like you are going to load it again. Then just before you click on the final step to import the file, click on the Advanced button and save the import specs with a new template name. Then use this new template in your code.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
mmmmm i've never used a template and am searching for the newly save one. where can i locate it?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,004
Office Version
  1. 365
Platform
  1. Windows
Where did the template "RMT_Trns_UPLOAD" come from in the first place?

All you have to do is set it like I described in the previous post, and make note of the name you are giving it. Then use that exact name you gave it in your VBA code.

If you do this through a macro instead of VBA (using the TransferText Action), it will list all the available templates that are set up for the database in a drop-down box under Specification Name).

You could always set-up the import using the Macro, then use the "Convert Macros to Visual Basic" functionality if you need it in VBA code.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
sorry...never even heard of a template.

all i've done is used vba to attempt to imports.

how can i call the template in VBA?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,208
Messages
5,857,918
Members
431,907
Latest member
RNN

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