保存.csv文件在mysql数据库表中 [英] Save .csv fle in mysql database table

问题描述

如何使用asp.net在mysql数据库表中插入.csv文件。我在第一个表（id，month，year）和第二个表（name，age，sex，class，id）中有两个表（id工作）作为本地密钥）按照两个月和一年插入.....



我的代码如下：

How to insert .csv file in mysql database table using asp.net.I have two table in 1st table(id,month,year) and 2nd table(name ,age ,sex,class,id) here(id work as foreigen key) insert according two month and year.....

my code is given below.

string filePath = FileUpload1.PostedFile.FileName;

            string filename = Path.GetFileName(filePath);

            string ext = Path.GetExtension(filename);

            string contenttype = String.Empty;

            if (!FileUpload1.HasFile)
            {

                ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language=''javascript''>alert(''Please select a file'')</script>");  //if file uploader has no file selected
            }
            else
                if (FileUpload1.HasFile)
                {
                    try
                    {


                        //Set the contenttype based on File Extension

                        switch (ext)
                        {

                            //case ".doc":

                            //    contenttype = "application/vnd.ms-word";

                            //    break;

                            //case ".docx":

                            //    contenttype = "application/vnd.ms-word";

                            //    break;

                            //case ".xls":

                            //    contenttype = "application/vnd.ms-excel";

                            //    break;

                            //case ".xlsx":

                            //    contenttype = "application/vnd.ms-excel";

                            //    break;

                            case ".csv":

                                contenttype = "application/vnd.ms-excel";

                                break;


                            //case ".pdf":

                            //    contenttype = "application/pdf";

                            //    break;

                        }

                        if (contenttype != String.Empty)
                        {



                            Stream fs = FileUpload1.PostedFile.InputStream;

                            StreamReader br = new StreamReader(fs);
                            //string line = br.ReadLine();
                            //string[] vlaue = line.Split('';'');
                            //DataTable dt = new DataTable();
                            //DataRow row;
                            //foreach (string dc in Value)
                            //{
                            //    dt.Columns.Add(new DataColumn(dc));
                            //}
                            //while (!br.EndOfStream)
                            //{
                            //    Value = br.ReadLine().Split('';'');
                            //    if (Value.Length = dt.Columns.Count)
                            //    {
                            //        row = dt.NewRow();
                            //        row.ItemArray = Value;
                            //        dt.Rows.Add(row);
                            //    }
                            //}

                            MySqlConnection con = new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
                            string inst = "insert into sla(month,year) values (''" + DropDownList1.SelectedItem.ToString() + "'',''" + DropDownList2.SelectedItem.ToString() + "'')( LOAD DATA LOCAL INFILE ''fs'' INTO TABLE sla1 FIELDS TERMINATED BY '','' ENCLOSED BY ''\n\r'' IGNORE 1 LINES (From SLA Non-Compliance Report Months/Parameter, Target, Unit, OM,Finance,InbndMRO,InbndRM,Maximo,Payroll,HILAllied,Hardware,Network,Software, DBA, OPM set'' idsla''=(select idsla from sla)))";
                            //MySqlBulkLoader op=new MySqlBulkLoader(con);

                            MySqlCommand commd = new MySqlCommand(inst, con);
                            con.Open();
                            commd.ExecuteNonQuery();
                            con.Close();
                        }
                    }

}

}

推荐答案

您好，



根据MySQL文档， LOCAL 关键字会影响文件的预期位置和错误处理。 LOCAL 仅在您的服务器和客户端都已配置为允许时才有效。例如，如果mysqld以--local-infile = 0启动，则 LOCAL 不起作用。在这种情况下，如果文件没有在ASP.NET网站所在的同一台机器上运行，则必须将文件传输到运行MySQL的服务器。此外，您需要确保MySQL服务对包含csv文件的文件夹具有读访问权限，并且还要在SQL中指定文件的完整路径。可以在此处找到对MySQL文档的引用[ ]



问候，

Hello,

According to MySQL documentation the LOCAL keyword affects expected location of the file and error handling. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. In such a case you will have to transfer the file to the server on which MySQL is running, if it''s not running on the same machine on which your ASP.NET web site is located. Also you need to ensure that the MySQL service has read access to the folder containing the csv file and you also specify the full path of the file in SQL. Reference to MySQL documentation can be found here[]

Regards,

这篇关于保存.csv文件在mysql数据库表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！