TransferText / TransferSpreadsheet

[Guest]

Hi,
I was trying to import a table, with both commands:
TransferText - for *.txt file
TransferSpreadsheet - for *.xls file
and I got a problem with both of them.

In TransferSpreadsheet- all the columns connect to one column after importing.
The code i used :
DoCmd.TransferText acImportDelim,"", "tbl_data2", File1, -1
DoCmd.TransferText acImportDelim, , "tbl_data2", File1, -1
Both didn’t work. In the text file I separated the columns with ‘tab’

In TransferSpreadsheet- all data is copied into the table, but for some
reason the table get about 5000 empty rows in the begging, and on other times
it’s copied to the end of the last table – what i need is to replace the data
of the last table.
The code i used :
DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="tbl_data2", _
FileName:=File1, Hasfieldnames:=True, SpreadsheetType:=8


Thanks all
Dana.

 

[Nikos Yannacopoulos]

Hi Dana,

See answers below questions.

HTH,
Nikos

Hi,
I was trying to import a table, with both commands:
TransferText - for *.txt file
TransferSpreadsheet - for *.xls file
and I got a problem with both of them.

In TransferSpreadsheet- all the columns connect to one column after importing.
The code i used :
DoCmd.TransferText acImportDelim,"", "tbl_data2", File1, -1
DoCmd.TransferText acImportDelim, , "tbl_data2", File1, -1
Both didn’t work. In the text file I separated the columns with ‘tab’

The second argument in TransferText is SpecificationName; this implies
that you need a specification to make it work properly! To create one,
do one import manually, so as to create a spec; it will be saved
internally in your database, so you can then use it in your code. If in
doubt on the name, click on Advanced / Save As while creating the spec.

In TransferSpreadsheet- all data is copied into the table, but for some
reason the table get about 5000 empty rows in the begging,

Possibly "blank" rows in your spreadsheet, i.e. rows where the contents
have been cleared, but the rows themselves have not been deleted.

and on other times
it’s copied to the end of the last table – what i need is to replace the data
of the last table.

TransferSpreadsheet appends records to an existing table, it does not
overwrite it. If you want to clear the table prior to a new import, you
can do so easily in your code by preceding the TransferSpreadsheet
command with:

strSQL = "DELETE * FROM tbl_data2"
CurrentDb.Execute strSQL, dbFailOnError

 

[Guest]

Nikos thanks a lot,
it worked

 

[Nikos Yannacopoulos]

Welcome!

 

[Guest]

I am having a similar problem with linking to a txt file with headers. The
txt file is tab delimited.

The link works in the sense that data is shown all in one field - Access
seems to ignore the tab delimiter. I cannot set a specification since I do
not know the number or order of columns until the file is opened.

Is it a case that the 'default' delimiter is a comma and if so is it
possible to change it? Or am I on a lost cause!

Many thanks

DoCmd.TransferText acLinkDelim,"", "tbl_data2", File1, True