.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Importing Excel 2003 and 2007 formats on a 64 bit platform

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

The biggest challenge I’ve had in a long time is trying to import Excel spreadsheets into a staging table. Ridiculous you say?   I would agree. The ultimate production box is 64 bit Windows 2003 server with Sql Server 2008 R2 installed.  I need the package to accept either 2003 or 2007 Excel formats.  The sheets to import all have identical column names but the columns contain disparate data. Meaning sometimes a specific column has text and sometimes it has numerical data. I have zero control over that particular aspect.  I don’t want the driver to take it’s best guess as to the data format, I just want to stage the data into a Sql Server table with  nvarchar(255) columns.

 Yes I have the Microsoft.ACE.OLEDB.12.0 driver loaded on the server and yes I have tried adding the IMEX=1 to the connection string.

  I have tried running the import via a stored procedure and for the m

View Complete Post

More Related Resource Links

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message



I'm trying to import (Custom Lists >> Import Spreadsheet) into WSS 3.0 and I'm getting the following message: 

Refers to the _layouts

You are not authorized to view this page.  You might not have permissions to view this direcotyr or page using the credentials you supplied. [More stuff here.]

Http ERror 403 - Forbidden

Is this just a permissions problem or is there some other underlying issue?  Should you be able to upload an Excel spreadsheet (with links) into a Custom List?


Thanks! Patti N.

MOSS 2007 and Word/Excel 2003

Hi, I am working with MOSS 2007 and building a context webpart which contains links to various places. One of the the links is trying to go to a Bookmark in a Word 2003 document. Upon attempting this it only goes to the top of the document and not to the expected bookmark location. I then tried to define a named range in an Excel 2003 file and had Sharepoint point to the named range. The same behavior occurred, it simply went to the top of the document. Here is a code snippet which points to the excel name range called Base: <A href="http://tserver4/sites/HALBI/GON/Shared%20Documents/Parts.xls#Base"> Does anyone know of any issues with this scenario? Thanks, Larry          

Trouble importing strange column format report from Excel 2007 to SQL 08

Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY

Importing Excel 2007 data into SQL 2005 database

I have a SQL 2005 cluster installation without SSIS and without management studio (I didn't install it). Now we need to import data from an excel 2007 file into a specific table in a database. I have tried doing this from a remote management studio, BI development studio and DTSwizard but I can't seem to make it work. The error message from BIDS is that "the data source is on a remote computer..." Does any of you have a step by step  guide that works from a remote location. BR Rasmus    

How to Convert Excel 2003 (.xls) format to Excel 2007 (.xlsx) programmatically in asp.net


Hi everybody

I am using a function to save dataset tables to excel files (some xml method http://www.codeproject.com/info/search.aspx?artkw=xls+to+xlsx&sbo=kw ) in xls format(excel 2003) but the excel file which is created taking 10 times more file size than conventional one.

And after that when I am converting to excel 2007 format I am getting the normal size for that file.

Now can anyone tell me how to convert excel 2003 file format to excel 2007 file format programmatically through asp.net

Thanks everyone in advance.


Has anybody experienced problems opening 'protected Excel 2007 worksheets in SPS 2003?


I have added the doc icons and MIME types to my SPS2003 farm and now it works properly with Office2007 documents, with the exception of 'protected' Excel files.  In those cases where the Excel file contains a 'protected' worksheet, the user can open the doc, unprotect the worksheet and make their edits.  When they 'protect' the worksheet again and try to save the file, they get an alert that the destination file is 'read-only' and they must save it with a different name.

This only happens for 'protected' Excel 2007 docs residing on an SPS2003 site.  Protected Excel 2003 documents work fine, as do unprotected documents.  Any help y'all can be is greatly appreciated.  I only have two users who are using the protected worksheets, but they are the most vocal of my users.

Importing from Excel - Cannot get SQL to Recognize Column Formats


I am trying create a table in SQL using only 5 columns of data from a large spreadsheet.  I am using the import wizard in SQL and attempting to write a query to only pull in those 5 columns.  I keep getting an error related to file type conversions (see below) for each column I'm trying to bring in.  Any ideas or suggestions?  I tried formatting my excel sheet to be 'text' and to be 'general' but it didn't help.  I tried VARCHAR and TEXT as my destination types without success.  Thanks!!

Source Column       Source Type    Destination Column       Destination Type

DE_H                            12            DE_H                            VARCHAR

Excel 2007




I want to develop an application which supports Server Side Excel Automation using a template(xltx). I am able to acheive most of the automation except(using excelpackage.dll - OfficeOpenXml), i am stuck up identifying the checkbox controls in my excel work sheet.

Any help on this is really appreciated.


My sample code.




display data in excel 2007


 I am using .net  version 1.1  and  excel 2003 to display data.I need to display data in 2007 .Can  anyone suggest the reference to be added ,connection string change and what should be imported.  

In sharepoint excel working fine in office 2007 but not in office 2010


Hi All,

we receive error  while trying to create excel sheet using the template we have uploaded on the SharePoint 2007.


This occurs on a machine that has Office 2010 installed. Earlier the machine had Office 2007 and everything was working fine.


Can you let me know why is problem is coming and how to resolve this.



Sanjeev Tiwari


Sharepoint 2007 and Excel 2010 features


Does Sharepoint 2007 Server support the new Excel 2010 feaures like slicers? Or do i have to have Sharepoint 2010 to get that working (considering all other components besides Sharepoint 2010 are available in my Sharepoint 2007 environment) ?

- Thanks



Video: Connecting PivotTables in Excel 2007 to Data Stored in SharePoint Lists

Learn how to use the Open XML SDK 2.0 to create Excel 2007 PivotTables that are connected to data stored in SharePoint lists.

Interacting with the Excel Web Services API for SharePoint Server 2007

Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Importing SharePoint List Data into Project Server 2007 Custom Fields

Learn how to use the programmability features of Project Server 2007 and Windows SharePoint Services 3.0 to import SharePoint list data into an enterprise custom field.

Publishing Excel 2007 Workbooks to SharePoint Server 2007 (Visual How To)

Watch the video and explore code as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Sample: Publishing Excel 2007 Workbooks to SharePoint Server 2007

Explore the code in this visual how-to article as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend