Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
905 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 39

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 39

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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