C# VS2005 Import .CSV File into SQL Database

Question

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

Why not add if(Path.Exists(fileName)) ... To check also, it could be an issue with the ExcelConnection (maybe permissions) — PostMan
– PostMan, Commented Nov 9, 2011 at 2:53

to StackOverflow · Accepted Answer · 2011-11-09 09:26:21Z

2

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) + "]"

edited Nov 9, 2011 at 9:26

answered Nov 9, 2011 at 9:19

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Conrad Frix Over a year ago

+1 Adding Extended Properties=\"Text\""; doesn't hurt but yes its the directory not the file name. Also the obligatory link to connectionstrings.com or the MSDN kb HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases doesn't hurt

Coder · Accepted Answer · 2011-11-08 08:37:35Z

0

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

answered Nov 8, 2011 at 8:37

Coder

9062 gold badges13 silver badges29 bronze badges

Comments

NaveenBhat · Accepted Answer · 2012-06-14 10:20:46Z

0

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.

edited Jun 14, 2012 at 10:20

answered Nov 8, 2011 at 8:37

NaveenBhat

3,3364 gold badges42 silver badges48 bronze badges

9 Comments

gymcode Over a year ago

Nope, if i change to \, it will give me an error. This filepath works when I upload .xls files, but only when I modify my code to cater for .csv files, this error appeared.

NaveenBhat Over a year ago

OleDbConnection wont accepts forward slashes(/). Hence, you need to mention backward slash here.

gymcode Over a year ago

it says unrecognized escape sequence if I use backslash

NaveenBhat Over a year ago

specify @ before "C: it removes the escape meaning of (\) or just use (\\). See my answer carefully....

Coder Over a year ago

you need to put (\) backslashbackslash

|

Mohammad Atiour Islam · Accepted Answer · 2013-04-25 07:04:54Z

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

Collectives™ on Stack Overflow

C# VS2005 Import .CSV File into SQL Database

4 Answers 4

1 Comment

Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related