Showing posts with label spreadsheet. Show all posts
Showing posts with label spreadsheet. Show all posts

Friday, March 9, 2012

Problem with DTS reading from spreadsheet

Has anyone encountered an issue with blank cells when DTS tries to read
a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I have
a spreadsheet where I read a range, and some of the cells show up as blank
even though there are values in them. If I alter the cell and resave the
sheet, it works fine. I suppose this could be worksheet corruption, but I
wanted to see if there were any other ideas about what to do. Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
Are you seeing this?
Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)
Allan
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:

> Has anyone encountered an issue with blank cells when DTS tries to read
> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
> have
> a spreadsheet where I read a range, and some of the cells show up as
> blank
> even though there are values in them. If I alter the cell and resave
> the
> sheet, it works fine. I suppose this could be worksheet corruption, but
> I
> wanted to see if there were any other ideas about what to do. Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
|||Yes, thank you! It is amazing. This was driving me nuts. Excel will read
in the character data, the character mixed with number data, and ignore the
number data.
Your website is one of the best SQL sites out there. The looping example
alone was a lifesaver.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEUNVTM5FHA.3496@.TK2MSFTNGP10.phx.gbl...
> Are you seeing this?
> Excel Inserts Null Values
> (http://www.sqldts.com/default.aspx?254)
>
> Allan
> "Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
> news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
>

Problem with DTS reading from spreadsheet

Has anyone encountered an issue with blank cells when DTS tries to read
a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I have
a spreadsheet where I read a range, and some of the cells show up as blank
even though there are values in them. If I alter the cell and resave the
sheet, it works fine. I suppose this could be worksheet corruption, but I
wanted to see if there were any other ideas about what to do. Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
Are you seeing this?
Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)
Allan
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:

> Has anyone encountered an issue with blank cells when DTS tries to read
> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
> have
> a spreadsheet where I read a range, and some of the cells show up as
> blank
> even though there are values in them. If I alter the cell and resave
> the
> sheet, it works fine. I suppose this could be worksheet corruption, but
> I
> wanted to see if there were any other ideas about what to do. Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
|||Yes, thank you! It is amazing. This was driving me nuts. Excel will read
in the character data, the character mixed with number data, and ignore the
number data.
Your website is one of the best SQL sites out there. The looping example
alone was a lifesaver.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEUNVTM5FHA.3496@.TK2MSFTNGP10.phx.gbl...
> Are you seeing this?
> Excel Inserts Null Values
> (http://www.sqldts.com/default.aspx?254)
>
> Allan
> "Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
> news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
>

Problem with DTS reading from spreadsheet

Has anyone encountered an issue with blank cells when DTS tries to read
a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
have
a spreadsheet where I read a range, and some of the cells show up as blank
even though there are values in them. If I alter the cell and resave the
sheet, it works fine. I suppose this could be worksheet corruption, but I
wanted to see if there were any other ideas about what to do. Thanks.
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********Are you seeing this?
Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)
Allan
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:

