.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

Convert excel rows wise data into column wise

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :WPF

public static string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

               OleDbDataAdapter adapter = new OleDbDataAdapter();

            OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]");

            OleDbConnection connection = new OleDbConnection(connectionString);

            selectCommand.Connection = connection;

            adapter.SelectCommand = selectCommand;

            DataSet cities = new DataSet();


View Complete Post

More Related Resource Links

How to insert column wise data as a seperate rows using ssis


Hi All,

I have my data in excel like this ....


Convert excel rows into column


 this is the data into excel sheet. i have excel sheet like following way. i want to upload and when it download so it should be following way.

REG_NO   2222    
Name   xyz
ADDRESS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx      
PHONE   3333333333      
Description asasdfasfasfasdfasdf      

How to convert a Excel file data into XML fastly

Hi all,        I have a excel file thats having content of the persons details.My code is as follows.Approximately the Excel sheet having 10,000 records   Form Load  ======= Conn = new OleDbConnection(ConnectionString);             try            {                if (Conn.State == ConnectionState.Closed)                    Conn.Open();                 string sql = "SELECT * FROM [Sheet1$]";                Cmd = new OleDbCommand(sql, Conn);                 DataAdapter.SelectCommand = Cmd;                                DataAdapter.Fill(ds, "Import_XML");                Rec_count = ds.Tables["Import

need to join two tables and also convert top 3 rows to column


Hi, I have two tables, CUSTOMER and PAYMENT_DETAILS. I need to extract "name" and "phone" from CUSTOMER table and TOP 2 payments for matching email from PAYMENT_DETAILS table. Kindly refer to the attached image for table structure and Result required.




Column Name - cannot convert between unicode & nonunicode string data types



Im using the OLEDB Source to connect the source columns. But its throwing an error msg Column Name - cannot convert between unicode & nonunicode string data types. Could you please provide me the oracle code so that I can use with the select statement. For some of the schemas I have chaged the data types using the derived column before the OLEDB destination.

Actually, one of my team member suggested me to use the Oracle char(2) in the select statement. Can any one help me with the code so that I can use it for the rest of the columns.

Eg source(External Column) - CreatedBYID - DT_wstr(18)

Source - Output cloumn - CreatedById - DTStr(18)



Convert NVARCHAR column data into DATETIME - SQL Server 2000


ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.



SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
		CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired


Your help is greatly appreciated. Spent hours to get this resolved.


Union all in SSIS join two tables row wise or column wise

Union all in SSIS join two tables row wise or column wise

Convert MS Excel to XML and then insert all data records into MS SQL



I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.


Extract XML data fro Excel Column

  • I have the following xml data in excel column, how do i extract data from it to a table using ssis.

    Column name XT_BODY

    <SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP></_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421 </DEVID><SOURCEID>RPCC</SOURCEID></SendData>

Exctract XML Data from Excel Column


I have the following xml data in excel column, how do i extract data from it to a table using ssis.

Column name XT_BODY

<SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP></_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421 </DEVID><SOURCEID>RPCC</SOURCEID></SendData>

issue with excel data column



i have a excel sheet (excel 2003) and one of the column doesn't have value in all the rows. some of the rows have value and it is integer. when i use this sheet to import, in external column, it is showing data type as unicode string, i manually changed the data type of output column to different integer types but it doesn't shows any data.

if i go back to excel sheet, change the numeric value into text (add quote next to number '999999 instead 99999) it shows me the value. what is the best method to work with this issue.

one thing is for sure, if this column will have value that will be always integer.

any help,




mark it as answer if it answered your question :)

Excel Data Mining Plug-in (2005) - Analyze Key Influencers - What happens when target column is incl


When I click Analyze Key Influencers I am able to select a column to analyze for key factors and then picks columns to be used for analysis.  What happens when I include the column to analyze for key factors in my list of columns used for analysis?  Based on testing it doesnt seem to matter very much, but i wanted to check what happens behind the scenes in the statical processes. Thank you.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

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.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

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