Importing TXT Files 1

[sha123]

Please Help, I need to import a txt file into my table,does anyone know the code for importing it using a command button?

The delimiter = |

 

[MacCaillean]

Hi sha123

Try

DoCmd.TransferText acImportDelim, , DestinationTable, SourceFile

in the OnClick event of your button.

DestinationTable

is the name of the table which your file will be imported into.

SourceFile

is the name of the file on disk that you want to import.

Regards

Mac

 

[asppage]

This is something that I needed help with not to long ago. I received some excellent help so I thougt I would return the favor. This code will import your text file into the table you desire. All you need to have is a textbox, (in my case I called my textbox Text2. Place this entire code in the onclick event of the button to your form. The table I have has 11 fields as you can see in the script below. The Mid(MyLine, 1, 2) line in the code reads the file. For example on the first line 1 is the first place marker in the textfile. The number 2 represents the number of spaces you want to read in the file. Look at the 9 numbers below in bold. Try it out. I'm sure it will work for you.

12 3456 789


Dim filnam As String, s As String, MyLine As String, MyLocation As Long, ch As String
Dim rs As DAO.Recordset, once As Boolean


filnam = "C:\ACCESS\JUNQUE\lbchrupld.txt"
Set rs = CurrentDb.OpenRecordset("lbchrupld&quot
once = False
If Not IsNull(Me.Text2) Then filnam = Me.Text2
Open filnam For Binary As #1
MyLine = ""
MyLocation = 0
Do While MyLocation < LOF(1)
ch = Input(99, #1)
If once Then
rs.AddNew
rs.Fields(1) = Mid(MyLine, 1, 2)
rs.Fields(2) = Mid(MyLine, 3, 3)
rs.Fields(3) = Mid(MyLine, 6, 2)
rs.Fields(4) = Mid(MyLine, 8, 13)
rs.Fields(5) = Mid(MyLine, 21, 5)
rs.Fields(6) = Mid(MyLine, 26, 15)
rs.Fields(7) = Mid(MyLine, 48, 6)
rs.Fields(8) = Mid(MyLine, 55, 5)
rs.Fields(9) = Mid(MyLine, 62, 4)
rs.Fields(10) = Mid(MyLine, 66, 5)
rs.Fields(11) = Mid(MyLine, 90, 8)
rs.Update
End If
once = True
MyLine = &quot;&quot;
MyLine = MyLine & ch
MyLocation = Loc(1)
Loop

MsgBox &quot; File has been successfully imported! &quot;
Close #1 ' Close file.
rs.Close
Set rs = Nothing

DoCmd.OpenForm &quot;fini&quot;, 3





End Sub


On Error Resume Next
ActiveXCtl12.ShowOpen
Me!txtPath = ActiveXCtl12.FileName

 

[sha123]

asppage thanks for the code!

I just have one problem:
Here is an example of the text file layout:
1- OD|005|20088354|N|881111|BULK SEASONAL STIR FRY|15|5520|6|15.59|23.99|93.54|20030809
As you can see the delimeter = |, when I do the import with your code it imports the whole line into one field!

How can I add a delimeter

 

[PaulF]

have you attempted to create an Import Specification using the Wizard? It will allow you to select the type of Delimiter, and even allow you to enter the | since it isn't in the list provided. Then just save it, and use is as part of the DoCmd.TransferText action.

PaulF