Fill gaps in table by importing/appending from excel

  • Thread starter Thread starter FishMan
  • Start date Start date
F

FishMan

HI
I have a table in the following format:

Sample Sequence1 Sequence2
1 aggta gtcct
2 aggct
3 gtcat
4
5 gttag
6 aggat gccta

where sample is the primary key.

There are/will be a few thousand of these records. My problem is that I
want to be able to fill the missing data by importing/appending from an
excel spreadsheet. These missing values are collected in groups of 96
and stored in a spreadsheet (with the same three fields). I want to
then be able to update without having to go through it all by hand. For
example the spread will have:

Sample Sequence1 Sequence2
2 gttag
3 gttagt
4 gaacct gaatg
5 cctta

Please help me to import this new data and fill in the gaps.
Thanks
FishMan
 
may we make the following two assumptions? 1) the Sample field in the table
and in the spreadsheet match - primary key to primary key. 2) you only want
to fill in the Null fields in the table - not overwrite an *existing*
sequence value in the table with data from the spreadsheet.

based on the above assumptions, you can dump the spreadsheet into a "temp"
table, and run two update queries - one for each Sequence field, as

UPDATE tblSamples INNER JOIN tblTemp ON tblSamples.Sample = tblTemp.Sample
SET tblSamples.Sequence1 = [tblTemp].[Sequence1]
WHERE (((tblSamples.Sequence1) Is Null));

UPDATE tblSamples INNER JOIN tblTemp ON tblSamples.Sample = tblTemp.Sample
SET tblSamples.Sequence2 = [tblTemp].[Sequence2]
WHERE (((tblSamples.Sequence2) Is Null));

hth
 
You'll need two update queries, one for Sequence1 and the other for
Sequence2.

If you access the Excel sheet via a linked table, the first will look
like this:

UPDATE MyTable As A INNER JOIN LinkedTable As B
ON A.Sample = B.Sample
SET A.Sequence1 = B.Sequence1
WHERE A.Sequence1 IS NULL;

and the second will be the same except for using Sequence2.

If you have lots of Excel sheets to process, it may be simpler to write
VBA code that builds and executes queries that go directly to the Excel
sheet without bothering with a linked table. The syntax of the queries
will be like this:

UPDATE MyTable As A INNER JOIN
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$] AS B
 
Thanks for the help people, just what was needed, linked table set up
and update queries working fine. Well done.
FishMan
 
Back
Top