Importing a Text File

[DaddyNackers]

I need some help....

I want to import a text file using VBA code. The text file is a fixed format (each field I want to allocate a specfic number of characters).

How is this possible?

This is some of the code I have already written...

--------------------------------------------------------
Dim tdfLinked As TableDef
Dim strTable As String
Dim strConnect As String

strConnect = "TEXT;DATABASE=K:\SCMLOGISTICS\FCST ACCURACY\ADMINISTRATION\INPUT FILES;"
strTable = "SourcingX"
Set tdfLinked = CurrentDb.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = "SOURCING.TXT"
CurrentDb.TableDefs.Append tdfLinked
--------------------------------------------------------

I know that the code I have written is for linking a file to an Access database. I really would like to just import it.

Any help is much appreciated!!

Thanks,
Mike

 

[DaddyNackers]

One more thing.........


This code works fine (at least it links to the file). But, all the data is in one field. The problem is the fixed format.

Thanks,
Mike

 

[AJD10]

I import txt & XLS files a lot:

DoCmd.TransferText acImportDelim,"Specification", "MyTable", "myfilename", True

I don't know if there's a better way to create Specifications, but I go to File/Get External Data/Import -- then under Advanced tab, I specify the fields to import from the TXT and what the field names are in the table.

(There's TransferSpreadsheet and all other options under the pop up.)

HTH

 

[DaddyNackers]

HTH - Thanks for your help. I am getting the files to import.

One more question...........When I import the text files, I am always losing the first record from the text file. Why is this, and how can I fix it??

Thanks,
Mike

 

[LearningSql]

Hello.

In the statement:

DoCmd.TransferText acImportDelim,"Specification", "MyTable", "myfilename", True

The "True" at the end is a paramenter specifying if the .txt file has field names for the 1st line. If you set this to False, you should be able to pick up the 1st line of data.

Hope this helps.