Getting data from Excel to Access

[GeekGirlInFurs]

This seems like the most "general" place to ask about how these two Office programs work together.

I need to get data from Excel into a new Access 2019 database created for the purposes of receiving this data.

The data in the Excel sheet is organized as a series of "records" of 10 rows each. The data is highly structured, and these 10 rows repeat without fail throughout.

Each "record" in Excel consists of basically three columns, with the first of those consisting of what in Access would be the fields of the database. The other two columns are the unique data of the record

The problem I'm having is that I don't see how to get the 10 vertical rows of each Excel record into the Access format where the fields are organized horizontally in the table. Access doesn't see anything in my Excel sheet that corresponds to its fields. And when I ask Access to create a table based on the Excel sheet, nothing useful results, even after I created a single-record Excel sheet as a simple template for Access to work wiih.

I've created a new Access database with a table that contains all the necessary fields. But even with that as a head start so to speak. I'm unable to get Access to recognize the structure of the Excel sheet in any usable way.

I'm assuming this is possible to do. Am I wrong?

I could enter the data manually. But there's a couple hundred of these records in Excel.

Any clues as to how I can proceed?

Is there a way to export from Excel in a tab delimited file that Access could use?

Thank you!

[HansV]

If the first column contains a field name, what exactly do the second and third column represent? Is the field a multivalue field in Access?

[GeekGirlInFurs]

Thanks, Hans.

The other columns represent data specific to the fields. This screen shot is an abstract of the Excel data structure I'm working with, showing two complete "records" as they appear in the Excel sheet. This structure is repeated exactly throughout the sheet.



I need to read these multiple Excel "records" into Access.

[HansV]

Let's say your sheet is named Sheet1.
Add a new sheet.
Enter the following formula in A1:J1 and confirm by pressing Ctrl+Shift+Enter:

=TRANSPOSE(Sheet1!A1:A10)

Enter the following formula in A2:J2 and confirm by pressing Ctrl+Shift+Enter:

=TRANSPOSE(OFFSET(Sheet1!$B$1:$B$10, 11*ROW()-22, 0))

Fill or copy down from A2:J2 as far as needed (the formula will start to return zeros when it runs out of data).

You should be able to import the new sheet into Access.

S2537.png

S2538.png

[GeekGirlInFurs]

Thanks again, Hans.

The TRANSPOSE function looks like exactly what I need. But I am not seeing how to actually use your formulas, which look like they will read the entire range from Sheet1 and auto populate the new rows.

When I copy the fields, and manually use the Transpose function (Alt-H,V,T) the data pastes exactly as expected. And I can see how that will let me restructure the data.

But should the formulas — especially the second one for the actual data — work so that the fields are populated automatically? That's what I'm not seeing how to do. When I paste the formulas as you gave them, they read only a single cell from Sheet1, rather than the range. Apologies if I'm being dense and not seeing something obvious! :)

[HansV]

Did you select cells from column A to column J and confirm the formula by pressing Ctrl+Shift+Enter?

[GeekGirlInFurs]

Thank you Hans! I was able to do what I needed using the Transform function, which I wasn't really aware of before you mentioned it. But it didn't do it using the exact method you suggested. Here's what happened.

For some reason I was never able to get the formulas you gave me to work. No matter how or where I pasted them in the range, and no matter when or where I pressed Ctrl+Shift+Enter — the results were nothing like in your screen shots. There must be something very basic that I'm just not getting, because I see from your screen shots and from the formulas exactly how it should work. And it's exactly what I was looking for. In any case......

Through my attempts to get the formulas to work, I was playi8ng with the the Transform function as it works from the Paste menu, and realized I could do it with the help of an AutoHotKey script, which performed all the keystrokes necessary to transform each record into the needed structure, then move down to the next record. Because the Excel sheet was already tightly structured, I just ran the AHK script once for each record, from a hotkey, which took just moments. :)

Still not sure what I was getting wrong with the formulas you so kindly provided, but pointing me towards the Transform function saved the day!

Thanks!

[HansV]

For what it's worth, here is an example using non-array formulas.

Example.xlsx