import data from excel to database

Question

0.00/5 (No votes)

See more:

Dear Friends
i need to save the data from excel to sql database without duplication .
But how can i fetch the file from my system from any drive to save the data in database .As i have tried and i am able to save the data in excel but i have give the path hard coded but i want to give the default path so that i can take the file from any of my drive or my desktop ..

This is my code from this code i can fertch the file from C: drive as i have given it hard coded

C#

private void ShowData()
    {
        
        string deli = ",";
        string tbname = "tblActiveSubBroker"; //dataset .. used 2 stored. then move dataset to .. grid..
        string filename = ("C:\\table2.csv"); // path. plz keep the same path. .. 
        string str = Convert.ToString(System.DateTime.Now);

        DataSet ds = new DataSet();
        StreamReader sr = new StreamReader(filename); //Inputoutput .function 2 read.. file..

        ds.Tables.Add(tbname); //add 2 table.. 

        //specify. colum1 for datagrid1.
        ds.Tables[tbname].Columns.Add("Sub_Code");//specify colum2 for datagrid2.
        ds.Tables[tbname].Columns.Add("Activedate");



        string alldata = sr.ReadToEnd();

        string[] rows = alldata.Split("\n".ToCharArray());

        foreach (string r in rows)
        {
            string[] items = r.Split(deli.ToCharArray());
            ds.Tables[tbname].Rows.Add(items);

        }

        int count = ds.Tables[tbname].Rows.Count;
        int index = 0;
        //int currentindex = 0;

        for (index = 0; index < count; index++)
        {
            string code = ds.Tables[0].Rows[index][0].ToString();
            string date = ds.Tables[0].Rows[index][1].ToString();
            con = new SqlConnection("Data Source=192.168.0.187;Initial Catalog=mis;Persist Security Info=True;User ID=sa;Password=ms");
            cmd.Connection = con;
            con.Open();
            SqlCommand cmd1 = new SqlCommand("Usb_fileupload", con);
            cmd1.CommandType = System.Data.CommandType.StoredProcedure;
            cmd1.CommandTimeout = 10000;
            SqlDataAdapter da = new SqlDataAdapter(cmd1);
            cmd1.Parameters.Clear();
            cmd1.Parameters.AddWithValue("@Sub_Code", SqlDbType.VarChar).Value = code;
            //cmd1.Parameters.Add("@Activedate", SqlDbType.NVarChar).Value = Convert.ToDateTime(TextBox3.Text.ToString().Trim()).ToString("MM/dd/yyyy");
            cmd1.Parameters.AddWithValue("@Activedate", SqlDbType.VarChar).Value = date;
             int i=cmd1.ExecuteNonQuery();
            con.Close();
            Label1.Text = "Data Inserted successfully";


        }      

    }

    protected void btnsave_Click1(object sender, EventArgs e)
    {
        Label1.Visible = true;
        ShowData(); // call the abv function.

             tbl2.Columns.Add("newcol");
 }

PLs Help so that how i can take the file from any drive or my desktop...

Can Anyone help me for this issue with a proper code.....

Thanks in Advance
Pratham

[edit]Code block added - OriginalGriff[/edit]

Posted 10-Jun-12 20:52pm

pratham2587

Updated 10-Jun-12 21:02pm

OriginalGriff

v3

Add a Solution

8 solutions

Solution 1

You can use SSIS for this.

-Rahul Sharma

Posted 10-Jun-12 21:25pm

Rahul.KumarSharma

Solution 2

1)Use the file upload control --> get the path from that --> Use In your code

2)You can also store the file path in web config. --> Read that path from web.config --> use in your code.
But in second solution you always need to provide the hard coded path in web.config file.

Posted 10-Jun-12 21:27pm

puneetnarang

Comments

pratham2587 11-Jun-12 3:30am

can anyone help me with the code for the same

pratham2587 12-Jun-12 6:55am

still issue not resolved ..Getting an error regarding a ""error the process cannot access the file because it is being used by another process""

Solution 3

As I understand the trouble you meet:

1. find Excel in different folder: list all the files in a folder[^]
2. import the Excel to SQL table: import MS Excel to datatable[^]

please update what "without duplication" refers to ? no duplication workbook or no duplication rows or cells?

