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

[venkates.99]

Hi all,
my code is like..

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

[RunTimeError]

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

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.

[sknake]

Upload your excel spreadsheet

[venkates.99]

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

[sknake]

I dont see it....

[vksingh24]

Hi,

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

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());
               
           }

[Geekitygeek]

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.

[vksingh24]

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.

[Geekitygeek]

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?

[vksingh24]

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.

[Geekitygeek]

Aah!! A serious case of cross wires here, my bad. When you said "I got the desired results" i immediately assumed it was the OP saying they had resolved the issue :p My sincerest apologies :) And as i said, i assumed the CAPS were accidental.

The fact that you have run the same query and got the desired results most likely confirms what i said; the data connector is mis-casting the column and returning empty strings when it fails to cast the strings to the wrong type. I have apps that retrieve emaila ddresses just fine from excel files so i cant see it being the special characters at fault. Guess we just have to wait for the OP to post their excel file correctly :)

[DdoubleD]

This is a little confusing to me... What happened to venkates.99, who started the thread? I see vksingh.24 saying the problem is solved--are the two of you working as a team or something?

If there is still a problem, let us know what it is because there is no uploaded spreadsheet to test with. The data sample-text provided does not cause any problem for me when put into a new sheet with no other values present in the sheet.

Otherwise, if the problem is solved, please update the thread to SOLVED. Thanks.

EDIT: I missed the last post by Ryshad--didn't notice page 2. Anyway, I guess we are all waiting to hear back from vksingh.24.

[vksingh24]

I am not working as a team with venkates.99. I think venkates is yet to confirm about his problem.

Thanks!

[venkates.99]

HI D,
I have solved this.I need more info about some other problem
i.e.

I need to load data from DataGridView/dataset to Excel via Save as Dialog box, and vice varsa,
i.e need load data from Excel to DataGridView/dataset via Open dialog box.

Please give a solution

Thanks
Venkat.

[Geekitygeek]

HI D,
I have solved this.I need more info about some other problem
i.e.

I need to load data from DataGridView/dataset to Excel via Save as Dialog box, and vice varsa,
i.e need load data from Excel to DataGridView/dataset via Open dialog box.

Please give a solution

Thanks
Venkat.

If your original problem is solved, please mark this thread as solved and create a new thread for your new question. I think this thread has become confusing enough as it is without piggybacking a new problem on top ;)