importing from Excel

Dear All,


I am importing data from ExCel to a SQl SERver 2000 table.For this I am using this function
which reads the data from Excel and inserts it into the SQL 2000 table.

 

 // Create Connection to Excel Workbook
            try
            {
                using (OleDbConnection connection = new OleDbConnection(excelconnectionString))
                {
                    OleDbCommand command = new OleDbCommand("Select * FROM [" + sheetname + "$]", connection);

                    connection.Open();

                    // Create DbDataReader to Data Worksheet
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        // SQL Server Connection String
                        string sqlConnectionString = db.getConnectionString("AAI_trialConnectionString");

                        // Bulk Copy to SQL Server
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                        {
                            bulkCopy.DestinationTableName = desttable;
                            bulkCopy.WriteToServer(dr);
                        }
                    }
                }
                return "success";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
But the problem is that it throws an EXception when the data in the Excel does not match the datatype of the column in the sql table i.e if I enter a string into a int or float field.

In order to tackle the problem I want to suitably change the code so that if a exception is generated it shud be tackled so that the next record gets inserted.Finally I want a list of row numbers that got inserted or those that didn't.
How to change the above code so as to achieve the above objective?

 

Thanks.


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/2/2008 12:45:22 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

11 Replies
1086 Views

Similar Articles

[PageSpeed] 10
Get it on Google Play
Get it on Apple App Store

 since you want to get the list of rows inserted and failed you can loop through rows one at a time

 

        // Create Connection to Excel Workbook
try
{
string ConnectionString = db.getConnectionString("AAI_trialConnectionString");
string ExcelQuery = new OleDbCommand("Select * FROM [" + sheetname + "$]", connection);

System.Data.OleDb.OleDbDataAdapter ExcelAdapter =
new System.Data.OleDb.OleDbDataAdapter(ExcelQuery, ConnectionString);
ExcelAdapter.Fill(Ds);

foreach (DataRow Dr in Ds.Tables[0].Rows)
{
try
{
if (InsertRow(Dr) > 0)
{
InsertedRowCount++;
}
else
{
FailedRowCount++;
}
}
catch
{
FailedRowCount++;
}
}
}
catch()
{}


private int InsertRow(DataRow Dr)
{
try
{
string ConString =// read con string for insert

string Qry = "INSERT INTO [Tbl] ([Name]) VALUES ('" + Dr["Name"].ToString() + "')";
SqlConnection Con = new SqlConnection(ConString);
Con.Open();
SqlCommand Cmd = new SqlCommand(Qry, Con);


int affectedRows=Cmd.ExecuteNonQuery();
Con.Close();
            return affectedRows;        
      }
catch (Exception Ex)
{
return 0;
}

}
  

 Hope this will help


Regards,
Muhammed Saleem P

---------------------------------------------
Please do not forget to click "Mark As Answer" on the posts that helped you.

Happy Programming !!!

0
muhammedsalimp
12/2/2008 2:24:49 PM

Hi Muhammad Salim,

Thanks for your reply.

But I have doubts which I want to clarify before using the code.

In the following line,

>>  string Qry = "INSERT INTO [Tbl] ([Name]) VALUES ('" + Dr["Name"].ToString() + "')";

1.What do I give as the name of the Datarow i.e Dr["Name"] i.e what do I put in place of "Name"?

2. Is it necessary to convert the datarow into string before inserting into the database.Will the datarow not get inserted if not converted?

3. Is it sufficient to put  0 within the catch statement to avoid getting exception and termination of the program?

 

Thanks.


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/3/2008 5:09:35 AM

sunny74:

In the following line,

>>  string Qry = "INSERT INTO [Tbl] ([Name]) VALUES ('" + Dr["Name"].ToString() + "')";

1.What do I give as the name of the Datarow i.e Dr["Name"] i.e what do I put in place of "Name"?

 

Give the name of Column as Dr["ColumnName"]. In my case column name is "Name" .

 

 

sunny74:

2. Is it necessary to convert the datarow into string before inserting into the database.Will the datarow not get inserted if not converted?

 

We are not converting DataRow into String. It is just an insert statement and in my example the column "Name" accepts string values and so I have converted the column value into string before inserting. Convert the column as per your requirement.

 

 

sunny74:

3. Is it sufficient to put  0 within the catch statement to avoid getting exception and termination of the program?

 