A little Improvement on how to find all Excel file in different folders:

C#

string[] files = Directory.GetFiles(@"D:\", "*.xls",SearchOption.AllDirectories);

Posted 10-Jun-12 21:54pm

Pandvi

Updated 10-Jun-12 22:03pm

v2

Comments

pratham2587 11-Jun-12 4:48am

without duplication means no duplicate records should be saved in database ..but that is not the issue ..i just want to know how i can fetch the excel file from different drives and upload that file data in database ...in my mentioned example i have given C: path hard coded but how can i select the file from different drives ...

Zoltán Zörgő 11-Jun-12 4:52am

What about OpenFileDialog component?

Pandvi 11-Jun-12 6:08am

Sure, you can use it. In that case, the openfiledialog must reture the srting of the file path. check:

OpenFileDialog dlg = new OpenFileDialog();
dlg.ShowDialog();

if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName;
fileName = dlg.FileName;
}

Solution 4

Your code has several problems. You better use SSIS like Rahul suggested. Here you have an article about how it can be used programatically: http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx[^]. The only drawback of it is that you need to upload the source file to the server or provide remote access to the file from the server.
If you stick to the client side processing, this is a really good article: C# - CSV Import Export[^]
If you want to skip duplicate rows, you better import the data into a temporary table and than use select distinct... into statement to have filtered data in it's final location.

Posted 10-Jun-12 21:59pm

Zoltán Zörgő

Updated 10-Jun-12 22:03pm

v2

Solution 5

Hi,
Go through the link-
Import Excel to Database[^]

Posted 11-Jun-12 2:43am

Jαved

Comments

pratham2587 12-Jun-12 6:55am

still issue not resolved ..Getting an error regarding a ""error the process cannot access the file because it is being used by another process""

Solution 9

This might help you

http://ozzieperez.net/blog/?p=13[^]

Posted 13-Jun-12 19:45pm

tssutha03

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pratham2587 · Accepted Answer · 2012-06-13T18:59:00

Issue resolved file uploaded in data base without any duplication ...........
Kindly find the code for the same....

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Data.SqlTypes;

public partial class _Default : System.Web.UI.Page
{

SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
DataTable tbl2 = new DataTable();
SqlConnection con = new SqlConnection();
DataSet ds = new DataSet();

StringBuilder sb = new StringBuilder();
string strPath = string.Empty;
SqlCommand cmd = new SqlCommand();
//string clientcode;
//string filename;
int j = 0;
//string Sub_Code;
//string Activedate;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{

mvTabs.SetActiveView(vwTab1);
Label1.Visible = false;
}
}

public static void ShowAlert(Page currentPage, string message)
{
StringBuilder sb = new StringBuilder();
sb.Append("alert('");
sb.Append(message);
sb.Append("');");
currentPage.ClientScript.RegisterStartupScript(typeof(_Default), "showalert", sb.ToString(), true);
}

public static void ShowAlert(string message)
{
Page currentPage = HttpContext.Current.Handler as Page;
if (currentPage != null)
ShowAlert(currentPage, message);
}

protected void btnsave_Click1(object sender, EventArgs e)
{
Label1.Visible = true;
try
{
string fileName = string.Empty;
if (FileUpload1.HasFile)
{

fileName = FileUpload1.PostedFile.FileName;

string file_ext = Path.GetExtension(fileName);
if (file_ext.Trim().ToLower() == ".csv")
{
FileUpload1.Controls.Clear();

string line = null; int i = 0;
string imgMap;
imgMap = ConfigurationSettings.AppSettings["imageFolderMap"].ToString();

// save file on server then upload serfile on database and delete server file
strPath = imgMap + Path.GetFileName(fileName);
if (!Directory.Exists(imgMap))
{

Directory.CreateDirectory(imgMap);
}
//else
//{
// Directory.Delete(imgMap);
// Directory.CreateDirectory(imgMap);

//}

FileUpload1.PostedFile.SaveAs(strPath);

using (StreamReader sr = File.OpenText(strPath))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{

foreach (object item in data)
{
{

dt.Columns.Add(new DataColumn());

}
}

i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);

}
}
//con = new SqlConnection(ConfigurationManager.ConnectionStrings["misConnectionString"].ConnectionString);

//SqlCommand cmd = new SqlCommand("TRUNCATE TABLE tblDndClient", con);
//cmd.Connection.Open();
//cmd.ExecuteNonQuery();
//cmd.Connection.Close();
//con.Open();
//SqlBulkCopy s = new SqlBulkCopy(con);
//s.DestinationTableName = "tblDndClient";
//s.WriteToServer(dt);
//s.Close();

}

}
else
{

Label1.Text = "Error in Inserting Data";

}
}

}
catch (Exception Ex)
{
string msg = Ex.Message.ToString();
Label1.Visible = true;
Label1.Text = "Error in Inserting Data";

}
finally
{

con.Close();
dt.Clear();

System.IO.File.Exists(strPath);
System.IO.File.GetAccessControl(strPath);
System.IO.File.Delete(strPath);
Label1.Visible = true;
Label1.Text = "Data Inserted Successfully";
}

}

}

