PowerPoint Viewer cannot open the file

Here is my recent Saga trying to view Microsoft PowerPoint 2007 file saved in Office Open XML format, i.e. pptx.

Prelude:

I work in the Windows world like many others out there and believe in Microsoft products and technologies to most extent, but recent experience in trying to view a pptx file shaken that belief quite a bit. I only have Office 2003 installed on my system and I received a pptx file and needed to view this pptx file.

Journey:

To view this file on my system, I had to jump through few hoops and spend quite a bit of time. Here is my journey (mostly missteps) and hopefully will save you sometime if you run into the same situation:

1) Thinking it would be a simple process of getting the right viewer, I obtained the PowerPoint Viewer from Microsoft website and installed it and then tried to open the file. To my surprise, I get the error "PowerPoint Viewer cannot open the file" ...:



So, I have searched on the web and found the following posting:
http://www.eggheadcafe.com/software/aspnet/31691910/cannot-open-in-viewer.aspx


According to the Lucy Thomson (PowerPoint MVP) who posted a reply to the original question on this page, 2007 Viewer basically is 2003 Viewer combined with converter that converts 2007 files to 2003 format. I unzipped the contents of the pptx file and everything looks fine as far as its contents. So, there must be something wrong with the converter packaged in that viewer.

2) I emailed the file to my gmail account to see if it would display it there, no luck there also. Gmail only supports ppt files.

3) I searched some more and found this tool called Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats. Bingo, now I can open the pptx file using PowerPoint 2003. I am kind of relieved but this is not the end of my saga by any means, read on and you see why.

4) Meanwhile, i.e. while I was searching on Google, I emailed a colleague to see if he was able to open this file on his system. I got the reply saying he was able to open the file but it took longer to open it. He did not give any more details. So, I did little more probing:

• He was also using Office 2003


• Did not have to install any software (I thought he must have had to install some addon or something, since the only way I could view the file is by installing Compatibility Pack)


• Running Office 2003 SP2 (I am also running SP2)

Got me puzzled, then I decided to check his actual version/build # and it turns out that it is slightly higher than my version # even though we both running SP2.

4) So, I thought I would also update my Office 2003 and then I should be able to open the pptx file without any problem. Since I do not like to have any extra software on my system I uninstalled the Compatibility Pack thinking I do not need it.


Now, I updated my Office 2003 to SP3 (that is the only office update choice I had through Windows Update). I figured, it should be OK since I will be getting the latest and greates updates to Office 2003 and I should have no problem opening the pptx files.


So, after I updated to Office 2003 SP3, I tried to open the pptx file and I get the following message asking me if I want to install Compatibility Pack:



I am back to where I started. I hit "No" since I already had the Compatibility Pack downloaded. I installed the "Compatibility Pack" again and now I can open the pptx files.

5) As I have mentioned earlier, I do not like to have any software that I really do not need, so I have uninstalled the Viewer and I could still open the pptx files with my Office 2003 (thanks to Compatibility Pack).

6) You do not necessarily have to have Office 2003 to use the Compatibility Pack, you could also use it with the PowerPoint 2003 Viewer. So, I have installed PowerPoint 2003 Viewer and the Compatibility Pack on a Virtual Machine to see if I can open pptx file using the 2003 viewer and sure enough I could view it.

Lessons Learned

Do not use the PowerPoint Viewer 2007. It is worthless.

If you have Office 2003 installed, then to view/open the pptx files, simply install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

If you do not have Office 2003, then to view the pptx files, first install the PowerPoint 2003 Viewer and then followed by Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

Happy PowerPoint Viewing ...


-Sonny

OPENROWSET and not Text files but Excel files

The other day, I was wondering if you can query against a database that is on a remote server without first having to create a linked server in SQL Server 2005. A co-worker told me that I could use OPENROWSET and then I read the following page on MSDN, which gives a very good overview of what it is and how to use it:

OPENROWSET Help Page on MSDN

Most of the SQL Experts out there already knows about this functionality, but I was very excited to learn about it. I spent quite a bit of time learning about it as it seems very powerful in what it can do, I mean, you can use it to not only query databases on remote server, you could also use it with your text files, Excel spreadsheets etc. Suppose you have textual data that relates to some tables in your database and using OPENROWSET you could turn that data table and use it like any other table.

For example, just let's say you have a business and you have a database similar to Northwind and you received an Excel spreadsheet from one of your consultants that you hired to do a customer satisfaction survey for all your customers in Canada. And your consultant is one of those people, who only works with Excel to do all her tracking. Since your consultant only works with Excel, you exported few columns (CompanyName, ContactTitle, ContactName, Phone) from the Customers table to an Excel file and gave her that file.

You ran the following query to create the Excel file:

SELECT CompanyName, ContactTitle, ContactName, Phone
FROM Customers
WHERE Country = 'Canada'

You got the following rows in the Excel file:



She filled in the satisfaction on a scale of 1 to 10 for last year on quartely basis and optional comments associated with each score. She sent you back the Excel sheet with the following extra columns:




So, now you can join this data as a table in your queries or import into a temporary table using something like this:

SELECT A.* INTO #temp1 FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\CanadaCustomersWithScores.xls','SELECT * FROM [Sheet1$]' ) AS A
WHERE A.CompanyName IS NOT NULL

Assuming the data is in Sheet1 and by saying CompanyName IS NOT NULL we will only include rows that are valid.

