Loading Text file to Access Database

[drpkrupa]

I have page with one button. If user click on the import button. I would like to open the file dialog box. User select the text file and click ok then i would like to transfer the data from text file to access table.

I wrote code which will open file dialog box and user can select text file. Can anyone help me out how to read the text file and load the data into access table.

Code for File dialog box.
Dim fDialog As Office.FileDialog
Dim vardirectory As String
vardirectory = "C:\Documents and Settings\user\Desktop\
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = True
.Title = "Please select one or more files"
.Filters.Clear
.Filters.Add "Text Files", "*.txt"
.InitialFileName = vardirectory
If .Show = True Then
For Each varFile In .SelectedItems
MsgBox varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

 

[Mr. B]

drpkurpa,

First, go to the File/Get External Data/Import option. The "Import" dialog box where you can locate a file will be loaded. At the bottom of this dialog box, change the "Files of type" to "Text files (*.txt;*.csv;*.tab;*.asc)". Go through the entire process. Before you exit this dialog box, click the "Advanced ..." button at the lower left of the dialog box. This will present the "Import Specification" dialog box. Go through the process here of defining just how the data is to be imported and then click the "Save As" button. Give the Import Specification a name. You may have to work with this a little until you have all of the import specification defined just like you want it, so that anytime you want to import this file you can simply go here and select the saved file specification and it will import the data correctly.

Once you have the Import Specification defined as you want it, go to your VBA code Window and type "docmd.transferText" place you cursor in the "transfertext" part of the statement and press "F1" and check out the Help file as to how to use this function.

HTH

 

[SOS]

Here's a pictoral view of how to do an import specification:

Access 2000 to 2003

Access 2007

 

[sphynx]

This post might help you

http://www.access-programmers.co.uk/forums/showthread.php?p=820781#post820781
http://www.access-programmers.co.uk/forums/showthread.php?p=820781#post820781

 

[drpkrupa]

Thanks for your help. I just tried and it works for me.