Here when an exception occurs the method InsertRow() returns '0' and then the row is considered as failed to insert. The program will not terminate here.

It will move on to next row which I think is your requirement. 




Regards,
Muhammed Saleem P

---------------------------------------------
Please do not forget to click "Mark As Answer" on the posts that helped you.

Happy Programming !!!

0
muhammedsalimp
12/3/2008 5:28:00 AM

Hi,

I am able to get the column names from the dataset but I am unable to get the Column DataTypes unless I adopt a very complicated method.

But I don't want to make things that complicated so I think I will have to go without the conversion part i.e Dr["Name"].ToString() because

I won't be knowing the DataTypes in advance.

Any suggestions?

If I don't do the conversion it won't matter much because in any case some rows are not going to be inserted.Is it not?

 

Thanks for your reply.


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/3/2008 10:54:55 AM

Column Data Type becomes an issue in the sense Dr["Name"] is of object type and you cannot append an object to your query.

So Convert it to string anyway.

For string and datetime data type you have to give values in a  single quotes like 'Name'

whereas for  other data type you simply give it a such.

 


Regards,
Muhammed Saleem P

---------------------------------------------
Please do not forget to click "Mark As Answer" on the posts that helped you.

Happy Programming !!!

0
muhammedsalimp
12/3/2008 11:19:56 AM

 Hi,

here is one of the full working code that i have used in my program,

may be it may helps ypu

 

    protected void btnImport_Click(object sender, EventArgs e)
    {
        lblError.Visible = false;

        string xlsFile = fileImport.FileName;

        if (xlsFile == "" || xlsFile.EndsWith(".xls") != true)
        {
            lblError.Text = "Please select an Excel File";

            lblError.Visible = true;

            return;
        }
       
        string targetFileName = MapPath("~/_OrgData/Excel/" + xlsFile);

        fileImport.PostedFile.SaveAs(targetFileName);
                 
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFileName + ";Extended Properties='Excel 4.0;HDR=YES;'";

        OleDbConnection xlsConn = new OleDbConnection(excelConnectionString);

        xlsConn.Open();

            try
            {
                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] WHERE Username <> NULL", xlsConn);

                DbDataReader dr = command.ExecuteReader();

                string sqlConnectionString = TAData.Common.GetConnectionString();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "tblPeopleTemp";

                    bulkCopy.ColumnMappings.Add("Username", "persUserName");
                    bulkCopy.ColumnMappings.Add("Password", "persPassword");
                    bulkCopy.ColumnMappings.Add("Surname", "persLName");
                    bulkCopy.ColumnMappings.Add("First Name", "persFName");
                    bulkCopy.ColumnMappings.Add("Classes Held", "persClassesHeld");
                    bulkCopy.ColumnMappings.Add("Address1", "persAddress1");
                    bulkCopy.ColumnMappings.Add("Address2", "persAddress2");
                    bulkCopy.ColumnMappings.Add("Address3", "persAddress3");
                    bulkCopy.ColumnMappings.Add("Post Code", "persPostCode");
                    bulkCopy.ColumnMappings.Add("EMAIL Address", "persEmail");
                    bulkCopy.ColumnMappings.Add("Phone", "persHomePhone");
                    bulkCopy.ColumnMappings.Add("Mobile", "persMobilePhone");
                   
                    bulkCopy.WriteToServer(dr);
                }
            }
               
        catch (Exception ex)
        {
            lblError.Text = "The Excel content is not valid";
           
            lblError.Visible = true;
        }

        xlsConn.Close();

        File.Delete(targetFileName);
    }

 

 

 

this is working fine for me

hope it will help you


Sincerely,
MOONCODER

Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question.
0
mooncoder
12/3/2008 11:25:34 AM

Hi Muhammad Salim,

I tried converting all the column Data to string and insert it but it didn't insert.So I had to fetch the individual datatypes and convert the data to those types.

Had to do a lot of coding.

Now I need to insert data from CSV to SQL table.The code is similar to the one for Excel. I think the same pattern which works for EXCEL should work for CSV. What do you think?

Thanks for your reply.


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/5/2008 5:24:44 AM

Actually I'm also working on a CSV Reader module now.

In online help i saw that CSV File can be read by changing the connection string as follows

       string CsvConnectionString = @"Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="

      + Path.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

But unfortunately it is not working for me. I cannot establish connection to the CSV file.

Maybe because of the size of file. My CSV file is about 13 Mb in size with 8,50,000 rows

