DaniWeb Forum Index > Software Development > C#

C# RSS

not able read special charactered strings -EXCEL to DataSet-C#

Please support our C# advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Oct 2009

Posts: 11

Reputation: venkates.99 is an unknown quantity at this point

Solved Threads: 0

venkates.99 venkates.99 is offline

Offline

Newbie Poster

not able read special charactered strings -EXCEL to DataSet-C#

26 Days Ago

Hi all,
my code is like..

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=C:\\ifocus\\Excel Worksheet.xls;" +
                "Extended Properties=Excel 8.0;";
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
                myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "ExcelInfo");
string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=C:\\ifocus\\Excel Worksheet.xls;" +
                "Extended Properties=Excel 8.0;";
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
                myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "ExcelInfo");

and My EXCEL data is like
-------------------------------------------------------------------------------------
Gold --- in@GLDM.1 -- 14,917.00-- Gold MCX
--------------------------------------------------------------------------------------
ALUM--- in@ALUM.1 -- 630.00 -- Aluminium MCX

Im gettings values from row as 'Gold' 'EmptySpace' '14,917.00' 'EmptySpace'
I need to get 'in@GLDM.1' & 'Gold MCX' as it is from Excel sheet.
in myDataSet when check for 'in@ALUM.1' data, for that cell it is showing Empty.

Could you Please any body sovle it.

Thanks in Advance...

Last edited by peter_budo; 26 Days Ago at 9:01 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

•

Join Date: Oct 2009

Posts: 31

Reputation: RunTimeError is an unknown quantity at this point

Solved Threads: 3

RunTimeError RunTimeError is offline

Offline

Light Poster

-3

26 Days Ago

I'm assuming its because the SQL is having trouble reading the 'Sheet1$.' Not sure if this will work but try and escape character:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1\$\]", strConn);
 OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1\$\]", strConn);

In some languages special characters such as '$' will be a prefix for the start of a special case but putting escape characters before will make the language treat them as a literal.

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

26 Days Ago

Upload your excel spreadsheet

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2009

Posts: 11

Reputation: venkates.99 is an unknown quantity at this point

Solved Threads: 0

venkates.99 venkates.99 is offline

Offline

Newbie Poster

25 Days Ago

•

Originally Posted by sknake

Upload your excel spreadsheet

hi sknake,

its not working, its causes to compile errors.
Im not using any SQL.
I have uploaed my Excelsheet , Please look into that.

Thanks..

Last edited by venkates.99; 25 Days Ago at 1:51 am.

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

25 Days Ago

I dont see it....

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2009

Posts: 8

Reputation: vksingh24 is an unknown quantity at this point

Solved Threads: 1

vksingh24

Offline

Newbie Poster

25 Days Ago

Hi,

I tryied it with Form and I got the desired result.

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
private void Form1_Load(object sender, EventArgs e)
        {
           OleDbConnection cnn = new OleDbConnection();
           cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\temp\\myData.xls;" + "Extended Properties=Excel 8.0;";
 
           OleDbDataAdapter dap = new OleDbDataAdapter("SELECT * From [Sheet1$]", cnn);
           DataSet ds = new DataSet();
           dap.Fill(ds,"ExcelInfo");
 
           foreach(DataRow d in ds.Tables[0].Rows)
           {
                   MessageBox.Show(d[0].ToString());
                   MessageBox.Show(d[1].ToString());
                   MessageBox.Show(d[2].ToString());
                   MessageBox.Show(d[3].ToString());
 
           }
private void Form1_Load(object sender, EventArgs e)
        {
           OleDbConnection cnn = new OleDbConnection();
           cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\temp\\myData.xls;" + "Extended Properties=Excel 8.0;";

           OleDbDataAdapter dap = new OleDbDataAdapter("SELECT * From [Sheet1$]", cnn);
           DataSet ds = new DataSet();
           dap.Fill(ds,"ExcelInfo");

           foreach(DataRow d in ds.Tables[0].Rows)
           {
                   MessageBox.Show(d[0].ToString());
                   MessageBox.Show(d[1].ToString());
                   MessageBox.Show(d[2].ToString());
                   MessageBox.Show(d[3].ToString());
               
           }

•

Join Date: Aug 2009

Posts: 351

Reputation: Ryshad has a spectacular aura about

Solved Threads: 62

Ryshad

Offline

Posting Whiz

25 Days Ago

what did you change other than variable names?

One thing to watch out for with excel; if you have different data formats in a column it can cause problems. When reading in the data the majority datatype in each column is used. For example, if the first few rows of a column contain numbers, the column is assumed to contain numbers and any non-numeric values will be returned as NULL since they cant be converted to the columns datatype. I had a similar issue with a text column being interpretted as Double and returning nothing but DBNull's : /

Theres a nice breakdown here on how to handle the problem.

Please don't take for granted the work that solvers do for you. Take the time to fully understand the code they give you so that you might adapt it to future problems.

"Learning is more than absorbing facts, it is acquiring understanding.” - William Arthur Ward

•

Join Date: Nov 2009

Posts: 8

Reputation: vksingh24 is an unknown quantity at this point

Solved Threads: 1

vksingh24

Offline

Newbie Poster

-1

25 Days Ago

•

Originally Posted by Ryshad

what did you change other than variable names?

One thing to watch out for with excel; if you have different data formats in a column it can cause problems. When reading in the data the majority datatype in each column is used. For example, if the first few rows of a column contain numbers, the column is assumed to contain numbers and any non-numeric values will be returned as NULL since they cant be converted to the columns datatype. I had a similar issue with a text column being interpretted as Double and returning nothing but DBNull's : /

Theres a nice breakdown here on how to handle the problem.

To explain you what I changed is very simple.

Other then the variable name if you look at the code I changed the each column value to return as string and ATLEAST IT RETURNS WHAT WAS IN THE EXCEL SHEET.

•

Join Date: Aug 2009

Posts: 351

Reputation: Ryshad has a spectacular aura about

Solved Threads: 62

Ryshad

Offline

Posting Whiz

25 Days Ago

•

Originally Posted by vksingh24

To explain you what I changed is very simple.

Other then the variable name if you look at the code I changed the each column value to return as string and ATLEAST IT RETURNS WHAT WAS IN THE EXCEL SHEET.

Im gonna assume that you hit CAPS by mistake rather than think you are being rude.
I saw that you added the Foreach loop but that wouldnt have altered what data was retrieved. You posted a query which you said returned empty values, then posted the same query with different variable names and said that it worked, i was jsut trying to figure out how.
When you said you were getting empty values, how were you checking the returned values initially? If you check them the same way now, do they still show as empty even when the .ToString() prints them correctly?

•

Join Date: Nov 2009

Posts: 8

Reputation: vksingh24 is an unknown quantity at this point

Solved Threads: 1

vksingh24

Offline

Newbie Poster

#10

25 Days Ago

•

Originally Posted by Ryshad

Im gonna assume that you hit CAPS by mistake rather than think you are being rude.
I saw that you added the Foreach loop but that wouldnt have altered what data was retrieved. You posted a query which you said returned empty values, then posted the same query with different variable names and said that it worked, i was jsut trying to figure out how.
When you said you were getting empty values, how were you checking the returned values initially? If you check them the same way now, do they still show as empty even when the .ToString() prints them correctly?

I can uderstand the misconception here.

I am not the original one who posted the question of returning empty values. I am still learning C# and use this forum as a part of my learning curve. I tried the same problem with a webform and I got the desired result.

...and also I am not rude. I just wanted to convey my message and when you try to write while being on foot rather than chair it is bound to happen.