Reading from Excel with Dynamic columns

narasimha · Post by **narasimha** » Tue Jun 28, 2005 11:41 am

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....

cecilia · Post by **cecilia** » Tue Jun 28, 2005 12:19 pm

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

narasimha · Post by **narasimha** » Tue Jun 28, 2005 2:13 pm

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?

cecilia · Post by **cecilia** » Tue Jun 28, 2005 2:40 pm

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

narasimha · Post by **narasimha** » Tue Jun 28, 2005 7:01 pm

I got a better way to read any excel and writing to a table(dynamically), by passing columns as paramenters...
I did not have to use pivot stage or any routines

Thanks for your suggestion though..

ray.wurlod · Post by **ray.wurlod** » Tue Jun 28, 2005 11:29 pm

Care to share?
Or, at the very least, to post the URL where you found the technique?

narasimha · Post by **narasimha** » Wed Jun 29, 2005 1:22 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 29, 2005 3:28 am

Nice one! :D

Rubu · Post by **Rubu** » Wed Jun 29, 2005 4:24 am

narasimha 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.

Hi narasimha

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

narasimha · Post by **narasimha** » Wed Jun 29, 2005 10:19 am

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$] "

ray.wurlod · Post by **ray.wurlod** » Wed Jun 29, 2005 6:35 pm

Do NOT use a user DSN. DataStage does not read them. This is deliberate and documented.

DSXchange

Reading from Excel with Dynamic columns

Reading from Excel with Dynamic columns

Re: Reading from Excel with Dynamic columns