Solved

Transferring data from excel to access where column data in excel needs to be transferred as row data in database.

Posted on 2014-09-09
6
797 Views
Last Modified: 2014-09-29
Hi,
 I have bunch of excel files with data , please see sample excel file.
I need to transfer that data into access database using access vba, where columnar data in excel needs to be transferred as row data in database.

Description of Excel file, and transfering rules
sample-excel-file.xlsx
A1 - A4 Contains data for MetNumber AccNumber OEBID Address . It should be common to to row 1-6.
B1-C1 - contains Year and UOM common to respective rows.
Column D -O represent monthly data from July(07) - June(06) . Each data must be transferred as a row in Access database

All records are alike and every 8th is a new MetNumber.

In sample access file I have a sample table which have transferred data from first 6 rows in sample excel file, so you can understand the pattern. I also have fields which have file name and row# in that file. So I can know where the particular data can be found in original excel file.

I have about 100 files with many rows, so would like to use access vba to select folder and it should go thorough files to update data. I am a bit lost how to convert column data into rows...
Any help would be appreciated
Sample-Access-File.accdb
0
Comment
Question by:maximyshka
  • 3
  • 3
6 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40313138
In a relational database, all rows contain the same data.  Since this spreadsheet does not conform to that rule, there is no automatic way to import the file.  You will have to write VBA to do it.  Keep in mind that the code you write will only work if the spreadsheet is formatted EXACTLY as you expect it to be.  Missing/extra rows/columns will cause a problem and you may or may not be able to code around them so start by assuming compliance.

It looks like you need to gather the data in the a column of the first 4 rows of each set and write that to tblA.  Then the data in columns B & C of the 6 rows in the set go into tblB.  Then columns D-O respectively to into tblC - one column per row alone with the ID of the related tblB record.

tblA
tblAUniqueID (autonumber)
fldA1
fldA2
fldA3
fldA4

tblB
tblBUniqueID (autonumber)
tblAUniqueID (foreign key)
fldB
fldC

tblC
tblCUniqueID (autonumber)
tblBUniqueID (foreign key)
fld D/E/F/G/H... O

Since you have to read four rows to accumulate all the data you need for tblA before you write any records, the issue is complicated and you are forced to create arrays to hold data and then work from the arrays.  A simpler way to code, is to write tblA immediately with only the first column populated.  That gives you the ID you need as the foreign key so you can write tblB and that gives you the foreign key you need to write all the rows for tblC.  As you read rows in the sheet, you will need to accumulate the remaining three columns for tblA and at the end of a group, update the tblA record before moving on.

I didn't even attempt pseudo code because I'm not sure I completely understand the rules but that's my first cut at a procedure.
0
 

Author Comment

by:maximyshka
ID: 40313215
Thank you for your help. I really hope that there are better solutions without using normalization.
Can data be transferred to access cell by cell?
0
 

Author Comment

by:maximyshka
ID: 40314911
is there any other way to transfer data to access , without using TransferSpreadsheet
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:PatHartman
ID: 40315021
Thank you for your help. I really hope that there are better solutions without using normalization.
 Can data be transferred to access cell by cell?
Did you mean normalization or did you mean automation?  What you described you wanted was normalization.  Taking each column and writing it to a separate row will normalize the table.  Yes.  Cell by Cell is what I described.  You do that with VBA by automating Excel.   TransferSpreadsheet works only on table type sets of data.  As it happens, you could use TransferSpreadsheet to import the data from the workbook or link to it if you prefer, but you would end up with exactly what you started with.  It would just be harder to work with than working directly with Excel.
0
 

Author Comment

by:maximyshka
ID: 40315259
Will solution be simpler without normalization? Do you have a code example on how to read from excel file running excel automation vba from access side? I am ok to have the data in one table as i show in access sample file i uploaded
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40315337
For some reason I can't download databases from this site.  The downloader translates them as html gibberish so I can't see the table you are looking for.

Only semi-normalizing the schema actually makes it more difficult since you have to read the first four rows of any set in order to get the fields from column A.  That means that you will have to constantly move forward and backward because you will need to collect all column A fields before you can write ANY row to the table.  Or, you have to load each set of data into a multi-dimensional array.

The spreadsheet is formatted consistently enough so that you could actually import it and that would avoid automation code.  Just make sure your import assigns good column names or your code will be undecipherable and also adds an autonumber ID.  You're going to need the ID to help you select sets of data.

Select ...
From ...
Where ID Between StartID and FinishID;
you maintain start and finish in code.   Start is 1 and finish is 8 for the first set.  Then you add 9 (6 + three blank rows) to each variable and get the next set.  

I don't have anything close to what you need code-wise.  Imports like this are always completely custom.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - open a DataValidation dropdown 6 22
Loop within Select Case 3 26
Access MDB/PDF 21 32
Data Conversion 9 0
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now