将数据从Excel导入数据库 [英] import data from excel to database

问题描述

亲爱的朋友
我需要将数据从excel保存到sql数据库而无需重复.
但是我怎么能从任何驱动器从系统中获取文件以将数据保存在数据库中.正如我尝试过的，我能够将数据保存在excel中，但是我给出了硬编码的路径，但是我想给出默认路径这样我就可以从任何驱动器或台式机中提取文件了.
这是我的这段代码中的代码，我可以从C:驱动器获取文件，因为我已对其进行了硬编码

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

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

PL帮助，以便我可以从任何驱动器或台式机上获取文件...

任何人都可以通过适当的代码来帮助我解决此问题..

在此先感谢
普拉瑟姆

[edit]已添加代码块-OriginalGriff [/edit]

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]

推荐答案

您可以为此使用SSIS.

-Rahul Sharma

You can use SSIS for this.

-Rahul Sharma

1)使用文件上传控件->从中获取路径->在您的代码中使用

2)您也可以将文件路径存储在Web配置中. ->从web.config中读取该路径->在您的代码中使用.
但是在第二种解决方案中，您始终需要在web.config文件中提供硬编码路径.

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.

据我所知，您遇到的麻烦是:

1.在其他文件夹中找到Excel:列出文件夹中的所有文件 [^ ]
2.将Excel导入SQL表:将MS Excel导入数据表 [^ ]

请更新无重复"指的是什么?没有复制工作簿，或者没有复制行或单元格?


关于如何在不同文件夹中查找所有Excel文件的一些改进:

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:

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

这篇关于将数据从Excel导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！