Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

Checking KPI value for divide-by-zero

We've got some potentially complex KPI value definitions, which can involve division of two cell values. I want to put in a check to make sure we don't do a divide-by-0. If I do, is there any performance implication due to the calculation of the denomerator happening twice (once in the first IIf clause, again in the actual value). For example, if I have a value that's essentially:

[Measure1] / (Aggregate([A Set], [Measure 2])

and I want to make it

IIf( (Aggregate([A Set], [Measure 2]) > 0, [Measure1] / (Aggregate([A Set], [Measure 2]), Null)

Is there a performance hit for doing this? Does the aggregate happen twice? Is there a smarter way of doing this check?

Yes, Aggregate will happen twice. The best way to handle it is

CREATE HIDDEN AggASet = Aggregate([A Set], [Measure 2])

and then you can say

IIf( AggASet > 0, [Measure1] / AggASet, Null)

|||Thanks Mosha. But can I specify a composite expression like that in a KPI value expression?|||No - the CREATE HIDDEN statement should be somewhere inside the MDX Script.|||Shoot, that's what I was afraid you were going to say. The problem is that these KPIs are being generated by a GUI tool, and so far we've done everything inside the KPI definition itself, without having to muck with the MDX script. Changing that is going to involve some major code changes, sadly.

Is there any other way to handle this condition gracefully?|||

Your choices as I see them are:

1. Do nothing - take perf hit, and hope that in the next version query optimizer will automatically detect such expressions and will do the "right thing"

2. Change your logic to add hidden calculated measures to MDX Script

3. Don't check for 0 in denominator and let users see 1.#INF when such division occurs

4. Create another KPI which will hold the Aggregate expression and reference its through KPIValue function from your KPI expression. This way you won't need to change MDX Script. I am not sure, however, if it is possible to create hidden KPIs. So the drawback is that you will get an extra 'junk' KPI.

5. Something else ?

HTH,

Mosha (http://www.mosha.com/msolap)

Checking for Null Values in a Table/Matrix Cell

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

=iif(fieldname = nothing, 0, fieldname)|||um, wouldn't that be =iif(fieldname = dbnull.value,0,fieldname) ?|||Actually, SSRS converts dbnull to an actual null in the report, so using:

iif(fields!fieldname.value = nothing , 0, fields!fieldname.value)

or

iif(IsNothing(fields!fieldname.value), 0, fields!fieldname.value)

seems to be the MSFT suggested way to trap for nulls.

iif(fields!fieldname.value = dbnull.value, 0, fields!fieldname.value) won't work. You might be able to get away with iif(fields!fieldname.value is system.dbnull.value, 0, fields!fieldname.value), but people have reported some issues with using System.DBNull and all the MSFT guys say to use "nothing".

Checkboxes in Rdl Table

I am looking fo any suggestion how to put a checkbox in a cell in a table on a rdl report. I used the wizard to make a report that has drilldown in it. I want to be able to have a check box in the cell on every row. When I check this box I want a paramater to run a sub report that builds pdfs of information that corresponsed to the data in that rows thats are checked. Any suggestions. I didnt find any tools that add checkboxes to my toolbox on the report builder. I am using vs2005.net C# asp.net

Right Click on the

|||

I am looking to do the exact same thing. Have you had any progress finding the answer?

|||

No I figured out how to do this in a regular ASP page but not a rdl report I have seen it done thought in an example but can find the example on the web. I will let you know if I finf the example.