> Has anyone encountered an issue with blank cells when DTS tries to read
> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query?
I
> have
> a spreadsheet where I read a range, and some of the cells show up as
> blank
> even though there are values in them. If I alter the cell and resave
> the
> sheet, it works fine. I suppose this could be worksheet corruption, but
> I
> wanted to see if there were any other ideas about what to do. Thanks.
>
> --
> ****************************************
*********
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> ****************************************
*********|||Yes, thank you! It is amazing. This was driving me nuts. Excel will read
in the character data, the character mixed with number data, and ignore the
number data.
Your website is one of the best SQL sites out there. The looping example
alone was a lifesaver.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEUNVTM5FHA.3496@.TK2MSFTNGP10.phx.gbl...
> Are you seeing this?
> Excel Inserts Null Values
> (http://www.sqldts.com/default.aspx?254)
>
> Allan
> "Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
> news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
>
>

Problem with DTS reading from spreadsheet

Has anyone encountered an issue with blank cells when DTS tries to read
a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I have
a spreadsheet where I read a range, and some of the cells show up as blank
even though there are values in them. If I alter the cell and resave the
sheet, it works fine. I suppose this could be worksheet corruption, but I
wanted to see if there were any other ideas about what to do. Thanks.
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************Are you seeing this?
Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)
Allan
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
> Has anyone encountered an issue with blank cells when DTS tries to read
> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
> have
> a spreadsheet where I read a range, and some of the cells show up as
> blank
> even though there are values in them. If I alter the cell and resave
> the
> sheet, it works fine. I suppose this could be worksheet corruption, but
> I
> wanted to see if there were any other ideas about what to do. Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************|||no IT JUST DOESNT FUCKING WORK.
THATS ONE OF THE REASONS I FUCKING HATE EXCEL SO MUCH
SHIT DOESNT WORK LIKE IT SAYS IT SHOULD
just the basics; microsoft
start fucking fixing bugs you piece of shit company
oh.. is $70bn of OUR hard-earned money not enough to fix a half dozen
bugs?
MS is too fat and lazy to succeed; plz boycott MS everywhere
they just dont fucking get it anymore|||Yes, thank you! It is amazing. This was driving me nuts. Excel will read
in the character data, the character mixed with number data, and ignore the
number data.
Your website is one of the best SQL sites out there. The looping example
alone was a lifesaver.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEUNVTM5FHA.3496@.TK2MSFTNGP10.phx.gbl...
> Are you seeing this?
> Excel Inserts Null Values
> (http://www.sqldts.com/default.aspx?254)
>
> Allan
> "Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
> news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
>> Has anyone encountered an issue with blank cells when DTS tries to read
>> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
>> have
>> a spreadsheet where I read a range, and some of the cells show up as
>> blank
>> even though there are values in them. If I alter the cell and resave
>> the
>> sheet, it works fine. I suppose this could be worksheet corruption, but
>> I
>> wanted to see if there were any other ideas about what to do. Thanks.
>>
>> --
>> *************************************************
>> Andy S.
>> andymcdba1@.noreply.yahoo.com
>> Please remove "noreply" before replying.
>> *************************************************
>

Monday, February 20, 2012

Problem with Date Output in Excel 2007

When we export data mining output including dates through Reporting Services to an Excel spreadsheet, Excel 2007 subtracts 4 years from the dates. Example, the date 11/16/2006 appears in Excel 2007 as 11/16/2002. How should this be handled? Workaround, bug fix, patch,etc.? Thanks, Sam

Sounds very weird to me.

The data renders correctly in Reporting Services, but incorrectly when exported?

Does this only happen with reports built over DMX queries? Do other reports, using SQL or MDX, render correctly in Reporting Services and in Excel?

thanks for any additional clarification you can give.

|||

Thanks, Don. The dates appear correct in the output through Reporting Services that we use as a Front End. When we export to Excel, it comes into Excel 2007 as 4 years earlier. Are the dates in Excel 2007 based on 1900 rather than 1904, and could that be the source of the problem? I will check with the developers on Tuesday with the answer to your other questions about MDX, etc. and will experiment with my SQL queries to see how Excel 2007 handles them so we can figure out if it is an Analysis Services export problem or an Excel 2007 problem.

Sam

|||

Don, Excel 2007 has no trouble bringing in dates from Excel 2003 spreadsheets or from the text output of SQL queries.

I posed your question to our developer. His reply:

Exporting to Excel or any other form is a built-in feature of Microsoft reporting services.I guess it has nothing to do with the way data is generated for the report.Moreover reports are accessing data from SQL server tables and not from the MDX/DMX.

The sequence is data is pumped from analysis server to SQL server table by using the DMX queries .Reports access data from these tables.

I hope this is helpful information--it would be surprising if no one else is having this difficulty witht the built-in export from Microsoft reporting services. Does this provide you with sufficient information to attempt to replicate the problem?

Sam

|||

Thanks. Let's see if we can narrow it down further. (We cannot reproduce the problem here, so I'm sorry but we have to ask you quite a few of these questions to get to the root of the matter.)

Are the results of the DMX queries correct when stored in SQL Server? If yes, and the results are correct when viewed in the report, then I would conclude that the problem is in the Reporting Services Excel export functionality. In that case, we would move this thread to the reporting services forum. (We have already alerted them to issue.) It would not be related to the data mining functionality.

However, if the results stored in the SQL Server are wrong, then it may well be an issue somewhere with the DMX query and we could look into that further.

hth