Automated Import TXT file into Access

[guffy2006]

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.

[Killer42]

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.

[Zeeshan7]

Hi,

Use TransferText action in Macro. Good luck

[honuman]

Hi,

Use TransferText action in Macro. Good luck

dr PLZ PLZ plz help me to get data from txt file to my access table.

[ADezii]

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

Dim fDialog As Office.FileDialog
Dim varFile As Variant
 
'Set up the File Dialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
  .AllowMultiSelect = False
  .Title = "Select a Text File to Import"
  .InitialFileName = "C:\*.txt"
 
  'Clear out any Filters, then Add you own (1)
  .Filters.Add "Text Files", "*.TXT"
 
  'Show the Dialog. If the Show Method returns True, the User picked
  'at least 1 File, otherwise the User clicked Cancel
  If .Show Then
    For Each varFile In .SelectedItems
      'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
      DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
    Next
  Else      'Nothing selected in File Dialog
  End If
End With

[burgesg]

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

[PianoMan64]

in the code type the following near the top

 
      docmd.setwarnings False
 

[ADezii]

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

Dim fDialog As Office.FileDialog
Dim varFile As Variant
 
'Set up the File Dialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
  .AllowMultiSelect = False
  .Title = "Select a Text File to Import"
  .InitialFileName = "C:\*.txt"
 
  'Clear out any Filters, then Add you own (1)
  .Filters.Add "Text Files", "*.TXT"
 
  'Show the Dialog. If the Show Method returns True, the User picked
  'at least 1 File, otherwise the User clicked Cancel
  If .Show Then
    For Each varFile In .SelectedItems
      'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
      DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
    Next
  Else      'Nothing selected in File Dialog
  End If
End With

Reference Line Numbers 4 and 27

Dim fDialog As Office.FileDialog
Dim varFile As Variant
 
DoCmd.SetWarnings False
 
'Set up the File Dialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
  .AllowMultiSelect = False
  .Title = "Select a Text File to Import"
  .InitialFileName = "C:\*.txt"
 
  'Clear out any Filters, then Add you own (1)
  .Filters.Add "Text Files", "*.TXT"
 
  'Show the Dialog. If the Show Method returns True, the User picked
  'at least 1 File, otherwise the User clicked Cancel
  If .Show Then
    For Each varFile In .SelectedItems
      'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
      DoCmd.TransferText(TransferType, SpecificationName, TableName, varFile, HasFieldNames, HTMLTableName, CodePage)
    Next
  Else      'Nothing selected in File Dialog
  End If
End With
 
DoCmd.SetWarnings True

[Frank Kelm]

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

[ADezii]

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.

[Frank Kelm]

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,

[ADezii]

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.

[Frank Kelm]

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!

[ADezii]

Thanks, I appreciate your help!

You are quite welcome.

[ArnoB]

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

[ADezii]

To process Multiple Text Documents, change Line #9 to:
.

AllowMultiSelect = True

P.S. - You will also need a Method to uniquely Name each Table.