469,356 Members | 2,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Regarding solving a problem in Converting Excel Data into Dataset

Hi,
I am converting Excel data into a Dataset in C#. There are around 24 columns
in the Excel Sheet.
First I tried to insert one row with correct values in the Excel sheet. i.e.
for text columns I entered text values and for numeric columns I entered
numeric values. It works fine and pass through all the validation checks and
gets inserted into the database successfully.
But when I gave some junk values in the excel sheet and tried to insert, the
previous one with the correct values are also reporting error.

In all the columns I have entered values, but for columns that has
irrelevant data it is entering as Null value in the DataSet. I am not able to
identify where the problem may be. Following is the code I am using to
convert Excel Data into DataSet.

Code:

public DataSet UploadStudentsXLFile(string filePath)
{
int i = 0;
string sheetName;

try
{
DataSet xlDataSet = new DataSet();
xlDataSet.Tables.Add("StudentsXml");

strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source= " + filePath + "; Extended Properties=Excel 8.0");

OleDbConnection xlCon = new OleDbConnection(strConn);

xlCon.Open();

System.Data.DataTable dt =
xlCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string[] excelSheet = new String[dt.Rows.Count];
foreach(DataRow row in dt.Rows)
{
excelSheet[i] = row["Table_Name"].ToString();
i++;
}
sheetName = excelSheet[0];

OleDbCommand xlCmd = new OleDbCommand("SELECT * FROM [" + sheetName +
"]", xlCon);
xlCmd.CommandType = CommandType.Text;
OleDbDataReader xlReader = xlCmd.ExecuteReader();

//Create a structure for the dataset
for(short fldIndex = 0; fldIndex < xlReader.FieldCount; fldIndex++)
{

//xlDataSet.Tables[0].Columns.Add(new
DataColumn(xlReader.GetName(fldIndex),xlReader.Get FieldType(fldIndex)));
xlDataSet.Tables[0].Columns.Add(new
DataColumn(xlReader.GetName(fldIndex), Type.GetType("System.String")));
}
DataRow dRow;
while(xlReader.Read())
{
dRow = xlDataSet.Tables[0].NewRow();
for(short fldIndex = 0; fldIndex < xlReader.FieldCount; fldIndex++)
{
dRow[fldIndex] = Convert.ToString(xlReader[fldIndex]);
}
xlDataSet.Tables[0].Rows.Add(dRow);
}
xlReader.Close();

xlCon.Close();
return xlDataSet;
}
catch(Exception)
{
throw;
}
}

Can you please help me out to solve this problem.

I am using .Net 2003, SQLServer 2000, Excel 2000.

Thanks in advance,
N.Ramakrishnan
Nov 21 '05 #1
1 2364
"Ramakrishnan Nagarajan" <ra**********@discussions.microsoft.com> schrieb
Hi,
I am converting Excel data into a Dataset in C#.


The C# groups is probably the better place to ask. :-)
Armin
Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Chris | last post: by
reply views Thread by acharyaks | last post: by
5 posts views Thread by Scott M. Lyon | last post: by
9 posts views Thread by dba123 | last post: by
2 posts views Thread by rwiegel | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.