If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Normalizing import
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 |
#2
|
|||
|
|||
Normalizing import
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" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... 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 |
#3
|
|||
|
|||
Normalizing import
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 "Douglas J. Steele" schreef in bericht ... 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" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... 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 |
#4
|
|||
|
|||
Normalizing import
Another problem is that I don't know the values of "Rightn" beforehand.
These column names can be any name. Lars "Lars Brownies" schreef in bericht ... 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 "Douglas J. Steele" schreef in bericht ... 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" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... 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 |
#5
|
|||
|
|||
Normalizing import
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; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Lars Brownies" wrote: 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 |
#6
|
|||
|
|||
Normalizing import
On Jul 14, 11:07*am, "Lars Brownies" wrote:
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. |
#7
|
|||
|
|||
Normalizing import
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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... Another problem is that I don't know the values of "Rightn" beforehand. These column names can be any name. Lars "Lars Brownies" schreef in bericht ... 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 "Douglas J. Steele" schreef in bericht ... 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" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... 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 |
#8
|
|||
|
|||
Normalizing import
Lars Brownies wrote:
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 |
#9
|
|||
|
|||
Normalizing import
Thanks Douglas and others.
With this info I think I can figure it out. Lars "Douglas J. Steele" schreef in bericht ... 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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... Another problem is that I don't know the values of "Rightn" beforehand. These column names can be any name. Lars "Lars Brownies" schreef in bericht ... 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 "Douglas J. Steele" schreef in bericht ... 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" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lars Brownies" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|