By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

MS Access menu import not same as TransferText

P: 3
Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!
Apr 27 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!
Check your specification file. There may be some problems with it.
Apr 27 '07 #2

P: 3
Check your specification file. There may be some problems with it.
I use the same specification file in TransferText as I do using Menu > import...
Apr 27 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I use the same specification file in TransferText as I do using Menu > import...
Check it anyway.
Apr 27 '07 #4

P: 3
Check it anyway.
I've been banging my head against the wall for the past 24 hrs because of this problem. This was seemingly a straightforward task. I've checked and double checked the import specification. As I mentioned before, I use the same one when I import manually as I do for the TransferText macro. Still not sure why the automation method doesn't work properly...
Apr 27 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Honestly not sure what is going on here. I've never had this problem. Usually though I use VBA to import text files. You can use code something like the following. It gives better control anyway.
Expand|Select|Wrap|Line Numbers
  1. Function ImportTextFile()
  2. Dim fhandle1 As Integer
  3. Dim fline As String
  4. Dim fPath As String
  5. Dim fld1 As Long
  6. Dim fld2 As Date
  7. Dim fld3 As String
  8. Dim strSQL As String
  9.  
  10.     fpath = "Full path of file.txt"
  11.     ' open the text file to be read
  12.     fhandle1 = FreeFile()
  13.     Open fpath For Input Access Read Lock Write As #fhandle1   
  14.  
  15.     Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
  16.  
  17.         Line Input #fhandle1, fline       
  18.  
  19.         fld1 = Left(fline, 5) 
  20.         fld2 = Mid(fline, 6, 10) 
  21.         fld3 = Left(fline, 16, 30) 
  22.  
  23.         strSQL = "INSERT INTO TableName (Field1, Field2, Field3) " & _
  24.            "VALUES(" & fld1 & ", #" & fld2 "#, '" & fld3 & "')"
  25.         DoCmd.RunSQL strSQL
  26.     Loop
  27.  
  28.     Close #fhandle1
  29.  
  30. End Function
  31.  
Mary
Apr 27 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.