TransferSpreadsheet - import specific columns?

[Robin]

Hi

I'm importing an Excel Spreadsheet into an Access table using
"TransferSpreadsheet". However, the Spreadsheet is 140 MB and it's on a
server halfway round the world. The import can take 3 hours! I only need 5
of the 70 or more cells - the rest gets dumped after the import. Is there a
way to import just specific cells?

The range "SheetName!" works for a whole sheet; "SheetName!A:CA" works for a
continuous range of cells in a Worksheet, but is there anything that would
allow the equivalent of "SheetName!A,G,AF,CA,CB" ?

Regards

Robin

 

[fredg]

Hi

I'm importing an Excel Spreadsheet into an Access table using
"TransferSpreadsheet". However, the Spreadsheet is 140 MB and it's on a
server halfway round the world. The import can take 3 hours! I only need 5
of the 70 or more cells - the rest gets dumped after the import. Is there a
way to import just specific cells?

The range "SheetName!" works for a whole sheet; "SheetName!A:CA" works for a
continuous range of cells in a Worksheet, but is there anything that would
allow the equivalent of "SheetName!A,G,AF,CA,CB" ?

Regards

Robin

I believe the range imported must be contiguous.

In 5 unused contiguous cells on that spreadsheet, lets say Z1:Z5,
write:
= A5
=G15
=B14
etc>

to get the value in cells A5,G15, A14, etc.

Then import "SheetName!Z1:Z5" instead of A5,G15, B14, etc.

 

[Robin]

Hi Fred

Unless I misunderstood what you said, I think this sounds a good idea for
individual cells, but I may have been a bit vague in my original post -
sorry! When I referred to 'Cells' I actually meant 'Columns of Cells'.

The file being imported is around 70 columns wide and contains about 24,000
records. I need to import entire columns of cells (all 24,000) by their
ordinal reference such as A, F, H rather than the individual cells, A5, F15,
H3.

Also, the Spreadsheet being imported is itself an automated export from
another system which I don't have any control over. I can't add anything to
the file beforehand.

Regards

Robin

 

[John Spencer]

No answer, just a couple of questions.

Is it the actual transmission that is taking all this time?

Can you do five imports and import the columns one at a time?

Can you ftp (or copy via some other means) the entire file to your computer and
then do the import into Access?

 

[Robin]

Hi John

No answer, just a couple of questions.

Is it the actual transmission that is taking all this time?

In the main, yes - large amount of data, very busy and slow LAN to a server
about 8,000 miles away. Anything I might be able to do to import just the
bits I need, will obviously speed things up. But the import also takes
quite some time indexing the new table afterwards, which I guess would be
much quicker if I only had five columns!

Can you do five imports and import the columns one at a time?

I had thought about this - may be the best answer, but I cannot think how to
do this at the moment in a way that gets everything re-compiled correctly
afterwards. I'll give this some more thought!

Can you ftp (or copy via some other means) the entire file to your
computer and
then do the import into Access?

This is still impacted by file size and network speed - doesn't really speed
things up much, although the indexing is faster afterwards.

 

[Robin]

An alternative came to me - if I can't limit the columns (beyond a
contiguous range), is there a way to limit rows? This file has about 24,000
rows, of which I need about 300. Can I filter based on the content of one
column (during the import, rather than afterwards)? I suspect not but
thought I'd ask anyway

 

[John Nurick]

Hi Robin,

Two possibilities to import only the data you need:

1) Get selected columns only, using a query into the worksheet (you have
to specify a contiguous range on the worksheet, but don't have to import
all the columns). The syntax is along these lines:

SELECT F1 AS MyField1, F3 AS MyField2, F25 AS MyField3
INTO MyNewTable
FROM [Excel 8.0;HDR=No;database=C:\Book.xls;].[Sheet1$]
WHERE F2 = 'Something'
;

or

SELECT Field1, Field3, Field25
INTO MyNewTable
FROM [Excel 8.0;HDR=Yes;database=C:\Book.xls;].[Sheet1$A2:E2000]
WHERE F2 < 200
;

Note the default field names F1, F2... if there are no column headers in
the Excel range. You can use a range name instead of a sheet name; in
that case don't use the $.

The problem with (1) is that as far as I know it still has to pull all
the data across the network in order to decide what needs to be
imported. So:

2) Use a script on the server to run a query that extracts only the data
you need, and then just download that. This doesn't mean running Excel
or Access on the server (which might upset your netadmin people),
although it does need to be a Windows server. The script, in any
OLE-aware language, can use the DAO or ADO libraries to run the query
and put the results into a text file, workbook or Access database.

BTW, is the 140MB spreadsheet the actual data store, or is it merely
output from a proper database? If the former, it's alarming; if the
latter, could you get the DB admins to create a query for you and give
you access to it?

 

[Robin]

Hi John

I've been so blinded by DoCmd.TransferSpreadsheet that I didn't see the wood
for the trees. The Maketable Query suggestion does just great! I'm
developing this off-site and have a test network with a throttled wireless
hop to simulate the bandwidth problem, and in this test environment, the
Query alternative speeds things up by at least a factor of 5 or 6.

Thank you very much John.

I love this News Group!

Regards

Robin

PS The 140MB file is a weekly report output from Seibel.

Hi Robin,

Two possibilities to import only the data you need:

1) Get selected columns only, using a query into the worksheet (you have
to specify a contiguous range on the worksheet, but don't have to import
all the columns). The syntax is along these lines:

SELECT F1 AS MyField1, F3 AS MyField2, F25 AS MyField3
INTO MyNewTable
FROM [Excel 8.0;HDR=No;database=C:\Book.xls;].[Sheet1$]
WHERE F2 = 'Something'
;

or

SELECT Field1, Field3, Field25
INTO MyNewTable
FROM [Excel 8.0;HDR=Yes;database=C:\Book.xls;].[Sheet1$A2:E2000]
WHERE F2 < 200
;

Note the default field names F1, F2... if there are no column headers in
the Excel range. You can use a range name instead of a sheet name; in
that case don't use the $.

The problem with (1) is that as far as I know it still has to pull all
the data across the network in order to decide what needs to be
imported. So:

2) Use a script on the server to run a query that extracts only the data
you need, and then just download that. This doesn't mean running Excel
or Access on the server (which might upset your netadmin people),
although it does need to be a Windows server. The script, in any
OLE-aware language, can use the DAO or ADO libraries to run the query
and put the results into a text file, workbook or Access database.

BTW, is the 140MB spreadsheet the actual data store, or is it merely
output from a proper database? If the former, it's alarming; if the
latter, could you get the DB admins to create a query for you and give
you access to it?

An alternative came to me - if I can't limit the columns (beyond a
contiguous range), is there a way to limit rows? This file has about
24,000
rows, of which I need about 300. Can I filter based on the content of one
column (during the import, rather than afterwards)? I suspect not but
thought I'd ask anyway