Importing a text file using VBA 2

[itechC]

Hi all,

I would like to import data from a text file into my access database using vba. I have alot of text files to import so i would like to automate it. Any ideas how to go about doing this. The data is sererated by commas.

 

[mgolla]

Use this code to import:

DoCmd.TransferText acImportDelim, , TableName, FileName

 

[itechC]

I tried that and it tells me "The search key was not found in any record"

here is a copy of my code:
Sub ImportTxtFile()

Dim vPath As String
Dim vTableName As String

vTableName = "catering"
vPath = "C:\Documents and Settings\Frank\My Documents\" & vTableName

DoCmd.TransferText acImportDelim, , vTableName, vPath

End Sub

 

[mgolla]

try adding the file extension .xls to vTableName

 

[itechC]

Ok i added the extension to the file name (txt). It imports the file but only the first info before the ;.
Example:
Thisis what the file that i want to import look like:
"ABC GOURMET CATERING";"";"";"916450132";"CALLE TORRES QUEVEDO, 4";"MOSTOLES";"MOSTOLES";"28936";"MADRID"

I only get ABC GOURMET CATERING in my access table.

 

[mgolla]

Ok,

You will need to create a SpecificationName to tell access that the delimiter identifier is ";" not "," to get it to import properly.

To do this select any table and choose File->Export

When the prompt opens change the save as type to Text Files

When the export text wizard opens click on the advanced button. Change the Field Delimiter to ";" then click on the save as button. Enter the name you want for that spec and change your code to this:

DoCmd.TransferText acImportDelim,SpecName,TableName,FileName

HTH

 

[itechC]

Thats exactly what i did and i still get only the title of the record i want to import. My table only gets populated with one field.

Sub ImportTxtFile()

Dim vPath As String
Dim vTableName As String
Dim vSpecName As String

vTableName = "catering"
vPath = "C:\Documents and Settings\Frank\My Documents\IT Data Direct\Spain\Spain Data\" & vTableName & ".txt"
vSpecName = "Spain Export Specification"

DoCmd.TransferText acImportDelim, vSpecName, vTableName, vPath

End Sub

Any other ideas??

 

[itechC]

if I import it manually by clicking on the semi-colon as my text delimeter it works fine.When i load my specification it gives me only the title.

 

[MaxEd]

I can't find the advanced feature when I click the export. HELP!! thanks.

 

[gigajeff]

Make sure you put the import specification name in quotes or it won't recognize it.

 

[MaxEd]

oops. I figured it out.. Thanks.

 

[kjv1611]

Hey,
mgolla, your answers in this post were exactly what I needed for a current project!

Thanks! have a star!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV