adding new excel data of the same type on a regular basis to one table

[ishtanbul]

I am extremely new to access, I have an internship with a large company and they thought I should work on their lab results database since they couldn't find anything else for me to do. They don't seem particularly concerned that I have no experience with Access, apparently letting me struggle with no manual isn't an issue. Let me explain the task:

Every so often, I will receive an excel file with results from tests they do burning various types of wood. Each excel file contains multiple wood samples (about 20 rows at a time) and each sample has various parameters being measured (element oxides in the leftover ash, 12 per sample) but there are also a bunch of blank or unimportant columns in each spreadsheet that I don't want to import, as well as the top row bc that is just the headings.

Each time the data gets imported, it has to add to the table, not replace anything, so eventually there will be hundreds of samples where the same parameters are being measured for. The idea, according to my boss, is that every time they get a new file from the lab, access should import all of the required datapoints (without the ones they dont want) into the right parts of the table in Access automatically, and that these would be related to other tables as well. Lets not worry about the relationships just yet though. Perhaps this is extremely simple, I have no experience whatsoever with VBA or macros, but if that is the only route, I guess I could learn.

 

[Beetle]

As long as the data being imported from Excel is consistent (i.e. the values you want and the values you don't want are always in the same columns) then I would import the data to a temporary table in Access. Once in the temp table use an append query to write the columns you want to the real table then a delete query to delete the imported records from the temp table.

If the imported data is not consistent, I would still use a temp table but you may need to use some VBA to loop through the table and find the columns that contain the values you are looking for.

 

[ishtanbul]

Thank you, thats a very helpful start. I've encountered a few problems though.

1. All of my data got rounded to the nearest decimal in the append destination table! not good

2. When I try to import a new temp table and append that to the destination table, I have to go back into the append query design veiw and reassign the table for each field to the new temp table. This is very tedious and I want to select and apply the new temp all at once.

3. Each of my appends should only be 14 records, but the second one I did was like 350 records for some reason! Many of them in the destination table were lots of duplicates in a row. Perhaps this has to do with the fact that I changed the field size in the destination table from long integer to decimal, to try and fix the rounding problem, but that didn't get fixed anyway...

Thanks for the help this is really a life saver!

 

[Beetle]

Can you post some examples of the data that is being incorrectly rounded (before and after)? Also post the SQL of your append query if possible and a basic description how you set up your temp table, etc.

Also, are the excel files consistent or inconsistent?

 

[ishtanbul]

I seem to have fixed the rounding problem by changing the field size to double instead of decimal, but that was trial and error not an educated decision. A typical field (with heading) looks like this:

SiO2 (%mass)
2,94

We use commas rather then decimals in Germany, the program knows this. Previously with both long integer and decimal as the field size that would round up to 3.

My temp table is set up exactly like my excel sheet, except all of the headings get changed to field 1, 2 etc bc it doesn't recognize certain characters. Thats not an issue though bc I know I want fields 36-47. I am working with 1 dataset (20X48 cells) and have made copies into sheet 2 and 3 but with minor alterations to simulate 3 unique datasets. Here is the SQL of the append query, using temp_1 (sheet 1):

INSERT INTO [bio_Element Oxides] ( [Sample Name], SiO2, Fe2O3, Al2O3, CaO, MgO, Na2O, K2O, SO3, MnO, BaO, TiO2, P2O5, [Summe Asche Oxide] )
SELECT [temp_2 BIOMASS TEST mod 1].Suchfeld, [temp_1 BIOMASS TEST].Field36, [temp_1 BIOMASS TEST].Field37, [temp_1 BIOMASS TEST].Field38, [temp_1 BIOMASS TEST].Field39, [temp_1 BIOMASS TEST].Field40, [temp_1 BIOMASS TEST].Field41, [temp_1 BIOMASS TEST].Field42, [temp_1 BIOMASS TEST].Field43, [temp_1 BIOMASS TEST].Field44, [temp_1 BIOMASS TEST].Field45, [temp_1 BIOMASS TEST].Field46, [temp_1 BIOMASS TEST].Field47, [temp_1 BIOMASS TEST].[Summe AscheOxide]
FROM [temp_1 BIOMASS TEST], [temp_2 BIOMASS TEST mod 1];

and temp_2 (sheet 2, the modified version of sheet 1):


INSERT INTO [bio_Element Oxides] ( [Sample Name], SiO2, Fe2O3, Al2O3, CaO, MgO, Na2O, K2O, SO3, MnO, BaO, TiO2, P2O5, [Summe Asche Oxide] )
SELECT [temp_2 BIOMASS TEST mod 1].Suchfeld, [temp_2 BIOMASS TEST mod 1].Field36, [temp_2 BIOMASS TEST mod 1].Field37, [temp_2 BIOMASS TEST mod 1].Field38, [temp_2 BIOMASS TEST mod 1].Field39, [temp_2 BIOMASS TEST mod 1].Field40, [temp_2 BIOMASS TEST mod 1].Field41, [temp_2 BIOMASS TEST mod 1].Field42, [temp_2 BIOMASS TEST mod 1].Field43, [temp_2 BIOMASS TEST mod 1].Field44, [temp_2 BIOMASS TEST mod 1].Field45, [temp_2 BIOMASS TEST mod 1].Field46, [temp_2 BIOMASS TEST mod 1].Field47, [temp_2 IOMASS TEST mod 1].[Summe AscheOxide]
FROM [temp_1 BIOMASS TEST], [temp_2 BIOMASS TEST mod 1];

Sometimes when I append temp_1 it correctly only transfers 19 records, other times it does 361... same with temp_2. Just FYI, 19x19 is 361. Here is the temp_1 design veiw info from field 36 (and all other number fields) just incase thats relevant:

Field Size: double
Format: General Number
Decimal places: auto
Required: No
Indexed: No
Text align: general

all others are blank

As for the last question, all the excel files are identical as of now, but in the future I will probably have slightly different ones where the SQL will need to be modified so it can find the appropriate cells. My biggest problem right now is that it is making 361 records instead of 19, and also that I need to change the table in the query for each field every time I want to import an new dataset, whereas i would greatly prefer to switch them all at once. Thanks!!!

 

[ishtanbul]

PROBLEM SOLVED! well mostly, apparently you can't have two source tables in the little holding area (in append query design view) even if just one of them is selected below, because access will decide to use the data from the selected table but multiply the number of records produced by the number of records in the unselected table. In my case, the two tables were identical except that they held unique data, so it was doing 19x19 = 361. Removing one of these tables (the one not actaully in use) or linking the primary keys solves this problem. Still can't update the table for all fields at once though... Anyone know a quick way to do that?