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

Automated Import TXT file into Access

P: 1
I try to automated import TXT using macro - but i would like the user to be able to select where the TXT file is or brouse.
Oct 20 '06 #1
Share this Question
Share on Google+
15 Replies


Expert 5K+
P: 8,434
I try to automated import TXT using macro - but i would like the user to be able to select where the TXT file is or brouse.
I did something quite similar in an Access 2003 database, but I don't recall exactly how. It's Saturday afternoon here now, I can check on Monday morning and let you know, if you like.
Oct 21 '06 #2

P: 44
Hi,

Use TransferText action in Macro. Good luck
Oct 23 '06 #3

P: 2
Hi,

Use TransferText action in Macro. Good luck
dr PLZ PLZ plz help me to get data from txt file to my access table.
Feb 4 '07 #4

ADezii
Expert 5K+
P: 8,619
I try to automated import TXT using macro - but i would like the user to be able to select where the TXT file is or brouse.
One very neat solution is the use the Microsoft Office FileDialog in conjunction with the TransferText Method. This will give you the complete flexibility that you require.
NOTE: You must first set a Reference to the Microsoft Office XX.0 Object Library
Expand|Select|Wrap|Line Numbers
  1. Dim fDialog As Office.FileDialog
  2. Dim varFile As Variant
  3.  
  4. 'Set up the File Dialog
  5. Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  6. With fDialog
  7.   .AllowMultiSelect = False
  8.   .Title = "Select a Text File to Import"
  9.   .InitialFileName = "C:\*.txt"
  10.  
  11.   'Clear out any Filters, then Add you own (1)
  12.   .Filters.Add "Text Files", "*.TXT"
  13.  
  14.   'Show the Dialog. If the Show Method returns True, the User picked
  15.   'at least 1 File, otherwise the User clicked Cancel
  16.   If .Show Then
  17.     For Each varFile In .SelectedItems
  18.       'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
  19.       DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
  20.     Next
  21.   Else      'Nothing selected in File Dialog
  22.   End If
  23. End With
Feb 4 '07 #5

P: 1
The import code works well excess i get a notify window saying 'You are about to append 1 row(s)' for each record. Has snyone got a suggestion about how to turn this off?
Cheers
Jan 22 '08 #6

Expert 100+
P: 374
in the code type the following near the top

Expand|Select|Wrap|Line Numbers
  1.  
  2.       docmd.setwarnings False
  3.  
Jan 22 '08 #7

ADezii
Expert 5K+
P: 8,619
One very neat solution is the use the Microsoft Office FileDialog in conjunction with the TransferText Method. This will give you the complete flexibility that you require.
NOTE: You must first set a Reference to the Microsoft Office XX.0 Object Library
Expand|Select|Wrap|Line Numbers
  1. Dim fDialog As Office.FileDialog
  2. Dim varFile As Variant
  3.  
  4. 'Set up the File Dialog
  5. Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  6. With fDialog
  7.   .AllowMultiSelect = False
  8.   .Title = "Select a Text File to Import"
  9.   .InitialFileName = "C:\*.txt"
  10.  
  11.   'Clear out any Filters, then Add you own (1)
  12.   .Filters.Add "Text Files", "*.TXT"
  13.  
  14.   'Show the Dialog. If the Show Method returns True, the User picked
  15.   'at least 1 File, otherwise the User clicked Cancel
  16.   If .Show Then
  17.     For Each varFile In .SelectedItems
  18.       'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
  19.       DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
  20.     Next
  21.   Else      'Nothing selected in File Dialog
  22.   End If
  23. End With
Reference Line Numbers 4 and 27
Expand|Select|Wrap|Line Numbers
  1. Dim fDialog As Office.FileDialog
  2. Dim varFile As Variant
  3.  
  4. DoCmd.SetWarnings False
  5.  
  6. 'Set up the File Dialog
  7. Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  8. With fDialog
  9.   .AllowMultiSelect = False
  10.   .Title = "Select a Text File to Import"
  11.   .InitialFileName = "C:\*.txt"
  12.  
  13.   'Clear out any Filters, then Add you own (1)
  14.   .Filters.Add "Text Files", "*.TXT"
  15.  
  16.   'Show the Dialog. If the Show Method returns True, the User picked
  17.   'at least 1 File, otherwise the User clicked Cancel
  18.   If .Show Then
  19.     For Each varFile In .SelectedItems
  20.       'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
  21.       DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
  22.     Next
  23.   Else      'Nothing selected in File Dialog
  24.   End If
  25. End With
  26.  
  27. DoCmd.SetWarnings True
Jan 22 '08 #8

P: 3
I would like to do the same thing, but also have limited skills. Is this code for a sub-routine that I can call from a form or a macro?

Thanks,

Frank
Jan 25 '08 #9

ADezii
Expert 5K+
P: 8,619
I would like to do the same thing, but also have limited skills. Is this code for a sub-routine that I can call from a form or a macro?

Thanks,

Frank
You can call it from the Click() Event of a Command Button, Function Procedure, Sub-Routine Procedure, etc.
Jan 25 '08 #10

P: 3
You can call it from the Click() Event of a Command Button, Function Procedure, Sub-Routine Procedure, etc.
Sorry to be so ignorant, but I've pasted this in to my application as a module.

Is this where it goes?

Thanks,
Jan 28 '08 #11

ADezii
Expert 5K+
P: 8,619
Sorry to be so ignorant, but I've pasted this in to my application as a module.

Is this where it goes?

Thanks,
The easiest solution would be to create a Command Button with the Wizard then copy and Paste this code in the Button's Click() Event.
Jan 28 '08 #12

P: 3
The easiest solution would be to create a Command Button with the Wizard then copy and Paste this code in the Button's Click() Event.

Thanks, I appreciate your help!
Jan 28 '08 #13

ADezii
Expert 5K+
P: 8,619
Thanks, I appreciate your help!
You are quite welcome.
Jan 29 '08 #14

P: 1
Hello,

I just started to build my first serious Access Database and need your help.

I use the above method and it is working, but I struggle having a really flexible import and also one for more than one file.

I get the ImportText function only working in a way that I can import a specific file named in the TransferText MEthod as File Name, not the selected one.

Finally I would like to know how to import 12 documents, called VLCV1, VLCV2...


This is what I wanna do.


I have 12 documents with 4 columns that give me
Date, Time, Pressure and Temperature

I need to read these documents in and connect them together to get one document that has

Date, Time, Pressure 1, pressure 2... temp1, temp2

The issue is that those 12 documents exist for different days and I want to keep them separated from each other, by at least selecting the right folder and then import the 12 according files, don't need to have all files always in Access.

I have some more questions, but getting the files into Access with the necessary flexibility is my first target.

Appreciate all help and if further explaination is required, please let me know
Nov 9 '08 #15

ADezii
Expert 5K+
P: 8,619
To process Multiple Text Documents, change Line #9 to:
.
Expand|Select|Wrap|Line Numbers
  1. AllowMultiSelect = True
P.S. - You will also need a Method to uniquely Name each Table.
Nov 10 '08 #16

Post your reply

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