pratham2587 · Accepted Answer · 2012-06-13T19:00:00

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Data.SqlTypes;

public partial class _Default : System.Web.UI.Page
{

SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
DataTable tbl2 = new DataTable();
SqlConnection con = new SqlConnection();
DataSet ds = new DataSet();

StringBuilder sb = new StringBuilder();
string strPath = string.Empty;
SqlCommand cmd = new SqlCommand();
//string clientcode;
//string filename;
int j = 0;
//string Sub_Code;
//string Activedate;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{

mvTabs.SetActiveView(vwTab1);
Label1.Visible = false;
}
}

public static void ShowAlert(Page currentPage, string message)
{
StringBuilder sb = new StringBuilder();
sb.Append("alert('");
sb.Append(message);
sb.Append("');");
currentPage.ClientScript.RegisterStartupScript(typeof(_Default), "showalert", sb.ToString(), true);
}

public static void ShowAlert(string message)
{
Page currentPage = HttpContext.Current.Handler as Page;
if (currentPage != null)
ShowAlert(currentPage, message);
}

protected void btnsave_Click1(object sender, EventArgs e)
{
Label1.Visible = true;
try
{
string fileName = string.Empty;
if (FileUpload1.HasFile)
{

fileName = FileUpload1.PostedFile.FileName;

string file_ext = Path.GetExtension(fileName);
if (file_ext.Trim().ToLower() == ".csv")
{
FileUpload1.Controls.Clear();

string line = null; int i = 0;
string imgMap;
imgMap = ConfigurationSettings.AppSettings["imageFolderMap"].ToString();

// save file on server then upload serfile on database and delete server file
strPath = imgMap + Path.GetFileName(fileName);
if (!Directory.Exists(imgMap))
{

Directory.CreateDirectory(imgMap);
}
//else
//{
// Directory.Delete(imgMap);
// Directory.CreateDirectory(imgMap);

//}

FileUpload1.PostedFile.SaveAs(strPath);

using (StreamReader sr = File.OpenText(strPath))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{

foreach (object item in data)
{
{

dt.Columns.Add(new DataColumn());

}
}

i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);

}
}
//con = new SqlConnection(ConfigurationManager.ConnectionStrings["misConnectionString"].ConnectionString);

//SqlCommand cmd = new SqlCommand("TRUNCATE TABLE tblDndClient", con);
//cmd.Connection.Open();
//cmd.ExecuteNonQuery();
//cmd.Connection.Close();
//con.Open();
//SqlBulkCopy s = new SqlBulkCopy(con);
//s.DestinationTableName = "tblDndClient";
//s.WriteToServer(dt);
//s.Close();

}

}
else
{

Label1.Text = "Error in Inserting Data";

}
}

}
catch (Exception Ex)
{
string msg = Ex.Message.ToString();
Label1.Visible = true;
Label1.Text = "Error in Inserting Data";

}
finally
{

con.Close();
dt.Clear();

System.IO.File.Exists(strPath);
System.IO.File.GetAccessControl(strPath);
System.IO.File.Delete(strPath);
Label1.Visible = true;
Label1.Text = "Data Inserted Successfully";
}

}

}

import data from excel to database

8 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Solution 7

Solution 8

Solution 9

Add your solution here

Preview 0