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

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

SSIS - reading blank rows from Excel 2007 which are actually deleted

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Folks, We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem – 1. Go to the Ribbon, and select the Home tab. 2. In the Editing section, click the small arrow next to Find & Select . 3. Select Go To Special . 4. Select the Blanks radio button and click OK. 5. All blank columns/rows will be highlighted. 6. Delete However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any po

View Complete Post

More Related Resource Links

Reading data from Excel 2007

I am attempting to read data from an uploaded spreadsheet using ACE.OLEDB. The code, which is running fine on dev and test machines for XL2003/2007 reports "External table is not in the expected format" error on connecting on the production server for XL2007 only. The code is Dim connectionString As String = "provider=Microsoft.Ace.OLEDB.12.0;" _ & "Data Source='" & ImportData.FullName & "';Extended Properties=Excel 12.0;" LogWebActivity.LogThis("Entering POPSUKD, ConStr=" & connectionString, LogWebActivity.LogDetailLevel.DetailAndData) Dim con As New System.Data.OleDb.OleDbConnection(connectionString) LogWebActivity.LogThis("Dimmed con", LogWebActivity.LogDetailLevel.Debugging) Try Dim cmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & WorksheetName & "$]", con) Dim adapter As New System.Data.OleDb.OleDbDataAdapter(cmdSelect) Dim dS As New Data.DataSet LogWebActivity.LogThis(cmdSelect.CommandText, LogWebActivity.LogDetailLevel.Debugging) con.Open() LogWebActivity.LogThis("Opened Connection", LogWebActivity.LogDetailLevel.Debugging) adapter.Fill(dS, WorksheetName) LogWebActivity.LogThis("Filled DataAdapter", LogWebActivity.LogDetailLevel.Debugging) _SKUS = dS.Tables(W

Looping through Excel 2007 tables in SSIS?

Saw the (http://msdn.microsoft.com/en-us/library/ms345182(v=SQL.90).aspx) msdn article on MSDN, however I need to loop through Excel 2007 tables, which is not supported via the .Net Connection.  Is there a way to loop through the 2007 sheets without resorting to a script component?Steve Walker

Text truncated to 255 using SSIS ACE.oledbprovider to import excel 2007 spreadsheet

Hi All, Trying to use SSIS SQL 2005 to import an Excel 2007 file (.xlsx). I'm using Office 12.0 Access Database Engine OLEDB Provider with extended properties Excel 12.0;HDR=Yes;IMEX=1 The problem is when I have text with more than 255 characters. It basically defaults the source output to Unicode string[DT_WSTR] 255, If I change it it errors out. I have done the registry change according to http://msdn.microsoft.com/en-us/library/ms141683.aspx but I think it only applies to Excel source not OleDb Source. (A Excel source does give me a text instead of string )   I have also tried to increasing the length of output such as 4000 instead of 255 but it doesn't allow either.   Anybody can help?I'm stuck for a day. Thank you very much for your help. Regards, Koala  

SSIS Excel import skip first rows

Hello,1. Is it possible during import data from Excel to DB table skip first 6 rows for example?2. Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?Thank you,V. A.

SSIS 2005 - Export Data into an Excel 2007 Table


Hello !

I have an excel file (xlsx) containing a table :

Excel Table

Once I launched my ssis task (successfully) to insert data in it, it is actually append after the table :

Excel Table after the SSIS task

So I am looking for a way to insert into the table and expand it with the data. I hope someone could help me.

Thank you !

SSIS package to import excel 2007 with different column numbers



I have an already developed SSIS 2005 package which can import maximum of 50 columns of *.xlsx file. The file format is same except with the column numbers. I have many components in SSIS package (e.g. unpivot data, derrived column etc) which are developed for 50 columns. The package works fine for importing 50 columns. But sometimes it fails for excel sheet having 12 columns (columns < 50) it gives error for the source columns cannot be found. Surprisingly, if the contents are copied to another file it may work but not surely. Please let me know if I can do anything in existing package.

Thanks in advance,

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

Reading excel data in SSIS Script task


I am reading an excel file using OleDbDataReader.

I can read all cells from the sheet. But when I read a string value its returning me a NULL value. If I replace that cell with a Integer value I can get it.

Basically I am doing a validation on the cells to see if there are any non-numeric values. For certain cells which has string value I can get it as string and I can do the validation but for certain others I cant. Its returning as NULL. Any help would be really helpful.




Gridview that exports selected rows to Excel

his seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet.

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




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.  

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.

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



SharePoint 2007 Central Admin - blank page, isapimodule error in IIS


I've got a Windows 2008 SP2 server runing x64.

When i try to access the SharePoint Central Admin site, I just get a blank page. No errors in the Windows logs, nothing showing up in the SharePoint logs. The IIS log shows a 500 error.

I enabled Failed Request Tracing for the 500 status code, which gives me the following information:

ModuleName IsapiModule

Notification 128

HttpStatus 500

HttpReason Internal Server Error

HttpSubStatus 0

ErrorCode 2147942593


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