So I'm working on reading the File data.

 


Regards,
Muhammed Saleem P

---------------------------------------------
Please do not forget to click "Mark As Answer" on the posts that helped you.

Happy Programming !!!

0
muhammedsalimp
12/5/2008 5:44:08 AM

Hi M Salim,

The same pattern is working for me with just 2 changes in case of CSV.

1.First is the connection string.I am using like this:

 flpath = Convert.ToString(ViewState["Filename"]);// this is the full path of the file
 onlypath = Path.GetDirectoryName(flpath);// this is the path without the file name


                csvConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + onlypath + ";" + "Extended Properties='text;FMT=Delimited(,);HDR=YES'";

2. Second is the command.It is like this:

string CSVQuery = "Select * FROM [" + sheetname +"]"; // where sheetname is the CSV(txt) file name.

If you make these changes I think your code will work.Pls let me know if your works or not after making these changes.

Your coding pattern is what I needed i.e inserting row by row ,inserting valid rows and not allowing invalid rows to terminate the application by returning a number. It works for me.

Thanks a lot for the coding pattern.

I am marking your replies as the answer.


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/6/2008 5:02:41 AM

Hi Mooncoder,

Your code is good but it doesn't check the datatypes nor does it convert the data to the necessary datatypes.

Anyhow thanks for your effort. 

 


SUBHRANIL BASU RAY
Mumbai

TODAY'S TECHNIQUES ARE OBSOLETE FOR TOMORROW'S JOB.
PLS MARK MY REPLY "AS ANSWER" IF IT HELPED YOU.
0
sunny74
12/6/2008 5:05:50 AM

 Hi sunny74,

The CSV reader is working perfectly once I made the changes you said.

Thanks. 


Regards,
Muhammed Saleem P

---------------------------------------------
Please do not forget to click "Mark As Answer" on the posts that helped you.

Happy Programming !!!

0
muhammedsalimp
12/9/2008 5:58:30 AM
Reply:

Similar Artilces:

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installedhttp://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

How to Import data from Excel into MS SQL in .Net?
Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.  I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work. - Most simple and direct way I've tried is creating a oledb connection to...

Excel to SQL and SQL to Excel
I wanted to know if there is a better way to do the following.  Whenever I query a SQL Server database, I connect using Access through the ODBC because I can write the query using the query builder and export the results to Excel very easily.  Also, when I import data from an Excel spreadsheet to a SQL Server, I import to Access first, then write an append query to get the data into SQL from Access. I would like to learn how to bypass Access in both directions. Yes totally .. you can directly export and import data from excel to sql server. To do that one option is to ...

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.---------------------------------------------------------------------...

Importing Excel into SQL Server 2005 with Import Wizard
I have several spreadsheets given to me which are in Excel 2007 file format. So, I've saved them as Excel 2003. I run the SQL Server Import and Export Wizard in SQL Server 2005 Management Studio from my local PC againist an online 3rd party hosted database. Initially I was getting errors yet the data was imported, so I wasn't too concerned. Now, on one file, only 8 records out of 285 records are imported. Here is the latest error messages extract: Messages * Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error co...

How to Import data from excel sheet within SQL Client into SQL Server
 Hi All,   I need to  import data from excel sheet to SQL Database in SQL Server.I already done it in machine which SQL Server run.but I cant do it within SQL Client machine.while try to do it this error was occure Server: Msg 7302, Level 16, State 1, Line 2Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.Please tell me How I do itThanks,Harsha Hi harsha  Can you post your code  If this post helps you, please mark it as Answer.Gnana Prakash SSoftware EngineerCoimbatorehttp://www.sgnanaprakash.com  Hi Gnana,this is My...

importing an excel spreadsheet into a sql database using sql server 2005 express
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?    Try this thread for many options.  Hope this helps. http://forums.asp.net/thread/1442470.aspxKind regards,Gift Peddie thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. FrankBollard:thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. I am glad I could help.Kind regards,Gift Peddie...

Import excel file using sql query without installing excel on the server
Hi, I want to import excel file using sql query. MS Excel is not installed on the server. I am using sql server 2005. I do not want to achieve this by code. I want it using sql query only.  I have used openrowset but it requires MS Excel to be installed on the server. Thanks, Ashish.    Ashish Chaugule ashish_chaugule: Hi, I want to import excel file using sql query. MS Excel is not installed on the server. I am using sql server 2005. I do not want to achieve this by code. I want it using sql query only.  I have used openrowset but it requires M...

