Import only certain columns from spreadsheet into an EXISTING table

[akedm]

Hi,

Is it me, or does Access allow for importing only certain columns from a spreadsheet only when importing into a new table. But when importing into an existing table (ie, appending), there's no such way to designate importing only certain columns. That's ridiculous.



The hypothetical spreadsheet has columns named: One, Two, Three and Four. I have the choice to not include column Three in a new table. That is, I can create a new table of columns One, Two, and Four from this hypothetical spreadsheet (omitting Three), and the Import wizard in Access is good with it. But when I update the spreadsheet that has columns One, Two, Three, and Four (that same spreadsheet as before), and wish to append the new data into the same table, I cannot, because Access doesn't allow me the choice to omit column Three from the import, as I did when the table was created.

What?!?

This is unacceptable. Please tell me I'm missing something.

Thanks.

[madpiet]

Hi,
Is it me, or does Access allow for importing only certain columns from a spreadsheet only when importing into a new table.

It's you.

Easiest way is to create a linked table to the Excel file and then something like this:

INSERT INTO AccessTable (Column1, Column2, Column3)
SELECT colA, colB, colD
FROM LinkedExcelFile
WHERE colB IS NOT NULL;

[CJ_London]

or just use sql

INSERT INTO AccessTable (Field1, Field2, Field3)
SELECT *
FROM (SELECT colA, colB, colD FROM [sheet1$A:D] AS xlData IN 'Path\filename.XLSX'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes])  AS XL;

[Micron]

I'd say the reason for the behaviour is that the wizard does not have to relate the source to an existing table - it is just going to create from scratch using your selections. In order to relate the source to an existing table it would need to be able to load that table (right down to its definition and not just the data in it) and allow you to say what goes where. Seems like a reasonable limitation to me.

[isladogs]

Is it me, or does Access allow for importing only certain columns from a spreadsheet only when importing into a new table. But when importing into an existing table (ie, appending), there's no such way to designate importing only certain columns. That's ridiculous.

Agree with the previous responses.
The only way what you're suggesting would ever be true in your append query / SQL is if you tried to omit a field that was REQUIRED in the destination table.

[akedm]

I barely understand what is being said here. And thank you for your replies. And the coding!

I don't think a linked table will work. The external data for this database will be cyclic - a series of monthly Excel workbooks imported into the database reporting the month's updates. If I understand 'linked tabels', a database will rely on the external data in the source workbook (Excel); that is, the database remains a mirror of the workbooks, and not independent data. Given that, I see that every time I update the report, the historical data will be overwritten in Access. That, or I'm wrong about linked tables.

So, I'll need to create my initial tables, and then append monthly via SQL.

By the way, in short, I'm trying to create a client balances aging database - how old are the balances, how many statements have we sent, is it an active or inactive client, last payment from client, frequency of payments, shape of the balance history, etc. I have a wonderful custom workbook in Excel pulling from nine other Excel reports monthly. It calculates a bunch of stuff from 20-some columns of data, and uses my macros to scrub the data and offer a combo of a dozen suggestions for next steps on each client. But after a year it's getting unwieldy in spreadsheet format, especially when I want to upgrade portions of the code. I'm thinking Access will be easier on a few fronts. I know a good amount about Excel, but I know nothing about Access.

Thank you.

[CJ_London]

That, or I'm wrong about linked tables.

you are wrong

When using linked tables you can change the underlying workbook. Think of the linked table as being a microscope and the spreadsheets as slides - you can change the slide and put in another one.

A typical method used is to copy the new file to replace to old file, usually using a standard name then run an append query. You can do this manually or in code. In code, assuming you have a linked table to a file called 'c:\dbfiles\importfile.xlsx', you might have something like:

delete old file 'c:\dbfiles\importfile.xlsx'
filecopy new file to 'c:\dbfiles\importfile.xlsx'
use append query to import new data

you may want additional actions to archive the recently imported file

[Micron]

I take this to mean that the linked data is volatile, i.e. it is affected by what happens to the linked workbook sheet.

that is, the database remains a mirror of the workbooks, and not independent data.

CJ, that makes it seem to me that you're saying if you change the data in the workbook sheet, the data in the linked sheet (in Access) doesn't update?

Since XL sheets rarely make for good tables, a common approach is to link to the sheet or import it, then use append/update queries to move the data into properly normalized tables. This also has the benefit that sheet data that is not the right data type can be converted to the proper type in the db tables. If your process over-writes the db tables with imports from the workbook it's of no concern as long as the structure of the imports contains the same columns every time. The process in Access is to then append/update the db tables from the new imported or or updated linked sheets. Or am I off base there?

[CJ_London]

CJ, that makes it seem to me that you're saying if you change the data in the workbook sheet, the data in the linked sheet (in Access) doesn't update?

Nope change the data in the workbook and that will be reflected in Access - as will replacing the file with another one (assuming the replacement has the same layout)

My preference is to use a query rather than a linked table. Benefits - more flexibility on what can be selected, by changing the IMEX value you can then edit the worksheet from Access. Disadvantage, slightly more coding required and you can't delete rows in the worksheet, just set the row values to nothing

[madpiet]

I was going to say "Have a look at PowerQuery", but then I think you may be stuck with DAX, which is a bit of a nightmare. But that way you could do the whole thing in Excel. And do things like retrieve all files with a given naming convention/prefix from a folder, append all the rows together, remove some columns, add some calculated columns, and end up with one tall table. Then report on that. No Access required. (Since you aren't comfortable with it).
Just one of the many options.