transfer text file

[rodneyb]

I receive a file from a client which has extension ".001" and the file looks like:

,64000 ,20031222,20031219,00010812,CCM,RTD,
,61426 ,20031222,20031219,00098108,CON,RTD,
,54928 ,20031222,20031219,00090638,GEN,RTD,


I want to be able to setup code to transfer this file onto a temp table. which command is best to used? the only command I can get to work is the TransferSpreadsheet command but in order for it to work I have to change the extension to .csv, open it and then save as an excel spreadsheet (.xls).

Any help would be greatly appreciated.

Thanks.

 

[WayneRyan]

Rodney,

I think that what you need is one of the "Import"
functions. I don't use them, because most of the
data I inherit is not that well "behaved". I use
VBA to import most of my "foreign" data.

You can probably use the Search facility here to
look for "Import" and the various import specifications.

Wayne

 

[rodneyb]

Thanks Wayne I've managed to fix it - the problem was I had two full stops in the file name to be loaded which was causing the error. However I noticed that when the file loads it's formatting big fields that have numbers in them to the text format "1.326655E+16" etc. how do I stop this from happening as I want all the numbers to show? ie normal format "13266551252516458" as this is an account number.

 

[Pat Hartman]

You need to create an import spec so that you can define the account number as a text field. The problem is that Access is interpreting the accoun number as numeric because it contains only numbers.

Don't forget to save the import spec (press the advanced button to get to the option) so you can refer to it in your TransferText code.

 

[namliam]

Pat,

While on the subject of import/export specs.... Is there any way to get at them without going through the menu, File/save as/ etc....

or file/import/bla bla

Regards

 

[Pat Hartman]

Not that I have ever found

 

[rodneyb]

Thanks guys - but how do I get access the import specs options as I am importing from this piece of code within a module:

DoCmd.TransferText acImportDelim, , "loadtmptransac", importloc & MyFile, False

 

[WayneRyan]

Rodney,

I don't use this, but the specification is argument #2 ("xx")

DoCmd.TransferText acImportDelim, "xx", "loadtmptransac", importloc & MyFile, False

Wayne

 

[rodneyb]

Thanks Wayne,

how do I create "xx"?

do I need to specify "xx" somewhere via menu options? if so how?

 

[WayneRyan]

Rodney,

xx is the name you gave your import specification. I think that
as you use the Wizard, about the third frame has a "Save as".
I think that it is under "Advanced".

It is there that you can give your specification a name.

You can then use that name in your code instead of the "xx"

Wayne

 

[Pat Hartman]

In order to create an import or export spec, you need to manually import or export the table or query, but only ONCE. Save the spec and then you can use it later in a TransferText Method or Action.