maximyshka
asked on
Transferring data from excel to access where column data in excel needs to be transferred as row data in database.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
is there any other way to transfer data to access , without using TransferSpreadsheet
Thank you for your help. I really hope that there are better solutions without using normalization.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.
Can data be transferred to access cell by cell?
ASKER
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
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.
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.
ASKER
Can data be transferred to access cell by cell?