Click here to Skip to main content
15,886,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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();
                        }
                    }



}
Posted
Updated 12-Apr-13 7:46am
v2

1 solution

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,
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900