Now, you might ask, can I do this with a text file instead of using Excel. Theortically yes you can, at least, according to the help page listed above. However, I was not able to get it working using the samples given there plus another annoying thing about using a text file is that you need to have a format file. It seems like you would have better flexibility using an Excel file, plus you can easily edit the data in Excel and the OPENROWSET seems to work without must fuss.

Another thing to note here is that, I have used xls files not xlsx file because I could not get to link to xlsx (Excel 2007) files with this provider. My later research revealed to me that I need to use a different provider to open xlsx files, which I listed later on this post. I also wonder if I can use OPENXML on xlsx sheets, which I am going to try it out later.

Speaking of OPENXML, I am planning to explore other OPENSET functions and will post my findings in a future blog:

CONTAINSTABLE
FREETEXTTABLE
OPENDATASOURCE
OPENQUERY
OPENXML

Another thing I had a hardtime trying to find is the typical list of providers as it does not list them in the help page and few searches on the web did not turn up anything. Then later on I stumbled onto the node in Object Explorer in SQL Server Management Studio that lists all the providers that are registered on my system. To see the list on your SQL Server instance:

Expand the Server Objects node > Linked Server > Providers.

You don't have SQL Server installed, but you still would like to know all the OLE DB Providers registered on your system, you could try the following C# code:

Guid guidOleDbEnum = new Guid("{C8B522D0-5CF3-11ce-ADE5-00AA0044773D}");

Type type = Type.GetTypeFromCLSID(guidOleDbEnum);

using (OleDbDataReader rdr = OleDbEnumerator.GetEnumerator(type))
{

while (rdr.Read())

Console.WriteLine("{0} - {1}", rdr["SOURCES_NAME"], rdr["SOURCES_DESCRIPTION"]);

}

Here is the list of providers that I see on my SQL Server instance:

ADsDSOObject - OLE DB Provider for Active Directory Services
DTSPackageDSO - OLE DB Provider for DTS Package Decision Support Object
Microsoft.ACE.OLDDB.12.0 - OLE DB Provider for Microsoft Access/Excel 2007
Microsoft.Jet.OLDDB.4.0 - Microsoft Jet OLE DB Provider for ISAM Database, e.g. Access/Excel 2003, dBase III, Lotus WK3, Paradox 5.x etc.
MSDAIPP.DSO - OLE DB Provider for Microsoft Internet Publishing Provider
MSDAORA - Microsoft OLE DB Provider for Oracle
MSDAOSP - Microsoft OLE DB Simple Provider
MSDASQL - Microsoft OLE DB Provider for ODBC
MSDMine - Microsoft OLE DB Provider for Data Mining Services
MSIDXS - Microsoft OLE DB Provider for Index Server
MSOLAP - Microsoft OLE DB Provider for OLAP Services (or Analysis Services)
SQLNCLI - Microsoft SQL Native Client OLE DB provider
SQLOLEDB - Microsoft OLE DB Provider for SQL Server 2000
SQLReplication.OLEDB - Microsoft SQL Server Replication OLE DB Provider for DTS
SQLXMLOLEDB.4.0 - OLE DB provider that exposes Microsoft SQLXML functionality through ActiveX Data Objects (ADO)

I do not think we can use all of these with OPENROWSET function, I would have to investigate as to which ones can be used and which one can't be used. I kind of feel bad to leave so many questions unanswered in this post, but at the same time, it is giving me an opportunity to learn more about this area and share it with my blog readers.

I feel like I am just scratching the surface and there is a lot to learn in this area.

I would love to hear from you what you think about the OPENROWSET function and the related information and any of your experiences in using it.

Thanks for reading.

Best Practices for Convert Class

1) Considered the following usage:
string userID = db.ExecuteScalar(dbCommand).ToString();

Better approach might be to use the Convert class
string userID = Convert.ToString(db.ExecuteScalar(dbCommand));

2) Considered the following usage:
if (dr["someColBool"] != null && dr["someColBool"].ToString().Length > 0 && Convert.ToBoolean(dr["someColBool"]) == true) continue;

Remove the unnecessary code and use one of the approaches:
if (Convert.ToBoolean(dr["someColBool"]) == true) continue;

or

if (Convert.ToBoolean(dr["someColBool"])) continue;

The above code works even if we have a null value for column "someColBool" in the DataRow instance. A null argument is a valid argument to various static convert methods on the Convert class, however, you can not pass empty string because it can't properly parse empty string.
When a null argument is passed it returns the default value for that value type.

3) Using Exceptions to catch invalid conversions
try
{
tbCurrentSomeColDate.Text = Convert.ToDateTime(dsTopSection.Tables[0].Rows[0]["someColDate"]).ToShortDateString();
}
catch (Exception)
{
Not a valid date
tbCurrentSomeColDate.Text = "";
}

General rule of thumb is to not to cast a wide net to catch all Exceptions if you can determine
during design time what exceptions might occur then put the code in place so that those exceptions would not thrown and only catch those exceptions that cannot be predetermined.

Another guideline is that if we can avoid causing an exception to be thrown, use that code instead of putting the code in a try ... catch statement.

A better approach might be to use the following code:

Object date = dsTopSection.Tables[0].Rows[0]["someColDate"];
DateTime dt;
if (date != null && DateTime.TryParse(date.ToString(), out dt))
tbCurrentSomeColDate.Text = dt.ToShortDateString();
else
tbCurrentSomeColDate.Text = "";

I am sure there are other better ways to use the Convert class and I would love to hear from the readers what are different ways they use the Convert class.

Happy Coding!