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

 

[Ken Snell [MVP]]

The basic code to do what you want is here:
Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile

The above code is using TransferSpreadsheet action. You can change that line
of code to use TransferText (with its own arguments) instead. Also, you'd
need to change the code that sets the filter for the type of file to use
text file extensions, not EXCEL extensions.

 

[Jack Leach]

http://www.applecore99.com/gen/gen029.asp

If the data in the text file is structured on a line by line basis (like an
..ini file) and not as a particular data format (like a text version of a
table, in which case Ken's link works great), the above link gives a rundown
on the commands used for basic file input/output in vba.

Using this you can open the file, import each line individually (to a
variable) and do with it what you would like from there. Something along the
lines of

Dim iFile As Integer 'Filenumber
Dim sLine As String 'Current Line of the file
iFile = FreeFile()
Open "C:\YourFile.txt" For Input As iFile
While Not EOF(iFile)
Line Input #iFile, sLine
'do something with the line here, add to recordset, array, etc.
Wend
Close #iFile


Depending on the task this is useful sometimes, but for delimited data
you're better off with TransferText as Ken provided.


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)