Excel as import to vb.net?
Has anyone experimented with using excel files as import to a vb.net app? I have a user who is ... reluctant to give up his personal sheets... Thanks! MVP...

How to import all data into SQL from Excel
Hi all,I have problem importing data from excel to sql 2000.i import this excel using my asp web form.but some data not imported.hi-lighted  data did not import.Ref_No     Stage_No       Boq_No       Description                  1                                     first stagee-001                             1.1         &nbs...

Excel Import into SQL Server
Hi, I am trying to write a routine that will use a fileUpload object so the user can browse to an excel file on their machine, then hit an Import button. On the click of the Import button I want to parse through the file and do some verification and manipulation with each row and then, if everything is ok, dump it all into a new table in sql server. I am wondering if I am on the right track? Should I save the file to the webserver and then dump it into a dataset, loop through the dataset and verify it, then if everything is ok, save via a stored proc?   Here is my control code: &nb...

Importing excel to sql server....
Dear AllI am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables'  list but when i execute the code again, system throws an exception that table already exists.On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.Regards<code>Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a...

Import from excel to sql server
Hi all I am facing a serious problem in importing the data that is available in Microsoft Excel into Our Sql server, Its a very Important and urgent requirement can any one pls let me know Thanks in advanceRamesh S Indecomm Global Services (Sr Software Developer) Bangalore,India...

Cannot Import Excel File Into SQL
I've read about a million posts on this, but I can't seem to get this to work.  This is my production environment:Web Server (ServerA): Windows Server 2003 SP2, ASP.NET 2.0Database Server (ServerB): Windows 2003 SP2, SQL Server 2005So I'm trying to have a web form upload an excel file to a share on ServerB, and then run a stored procedure that imports the contents of the excel file into a table in my database.  I can upload the file fine, but I cannot get the stored procedure to import the data. I always get this error, whether I run the process through my web form, or ...

import data from sql to excel
How is it possible to download the excel file programatically as a TRUEEXCEL file (Not an HTML file that looks like an Excel)BTW, this is the code that downlaods the grid to excel..Response.ContentType = "application/vnd.ms-excel";Response.Charset = "";Response.AddHeader("Content-Disposition", "attachment;filename=myfile.xls"); It is possible (just) but you'd need Excel installed on the server to do it, which is unlikely. You can fairly easily create CSV files, which will open fine in Excel. how do i do that?can you please guidE? If you mean ...

Web resources about - importing from Excel - asp.net.sql-datasource

Viral Channels: Hi5 Adds Contact Importing to App Invite Flow
... social networking platform I’ve seen, hi5 announced tonight that it is adding the ability for users to invite non-hi5 friends to apps by importing ...

YouTube - iPad importing photos from floppy disk.
You are using an outdated browser, which YouTube no longer supports. Since some features on YouTube may not work, you are viewing a lightweight ...

Tony Abbott considers importing US-style school program with private industry involvement
Tony Abbott is considering an unprecedented Americanisation of the school education system with radical changes that could see HECS-style fees ...

Importing fuel will prove better for the consumer
Importing fuel will prove better for the consumer

Sydney grandmother Lorn Cheng arrested for allegedly importing heroin in 252 pellets
Brenda filed a missing persons report last Friday, concerned that her mother hadn't returned from a holiday in Cambodia when she said she would. ...

Importing more fads from failed systems
Fast-tracking talented professionals into the classroom is yet another imported fad from England and the US - both of which are failed education ...

Mark Clermont sentenced to life in jail for importing drugs worth $260 million inside road roller
A judge has praised police for preventing cocaine and methylamphetamine with a street value of $260 million reaching the streets of Sydney and ...

Hong Kong man charged with importing 150kg of crystal methamphetamine into Sydney
A Hong Kong national is charged with importing 150 kilograms of ice following a joint-agency investigation in Sydney.

Man, 91, charged with importing cocaine hidden in soap boxes into Australia
A 91-year-old Sydney man has been charged with importing cocaine hidden in soap into Australia, prompting police to warn travellers to beware ...

Adobe updates Photoshop Express for Android with blemish removal tool, RAW importing, more
Adobe has actively been rolling out new and powerful tools for mobile platforms in recent months including Lightroom Mobile for iPad followed ...

Resources last updated: 1/14/2016 4:41:49 AM