Normalizing import

[Lars Brownies]

I'm writing an import routine that regularly imports excelfiles into an
Access file. One of the Excel files is not normalized and doesn't have a
fixed number of columns. The number of columns can occasionally grow over
time by one. The table look like this:
Person Right1 Right2 Right3
A X X
B X

I'd like to normalize this table like:
Person Rights
A Right1
A Right3
B Right2
soo that I doesn't have to change my Access table when a new colun is added
in the Excel file.

Is there some special trick or routine to do this?

Thanks in advance,
Lars

 

[Douglas J. Steele]

Assuming you're just looking to normalize the existing table, use a Union
query:

SELECT Person, "Right1" AS Rights
FROM MyTable
WHERE Right1 = "X"
UNION
SELECT Person, "Right2"
FROM MyTable
WHERE Right2 = "X"
UNION
SELECT Person, "Right3"
FROM MyTable
WHERE Right3 = "X"
UNION
....
UNION
SELECT Person, "Rightn"
FROM MyTable
WHERE Rightn = "X"

 

[Lars Brownies]

Thanks,
The routine should always work. Are you suggesting I should first count the
number of valid columns and then build my union query string (like you
suggested) based on that? Or did you have something else in mind?

Lars

 

[Lars Brownies]

Another problem is that I don't know the values of "Rightn" beforehand.
These column names can be any name.

Lars

 

[Jerry Whittle]

Here's what will work with your example data once you put in the proper table
name:

SELECT PersonRights.Person, "Right1" AS Rights
FROM PersonRights
WHERE PersonRights.Right1 Is Not Null
UNION ALL
SELECT PersonRights.Person, "Right2" AS Rights
FROM PersonRights
WHERE PersonRights.Right2 Is Not Null
UNION ALL
SELECT PersonRights.Person, "Right3" AS Rights
FROM PersonRights
WHERE PersonRights.Right3 Is Not Null
Order by 1,2;

However you said that you don't know how many fields there will be.
Therefore what you need to do is create a table with the same structure as
you described below with the maximum number of Rights fields that you think
possible (up to 255 which is the maximum for Access). Before doing an import,
you will need to delete all records in this table. Next import the
spreadsheed into this table either directly; having the Excel spreadsheet
linked then using an append query; or importing the spreadsheet as it's own
table then doing an append.

Next create a query like above with all the possible Rights. In the example
below, Right4 will not cause an error or be displayed if there isn't any
matching data.

SELECT PersonRights.Person, "Right1" AS Rights
FROM PersonRights
WHERE PersonRights.Right1 Is Not Null
UNION ALL
SELECT PersonRights.Person, "Right2" AS Rights
FROM PersonRights
WHERE PersonRights.Right2 Is Not Null
UNION ALL
SELECT PersonRights.Person, "Right3" AS Rights
FROM PersonRights
WHERE PersonRights.Right3 Is Not Null
UNION ALL
SELECT PersonRights.Person, "Right4" AS Rights
FROM PersonRights
WHERE PersonRights.Right4 Is Not Null
Order by 1,2;

 

[Piet Linden]

I'm writing an import routine that regularly imports excelfiles into an
Access file. One of the Excel files is not normalized and doesn't have a
fixed number of columns. The number of columns can occasionally grow over
time by one. The table look like this:
Person   Right1 Right2 Right3
A        X             X
B               X

I'd like to normalize this table like:
Person   Rights
A        Right1
A        Right3
B        Right2
soo that I doesn't have to change my Access table when a new colun is added
in the Excel file.

Is there some special trick or routine to do this?

Thanks in advance,
Lars

You need to use
Activesheet.Usedrange.Columns.Count in Excel to figure out how many
columns contain data, and then you can loop over them in Excel and
import the data into Access.

 

[Douglas J. Steele]

Whether you're importing the spreadsheet or linking to it, you should be
able to use VBA to generate the UNION query for you dynamically. Something
like:

Dim dbCurr As DAO.Database
Dim tdfInput As DAO.TableDef
Dim qdfUnion As DAO.QueryDef
Dim fldInput As DAO.Field
Dim lngLoop As Long
Dim strFieldName As String
Dim strSQL As String

Set dbCurr = CurrentDb
Set tdfInput = dbCurr.TableDefs("MyTable")
For lngLoop = 1 To (tdfInput.Fields.Count - 1)
strFieldName = tdfInput.Fields(lngLoop).Name
strSQL = strSQL & _
"SELECT Person, """ & strFieldName & " AS Rights " & _
"FROM MyTable " & _
"WHERE " & strFieldName & " = 'X' " & _
"UNION "
Next lngLoop
strSQL = Left(strSQL, Len(strSQL) - 6)

' This assumes that the union query (named qryUnion) already exists.

Set qdfUnion = dbCurr.QueryDefs("qryUnion")
qdfUnion.SQL = strSQL

qdfUnion.Close
Set qdfUnion = Nothing
Set tdfInput = Nothing
Set dbCurr = Nothing

 

[James A. Fortune]

Another problem is that I don't know the values of "Rightn" beforehand.
These column names can be any name.

Lars

There's a limit on the number of UNION ALL statements you can have in a
SQL query. Since you are using VBA already, if the number of fields is
potentially large enough that the number of UNION ALL statements can
become an issue, you can use two recordsets (one a dynaset-type) to
populate your normalized table using a single pass through the data.
That would also allow you to obtain the column names used in your
normalized table entries from the Recordset's Field collection. If you
use the UNION ALL method, creating the SQL string on-the-fly using a
TableDef might be the easiest way to account for the unknown field
names. If you link to an Excel spreadsheet, don't forget to include the
IMEX value in the connection string.

James A. Fortune
(e-mail address removed)

 

[Lars Brownies]

Thanks Douglas and others.
With this info I think I can figure it out.

Lars