Importing Only Certain Fields From Excel (1 Viewer)

[Ryobi]

Hello,

I have a problem that perhaps somebody know the answer. I am importing
data from an Excel spreadsheet in Msaccess table. I am using Docmd.Transferspreadsheet method. I am able to import the data, but what I would like to do is to import only certain fields from the spreadsheet. I am able to do with the wizard, but the Transferspreadsheet does not have that option.
Does anybody know a way around this problem ?

 

[Frothingslosh]

It's complicated and fairly slow, but using THIS is always an option, even if just as a starting point.

Edit: Upon thinking about it, I'd say that in your case, however, rather than using the above solution (which is overkill for you), I'd import the entire spreadsheet into a staging table, and then use queries to just copy the columns you want to keep into the permanent table(s).

If that's not an option, then you're stuck using an automation solution such as the one I linked.

 

[Ryobi]

Thank you for replying. I will give a shot later since I need to create something quickly for now. The reason that I wanted to imported certain columns is because the spreadsheet has 22,000 records. Having said that, I only need certain fields, but the software company will not gives us the options to select which columns we need. It's all or nothing.

 

[Frothingslosh]

Welp, like I said, best solution is to do a full import to a staging table, followed by using an append query to copy just the columns you need to your production table(s).

If you go this route, however, make sure that the db with the staging table (which really should be the backend) gets compacted regularly.

If that's impractical, then as far as I'm aware, you need to use automation to do this. The primary problem with that is line-by-line importing is far, far slower than TransferSpreadsheet.

 

[Ryobi]

Usually is I clear the import tables since we are usng the table get totals and not any other purpose. You are correct as far going line by line it a very time consuming process, but least now I have an option if I want to go that route. Thank you for the information.

 

[spikepl]

Or use a so-called side-end for the staging tables. A throw-away one-use template. You copy it , use the copy and dump the copy after use

 

[sxschech]

Here is another approach that uses an sql statement, you would need to provide the cell range that you want to import. The example shows getting data from
A2:U65536, but of course if your data is from F36:X150, you can do that too. Also, since this is a query, you can union together if data are non contiguous. Even though the sql statement doesn't require to be run in vba, if the statement doesn't work, you may need to check the references for excel in the tools references of the vba and then use the appropriate version in your sql statement.

http://www.accessmvp.com/kdsnell/EXCEL_Import.htm#ImpSQL

SELECT T1.*, 1 AS SheetSource
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFolder\MyFile.xls].[WorksheetName$A2:U65536] as T1;

You won't need the "1 AS SheetSource" in your query unless you wanted that there to identify where it came from. If your headers on not directly above the data, then if you want the headers to have specific field names, access will generically name each col as F1, F2, F3, etc., so do something like the following:

SELECT F1 AS ID, F2 AS NAME,...

And Change HDR=YES to HDR=NO

 

[gemma-the-husky]

when you say "the software company", I take it they produce the spreadsheet/csv for you.

can you connect directly to their tables? ODBC Link perhaps?