在mysql数据库中插入csv文件 [英] insert csv file in mysql database

问题描述

我想使用asp.net在mysql数据库表中插入csv文件数据。

有两个表（id，month，year，contenttype）和第二个表（名称，年龄，性别） ，class，id）它们与foreigen键有关。



当我插入记录时，它在网页上给出了以下错误...

错误：SQL语法中有错误;查看与您的MySQL服务器版本对应的手册，以便在''LOAD DATA INFILE'附近使用正确的语法+'路径+''INSERT INTO TABLE sla1 FIELDS TERMINATED BY''，''LINES''在第1行



我的代码如下：

I want to insert csv file data in mysql database table using asp.net.
There is two table(id,month,year,contenttype) and in 2nd table(name ,age,sex,class,id)they are related with foreigen key.

When i insert record then it given following error on webpage...
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''LOAD DATA INFILE ''+path+'' INSERT INTO TABLE sla1 FIELDS TERMINATED BY '','' LINES '' at line 1

my code given below..

MySqlConnection exclecon = new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
            string path = FileUpload1.PostedFile.FileName;
            string ext = Path.GetExtension(path);
            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
                {
                    switch (ext)
                    {
                        case ".csv":

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

                            break;
                    }
                    string year = ddlyear.SelectedItem.ToString();
                    string month = ddlmonth.SelectedItem.ToString();
                    string insquery = "insert into sla(month,year,contenttype) values(@month,@year,@contenttype) LOAD DATA INFILE '+path+' INSERT INTO TABLE sla1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' IGNORE 2 LINES";
                    MySqlCommand mycom = new MySqlCommand(insquery, exclecon);

                    mycom.Parameters.AddWithValue("@month", month);

                    mycom.Parameters.AddWithValue("@year", year);
                    mycom.Parameters.Add("@contenttype", MySqlDbType.VarChar).Value = contenttype;
                    exclecon.Open();
                    mycom.ExecuteNonQuery();
                    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('File uploaded Successfully ')</script>");
                }

                catch (Exception ex)
                {
                    Label4.Text = "Error: " + ex.Message.ToString();

                }
            }
        }

推荐答案

您好，



尝试将它们作为两个单独的SQL执行。要使LOAD DATA成功运行，请确保将文件传输到运行MySQL守护程序的服务器，并且该文件具有文件所在文件夹的读取权限。文件的路径要么是绝对路径，要么是相对于服务器数据目录或默认数据库的数据库目录的路径。如果您不想将文件传输到服务器，则必须在SQL中添加关键字 LOCAL 。如果未使用--local-infile = 0启动MySQL，则LOCAL关键字将不起作用。有关这方面的更多信息，请参见[ ^ ]。您的查询执行代码可能与下面显示的代码非常相似。

Hello,

Try executing them as two separate SQL''s. For LOAD DATA to successfully work make sure that the file is transferred to the server on which MySQL daemon is running and that it has the read permissions for the folder in which file resides. The path of the file either needs to be the absolute path or should be a path relative to servers data directory or the database directory of the default database. If you don''t want to transfer file to server then you will have to add keyword LOCAL in your SQL. The LOCAL keyword will not work if MySQL is not started with --local-infile=0. More information on this can be found here[^]. Your query execution code might look very similar to one shown below.

MySqlConnection exclecon = new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
string insquery = "INSERT INTO sla (month, year, contenttype) VALUES(@month, @year, @contenttype)";
string uploadQry = "LOAD DATA INFILE " + path + " INSERT INTO TABLE sla1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' IGNORE 2 LINES";
MySqlCommand mycom = new MySqlCommand(insquery, exclecon);
mycom.Parameters.AddWithValue("@month", month);
mycom.Parameters.AddWithValue("@year", year);
mycom.Parameters.Add("@contenttype", MySqlDbType.VarChar).Value = contenttype;

MySqlCommand myCUpload = new MySqlCommand(uploadQry, excelcon);

exclecon.Open();
mycom.ExecuteNonQuery();
myCUpload.ExecuteNonQuery();

问候，

Regards,

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