CODEWITHSUNDEEP
c#sql-servervisual-studioexcel
gymcode
gymcode

Reputation: 4623

C# VS2005 Import .CSV File into SQL Database

I am trying to import a .csv file into my database. I am able to import an excel worksheet into my database, however due to different file format as .csv as from .xls, I need to make an import function specially for .csv.

Below is my code:

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        // Get the name of the Excel spreadsheet to upload. 
        string strFileName = Server.HtmlEncode(FileUpload1.FileName);

        // Get the extension of the Excel spreadsheet. 
        string strExtension = Path.GetExtension(strFileName);

        // Validate the file extension. 
        if (strExtension != ".xls" && strExtension != ".xlsx" && strExtension != ".csv" && strExtension != ".csv")
        {
            Response.Write("<script>alert('Failed to import DEM Conflicting Role Datasheet. Cause: Invalid Excel file.');</script>");
            return;
        }

                    // Generate the file name to save. 
            string strUploadFileName = @"C:\Documents and Settings\rhlim\My Documents\Visual Studio 2005\WebSites\SoD\UploadFiles\" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

            // Save the Excel spreadsheet on server. 
            FileUpload1.SaveAs(strUploadFileName);

            // Create Connection to Excel Workbook
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Text;";
            using (OleDbConnection ExcelConnection = new OleDbConnection(connStr)){
            OleDbCommand ExcelCommand = new OleDbCommand("SELECT [columns] FROM +userrolelist", ExcelConnection);

            OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

            ExcelConnection.Open();

        using (DbDataReader dr = ExcelCommand.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = "Data Source=<IP>;Initial Catalog=<DB>;User ID=<userid>;Password=<password>";

            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "DEMUserRoles";
                bulkCopy.WriteToServer(dr);
                Response.Write("<script>alert('DEM User Data imported');</script>");

            }
        }
        }
    }
    else Response.Write("<script>alert('Failed to import DEM User Roles Data. Cause: No file found.');</script>");
}

The file has been successfully saved, but the error says that the path for the file is not valid, even though the file has been successfully saved as .csv, therefore I am not able to continue with the process of importing the data into my database.

Below are the screenshots of my error: enter image description here

enter image description here

In conclusion I am having the error that the file path which the csv file is saved is not valid, although the csv file is successfully saved. Need some help from experienced. Thank You

Upvotes: 1

Views: 2148

Answers (4)

Mohammad Atiour Islam
Mohammad Atiour Islam

Reputation: 5708

To avoid the connection open you can use like

// Read the CSV file name & file path  
            // I am usisg here Kendo UI Uploader  
            string path = "";  
   string filenamee = "";  
   if (files != null)  
   {  
     foreach (var file in files)  
     {  
       var fileName = Path.GetFileName(file.FileName);  
       path = Path.GetFullPath(file.FileName);  
       filenamee = fileName;  
     }  
                 // Read the CSV file data  
     StreamReader sr = new StreamReader(path);  
     string line = sr.ReadLine();  
     string[] value = line.Split(',');  
     DataTable dt = new DataTable();  
     DataRow row;  
     foreach (string dc in value)  
     {  
       dt.Columns.Add(new DataColumn(dc));  
     }  
     while (!sr.EndOfStream)  
     {  
       value = sr.ReadLine().Split(',');  
       if (value.Length == dt.Columns.Count)  
       {  
         row = dt.NewRow();  
         row.ItemArray = value;  
         dt.Rows.Add(row);  
       }  
     }

For more help you can also See This Link

Upvotes: 0

NaveenBhat
NaveenBhat

Reputation: 3318

You need to use the backward slashes(\) on the file path.

string strUploadFileName = @"C:\Documents and Settings\rhlim\My Documents\Visual Studio 2005\WebSites\SoD\UploadFiles\" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

EDIT 1: I believe FileUpload1.SaveAs converts the / to \ internally to identify the correct location.

EDIT 2: Its the problem with your connectionstring, even though you are using .csv format, you need to set Excel 8.0 or Excel 12.0 Xml as the Extended Properties

Here is the sample:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 12.0 Xml;";

For other types, check the code of OLEDB section of my article.

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124696

If you're reading a CSV file, your connection string should specify the directory containing your CSV file.

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                 Path.GetDirectoryName(strUploadFileName);

You then use the filename in your SELECT statement:

"SELECT * FROM [" + Path.GetFileName(strUploadFileName) + "]"

Upvotes: 2

Coder
Coder

Reputation: 896

I think you have this problem because you use "/" instead of "\" Try to modify the path C:\.....

Upvotes: 0

Related Questions