transform from excel layout to database

Question

I would think this is not uncommon - and so would welcome advice from those that have tackled this before a few times.

A project is begun on excel - and so the layout is typical excel side-by-side:

Date:        1/1/16       1/8/16       1/15/16    etc

Amount:    $20             $22           $32         etc

It needs to go into a database in rows:

Date     Amount

1/1/16     $20

1/8/16     $22

1/15/16    $32

etc.

is there an efficient union query approach to make this transform? what would the sql look like in this example?

Answer 1

Hi. A UNION query will certainly work, but it might be inefficient. If you can, I would recommend using VBA to convert your table into a properly normalized structure. Just my 2 cents...

Answer 2

it is a one time import/transform to move data from the excel into the database - not an operational recurring process - so in that light one can live with some inefficiency I suppose.  But it is a big excel sheet; lots of rows & columns.

I am unclear as to what vba code approach would look like.

Answer 3

If you have access to Excel, I would use the built in function of Excel to transpose the data.

1. Copy the data in first sheet
2. Right click in another sheet in the A! field and
3. Paste Special
4. Transpose

Then import the corrected sheet

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 4

ah thank you CW - am not an excel guy and so didn't even think of that.....  the real excel is a lot more hairy in having lots of rows.....its more like:

Date:        1/1/16       1/8/16       1/15/16    etc

ABC Co:    $20             $22           $32         etc

XYZ Inc:    $20             $22           $32         etc

ZZZ Top:   $20             $22           $32         etc

but I get the jist of your suggestion and will experiment with an excel transpose....

Answer 5

How hairy can it be.

Using your data in excel here is the results in a screen shot.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 6

But really you need something more like...

lets call it a fee Table since I don't really know what it is...

Fee_Table

Date     |     Company     |     Fee

1/1/16         ABC                  $20

1/1/16         XYZ                  $20

1/1/16         ZZZ                  $20

1/8/16         ABC                  $22

1/8/16         XYZ                  $22

1/8/16         ZZZ                  $22

1/15/16        ABC                 $32

1/15/16        XYZ                 $32

1/15/16        ZZZ                 $32

If this is the case then you would want to use an array to collect the data.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 7

it is a one time import/transform to move data from the excel into the database - not an operational recurring process - so in that light one can live with some inefficiency I suppose.  But it is a big excel sheet; lots of rows & columns.

I am unclear as to what vba code approach would look like.

Hi. Even if this is just a "one-time" requirement, creating a UNION query can be tedious and possibly create some errors if you're not careful. If you decide to use Excel to solve the problem, it might be easier than creating UNION queries. If you decide to use Access VBA, then you can use a loop to go through each column, which means it won't matter how many columns there are, and you can potentially reuse the code in case the same issue comes up in the future.

Just my 2 cents...

Answer 8

point taken on the tedious aspect of setting up the union query.....

Answer 9

yep - you hit the nail on the head here with what I will call 'Final format'. 

The excel transpose of your prior post is definitely an excellent first phase to reformat - will call that 'Transpose format'

I could grunt in a lot of data getting from transpose format to final format with simple appends, manually resetting them to the new column; ABC, XYZ , etc...and while not elegant I am not at above doing just that within reason....but in this case there would be hundreds of columns and that's not viable...

Array work is not current in my programming know-how....some vague memories of past programming class work but nothing current.....

Answer 10

Suggestion 1

Import the data as is and use the Access Table Analyzer to see the results

Suggestion 2

Post your question on Excel Forums for other options for the data arrays

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 11

I appreciate the various input on this.

I will add an observation that having developed Access for a fairly long time - and often doing work that originally started on Excel  - that I never realized the hassle/complexity of moving a large volume of excel data into a database.

Guess all my prior work the excel was either just used as an example used but I started the db from scratch or that the transfer of data into the new database was not so large that it couldn't be accomplished just via some semi manual grunt effort. 

Now that I see a really big excel with 80+ columns and 1000s+records - attempting to normalize this guy is quite a challenge.  The user in this case has been using excel for years before realizing it couldn't keep on this way - needs to go multi user and get better input controls.

I've seen/used some slick little commercial apps that transform between QuickBooks and excel - - - - one for this situation would be useful.

Answer 12

Sorry for not reading all the details above, but doesn't a Pivot work in Excel or a Crosstab Query in Access? 

http://www.excel-easy.com/data-analysis/pivot-tables.html

http://www.fmsinc.com/microsoftAccess/query/crosstab-report/index.html

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 13

There are a couple of examples of how to recast data into a set of normalized tables, in one case including an import from Excel, in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

For situations where the imported table 'encodes data as column headings' as in the example in your original post the UnencodeColumns demo shows how to automate the insertion of the data into normalized tables.  This file includes two examples, one where the imported data are Boolean TRUE or FALSE values, presented in check boxes in the form in the demo, the other where the imported data are quantitative values, presented as integers in text boxes in the demo.  The latter would be the appropriate model in the case of your original example, with the StoreStock_Vals table being analogous to the result table you want.

For situations where the imported table requires normalization by decomposition into a set of multiple related tables, including one modelling a binary many-to-many relationship type, the DecomposerDemo file illustrates how to do this by means of a sequence of 'append' queries.  The demo takes you through these one by one, with brief explanation of each.

---

Ken Sheridan, Stafford, England

Answer 14

well a pivot/transform in excel definitely is a useful half step; the info by CW above is on that topic.....the other half can still be a ton of work depending on the excel....

Answer 15

thanks KS - will take a look at it....