Reading from Excel with Dynamic columns
Moderators: chulett, rschirm, roy
Reading from Excel with Dynamic columns
Hi
I am able to read information from an excel document and put it into a table in SQL Server database.
My bottle neck is that at anytime i have 6 colums in the excel, but there may or may not be 10 other custom colums whose name is decided dynamically based on the customer's input.
I get the names of these colums either from an xml or a sequential file.
Right now i am able to import the metadata of the xls file and create jobs both by ODBC and OLEDB methods.
How do we dynamically handle the input rows from an excel and load the same to a table in SQL Server....
I am able to read information from an excel document and put it into a table in SQL Server database.
My bottle neck is that at anytime i have 6 colums in the excel, but there may or may not be 10 other custom colums whose name is decided dynamically based on the customer's input.
I get the names of these colums either from an xml or a sequential file.
Right now i am able to import the metadata of the xls file and create jobs both by ODBC and OLEDB methods.
How do we dynamically handle the input rows from an excel and load the same to a table in SQL Server....
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
Re: Reading from Excel with Dynamic columns
Hi Narasimha
Are you using Pivot stage to read each row from Excel and load it to SQL Server?
When I had to do what I "guess" you are doing, I always should use Pivot stage. In that cases I needed the max number of columns, but didn't need its names.
Could you please be more specific?
Regards
Are you using Pivot stage to read each row from Excel and load it to SQL Server?
When I had to do what I "guess" you are doing, I always should use Pivot stage. In that cases I needed the max number of columns, but didn't need its names.
Could you please be more specific?
Regards
I am using ODBC Stage to read the Excel file and an Odbc Stage to create and populate the table in SQL Server.
The issue the is that the row names and the number of rows can change from customer to customer...
I know my max number of rows. (16). I dont care if i am passing null values into the remaining of the 16.
I have not tried the pivot stage for this, would give it a try. Can you tell me how you overcame this situation?
The issue the is that the row names and the number of rows can change from customer to customer...
I know my max number of rows. (16). I dont care if i am passing null values into the remaining of the 16.
I have not tried the pivot stage for this, would give it a try. Can you tell me how you overcame this situation?
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
The idea is to use Pivot stage (it's well documented in on-line help and manuals). In the Transform that follows to Pivot stage you can use MOD function to get each value and a Routin like I wrote below to assign specifics values (like column names) for each column.
Sorry I can't develop all the procedure for you. Please make some exercises with these tips and let me know what else are you needing.
EQUATE NroFila TO Arg1
Pos = Mod(NroFila,16)
Begin Case
Case Pos = 1
Ans = 'ERLOS'
Case Pos = 2
Ans = 'FERTF'
Case Pos = 3
Ans = 'FERTP'
Case Pos = 4
Ans = 'RSTKF'
Case Pos = 5
Ans = 'VVFLE'
Case Pos = 6
Ans = 'VVBON'
Case Pos = 7
Ans = 'VVCOS'
Case Pos = 8
Ans = 'VVDIS'
Case Pos = 9
Ans = 'VVFAB'
Case Pos = 10
Ans = 'VVFAM'
Case Pos = 11
Ans = 'VVIBR'
Case Pos = 12
Ans = 'VVINC'
Case Pos = 13
Ans = 'VVREE'
Case Pos = 14
Ans = 'VVREC'
Case Pos = 15
Ans = 'VV750'
Case Pos = 0
Ans = 'VVIFC'
End Case
Sorry I can't develop all the procedure for you. Please make some exercises with these tips and let me know what else are you needing.
EQUATE NroFila TO Arg1
Pos = Mod(NroFila,16)
Begin Case
Case Pos = 1
Ans = 'ERLOS'
Case Pos = 2
Ans = 'FERTF'
Case Pos = 3
Ans = 'FERTP'
Case Pos = 4
Ans = 'RSTKF'
Case Pos = 5
Ans = 'VVFLE'
Case Pos = 6
Ans = 'VVBON'
Case Pos = 7
Ans = 'VVCOS'
Case Pos = 8
Ans = 'VVDIS'
Case Pos = 9
Ans = 'VVFAB'
Case Pos = 10
Ans = 'VVFAM'
Case Pos = 11
Ans = 'VVIBR'
Case Pos = 12
Ans = 'VVINC'
Case Pos = 13
Ans = 'VVREE'
Case Pos = 14
Ans = 'VVREC'
Case Pos = 15
Ans = 'VV750'
Case Pos = 0
Ans = 'VVIFC'
End Case
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This might not be the generalized approach, but it is serving my purpose.
Problem
Have to read from an excel to a flat file or a table.
The tough part is that, the names of the columns are different for different customers, but we know that we have a maximum of 16 columns.
Solution
Establishing an ODBC connection to an excel file which in my case will have 16 dummy column names.
Create 16 job parameters C1,C2..etc..
Rename the columns in the odbc stage to C1,C2..etc
Replace "`Sheet1$`.`Dummy Column Name1`" with "`Sheet1`.`#C1#`"in the User-defined SQL query
With this approach, I am able to read excel sheets with changing column names.
Problem
Have to read from an excel to a flat file or a table.
The tough part is that, the names of the columns are different for different customers, but we know that we have a maximum of 16 columns.
Solution
Establishing an ODBC connection to an excel file which in my case will have 16 dummy column names.
Create 16 job parameters C1,C2..etc..
Rename the columns in the odbc stage to C1,C2..etc
Replace "`Sheet1$`.`Dummy Column Name1`" with "`Sheet1`.`#C1#`"in the User-defined SQL query
With this approach, I am able to read excel sheets with changing column names.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi narasimhanarasimha wrote:This might not be the generalized approach, but it is serving my purpose.
Problem
Have to read from an excel to a flat file or a table.
The tough part is that, the names of the columns are different for different customers, but we know that we have a maximum of 16 columns.
Solution
Establishing an ODBC connection to an excel file which in my case will have 16 dummy column names.
Create 16 job parameters C1,C2..etc..
Rename the columns in the odbc stage to C1,C2..etc
Replace "`Sheet1$`.`Dummy Column Name1`" with "`Sheet1`.`#C1#`"in the User-defined SQL query
With this approach, I am able to read excel sheets with changing column names.
Could you please explain how we can use to connect an xl file using ODBC. It would be a great help to me...
Regards
rubu
You can connect to an excel either through an ODBC connection or an OLEDB connection.
ODBC Stage-Create a system DSN using Microsoft Excel Driver (*.xls)
-Select a Workbook.
-Import into datastage usinf ODBC Table Definitions. (You have to check the "Include System Tables", because internally it takes the sheets as tables)
-Select the Sheet$
-You should be able to see the data in the excel from the "view data"
OLEDB Stage
- Use the connection String "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;Database=c:\Excel\manifest2.xls;[Sheet1$] "
ODBC Stage-Create a system DSN using Microsoft Excel Driver (*.xls)
-Select a Workbook.
-Import into datastage usinf ODBC Table Definitions. (You have to check the "Include System Tables", because internally it takes the sheets as tables)
-Select the Sheet$
-You should be able to see the data in the excel from the "view data"
OLEDB Stage
- Use the connection String "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;Database=c:\Excel\manifest2.xls;[Sheet1$] "
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: