Upload CSV File And Read Using ASP.net

steps :

1) Create Simple Web Application Using (ASP.Net with C#).

2) Create Class CSVReader (CSVReader.cs)

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Web;
   5: using System.Web.Security;
   6: using System.Web.UI;
   7: using System.Web.UI.WebControls;
   8: using System.Web.UI.WebControls.WebParts;
   9: using System.Web.UI.HtmlControls;
  10: using System.IO;
  11: using System.Text;
  12: using System.Collections;
  13:  
  14: public class CSVReader
  15: {
  16:     //
  17:     private Stream objStream;
  18:     private StreamReader objReader;
  19:  
  20:     //add name space System.IO.Stream
  21:     public CSVReader(Stream filestream) : this(filestream, null) { }
  22:  
  23:     public CSVReader(Stream filestream, Encoding enc)
  24:     {
  25:         this.objStream = filestream;
  26:         //check the Pass Stream whether it is readable or not
  27:         if (!filestream.CanRead)
  28:         {
  29:             return;
  30:         }
  31:         objReader = (enc != null) ? new StreamReader(filestream, enc) : new StreamReader(filestream);
  32:     }
  33:     //parse the Line
  34:     public string[] GetCSVLine()
  35:     {
  36:         string data = objReader.ReadLine();
  37:         if (data == null) return null;
  38:         if (data.Length == 0) return new string[0];
  39:         //System.Collection.Generic
  40:         ArrayList result = new ArrayList();
  41:         //parsing CSV Data
  42:         ParseCSVData(result, data);
  43:         return (string[])result.ToArray(typeof(string));
  44:     }
  45:  
  46:     private void ParseCSVData(ArrayList result, string data)
  47:     {
  48:         int position = -1;
  49:         while (position < data.Length)
  50:             result.Add(ParseCSVField(ref data, ref position));
  51:     }
  52:  
  53:     private string ParseCSVField(ref string data, ref int StartSeperatorPos)
  54:     {
  55:         if (StartSeperatorPos == data.Length - 1)
  56:         {
  57:             StartSeperatorPos++;
  58:             return "";
  59:         }
  60:  
  61:         int fromPos = StartSeperatorPos + 1;
  62:         if (data[fromPos] == '"')
  63:         {
  64:             int nextSingleQuote = GetSingleQuote(data, fromPos + 1);
  65:             int lines = 1;
  66:             while (nextSingleQuote == -1)
  67:             {
  68:                 data = data + "\n" + objReader.ReadLine();
  69:                 nextSingleQuote = GetSingleQuote(data, fromPos + 1);
  70:                 lines++;
  71:                 if (lines > 20)
  72:                     throw new Exception("lines overflow: " + data);
  73:             }
  74:             StartSeperatorPos = nextSingleQuote + 1;
  75:             string tempString = data.Substring(fromPos + 1, nextSingleQuote - fromPos - 1);
  76:             tempString = tempString.Replace("'", "''");
  77:             return tempString.Replace("\"\"", "\"");
  78:         }
  79:  
  80:         int nextComma = data.IndexOf(',', fromPos);
  81:         if (nextComma == -1)
  82:         {
  83:             StartSeperatorPos = data.Length;
  84:             return data.Substring(fromPos);
  85:         }
  86:         else
  87:         {
  88:             StartSeperatorPos = nextComma;
  89:             return data.Substring(fromPos, nextComma - fromPos);
  90:         }
  91:     }
  92:  
  93:     private int GetSingleQuote(string data, int SFrom)
  94:     {
  95:         int i = SFrom - 1;
  96:         while (++i < data.Length)
  97:             if (data[i] == '"')
  98:             {
  99:                 if (i < data.Length - 1 && data[i + 1] == '"')
 100:                 {
 101:                     i++;
 102:                     continue;
 103:                 }
 104:                 else
 105:                     return i;
 106:             }
 107:         return -1;
 108:     }
 109: }
 110:  

3) put below control in to the aspx page.

<table>
 <tr>
  <td>
   <asp:FileUpload ID="FileUpload1" runat="server" />
  </td>
  <td>
   <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload CSV" />
  </td>
 </tr>
 <tr>
   <td colspan="2">
    <asp:Label ID="lblMsg" Text="Please Select Proper File" runat="server" BorderColor="White"
         Font-Bold="True" ForeColor="Red" Visible="false"></asp:Label>
   </td>
 </tr>
 <tr>
   <td>
    <asp:GridView ID="gv" runat="server"></asp:GridView>
   </td>
 </tr>
</table>

4) In Code Behind File Put Below Code.

   1: protected void Button1_Click(object sender, EventArgs e)
   2:    {
   3:        if (FileUpload1.PostedFile.FileName == string.Empty)
   4:        {
   5:            lblMsg.Visible = true;
   6:            return;
   7:        }
   8:        else
   9:        { 
  10:            //save the file 
  11:            //restrict user to upload other file extenstion
  12:            string[] FileExt = FileUpload1.FileName.Split('.');
  13:            string FileEx = FileExt[FileExt.Length - 1];
  14:            if (FileEx.ToLower() == "csv")
  15:            {
  16:                FileUpload1.SaveAs(Server.MapPath("CSVLoad//" + FileUpload1.FileName));
  17:            }
  18:            else
  19:            {
  20:                lblMsg.Visible = true;
  21:                return;
  22:            }
  23:        }
  24:  
  25:        //create object for CSVReader and pass the stream
  26:        CSVReader reader = new CSVReader(FileUpload1.PostedFile.InputStream);
  27:        //get the header
  28:        string[] headers = reader.GetCSVLine();        
  29:        DataTable dt = new DataTable();
  30:        //add headers
  31:        foreach (string strHeader in headers)
  32:            dt.Columns.Add(strHeader);
  33:  
  34:        string[] data;
  35:        while ((data = reader.GetCSVLine()) != null)
  36:            dt.Rows.Add(data);
  37:        //bind gridview
  38:        gv.DataSource = dt;
  39:        gv.DataBind();
  40:        
  41:    }

5) create Folder In WebSite CSVLoad for Save Uploaded CSV File.

Thnx

About these ads

65 comments

  1. Sudhanshu · · Reply

    Pz your above mention code is very difficult to understand pz send me easy code to read one-many csv file and save its record into sqlserver database using C#

  2. hi Sudhanshu,
    you just make the Class CSVReader. after that you can easily use that class inbuilt function which are help.

    thnx

    1. hello sir when i m using the above code i gt error
      please help me resolve this error
      Line 152: private static object @__fileDependencies;
      Line 153:
      Line 154: [System.Diagnostics.DebuggerNonUserCodeAttribute()]
      Line 155: public secure_admin_restricted_csvreader_aspx() {
      Line 156: string[] dependencies;

  3. Thanks !

    Nice Article.

  4. Hi, what happen if I have other symbol as delimiter such as “|”?

    1. hi,
      you can change the symbol in the line int nextComma = data.IndexOf(‘,’, fromPos);.

      try it.

      thnx

  5. Thanks!!

  6. Thanks!

    1. Hi sonar,
      Most Welcome.

      thnx

      1. This article is good but you have to describe it…

        Otherwise

        thanks

  7. Great Code, next time please include a seperate file, copying and pasting took forever because of the line numbers… Be kind to your fellow coders.

  8. satyajit · · Reply

    good article, but i need the csv file should be uploaded to sql server table

  9. Thank you very much for this very helpful article. Could you please upload the actual code files since copying and pasting from here is not good?

    For the people who wanted to have the CSV file uploaded to a SQL Server table, just do: “BULK INSERT [Table_Name] FROM ‘[File_Name]‘ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’)”

    – Dmitriy Zasyatkin

    1. How do I import into sql table? I tried the bulk insert, but this is on a web server, the file is in the /app_data folder.

  10. Hi,
    I need to upload a csv file from my local hard disk. I couldnt able to execute this code correctly.Can anyone Help me.

  11. thanks a lot, for this code, just i have to make it in VB.net ;o)

  12. really it was helpfull

    Thanks

  13. Vicky · · Reply

    Very nice & simple code, Thanx for sharing these

  14. This code is exactly what i need.

    Nice article and very helpful.. Thanks for this..

  15. very nice a amigo!

    thanks

  16. thnx…
    your code is working nice…
    now i would like to insert that csv file into sql server 2005…you have any idea/code…

    1. Bipin Singh · · Reply

      Dear Rahul,

      that is very easy. just write a code to insert data in sql server within a
      “while ((data = reader.GetCSVLine()) != null)”
      loop.

  17. Bipin Singh · · Reply

    Heyy,

    thanks a lot for posying this excellent code. it helped me a lot in a very easy way.

    once again thanks a lot for the great code.

  18. Dryer Vent Cleaning · · Reply

    This site rocks!

  19. Hi, thanks for this code.
    where this csv file is stored?
    CSVLoad means at what location?
    Plz help me…..

    1. Hi Sonal,
      please check folder CSVLoad in your web site,csv file stored in..
      create Folder In WebSite CSVLoad for Save Uploaded CSV File which is mention in step 5

      Thnx

  20. yugandhar · · Reply

    Pls send me Easy code for Uploaded CSV File

  21. your code giving wrong out put when
    sentence is like that in CSV
    “It’s my pen, what about you ?”
    Your code will give the put put after reading is
    “It”s my pen, what about you ?”
    Pls give me the solution.

    1. please replace special character.

  22. thx alot it was very easy and useful i try it now :)

  23. Shailesh · · Reply

    Thx a lot

  24. Saved me a night of coding dude. I love you men!

    1. Thanks a lots

      if any kind of improvement in content then give your valuable suggestions.

      Thnx

  25. Jahangir · · Reply

    Nice Coding

    Thanks

  26. k7hulu · · Reply

    Can someone please convert this into VB.NET?

    while ((data = reader.GetCSVLine()) != null)
    dt.Rows.Add(data);

  27. Hi,
    I’m using this code Actually when it read ist time it reads header as well as next 7 lines from csv .but i’m able to get column name…..But next time when i want data which will start from 2nd line.
    but when reads it 2nd time,it starting from 8th line…please help me..how to get only data without columns.

  28. Thanks…

  29. Saiful Islam Sumon · · Reply

    Thanks a lot
    The code is working nice with well design…

  30. florencia · · Reply

    what’s this line in vb.net?

    while ((data = reader.GetCSVLine()) != null)
    dt.Rows.Add(data);

    when im trying to convert, it shows :

    While (InlineAssignHelper(data, reader.GetCSVLine())) IsNot Nothing
    dt.Rows.Add(data)

  31. Deric Edwin · · Reply

    Hey sirs ! So how do i upload this to SQL server using C# ? here’s my code

    protected void Button1_Click(object sender, EventArgs e)
    {
    if (FileUpload1.PostedFile.FileName == string.Empty)
    {
    lblMsg.Visible = true;
    return;
    }
    else
    {
    //save the file
    //restrict user to upload other file extenstion
    string[] FileExt = FileUpload1.FileName.Split(‘.’);
    string FileEx = FileExt[FileExt.Length - 1];
    if (FileEx.ToLower() == “csv”)
    {
    FileUpload1.SaveAs(Server.MapPath(“CSVLoad//” + FileUpload1.FileName));

    }
    else
    {
    lblMsg.Visible = true;
    return;
    }
    }

    //create object for CSVReader and pass the stream HEADERRR
    CSVReader reader = new CSVReader(FileUpload1.PostedFile.InputStream);
    //get the header
    string[] headers = reader.GetCSVLine();
    DataTable dt = new DataTable();
    //add headers
    foreach (string strHeader in headers)
    dt.Columns.Add(strHeader);

    string[] data;

    while ((data = reader.GetCSVLine()) != null)
    {
    string Server = “1xx.xx.1xx.xx”;
    string Username = “sa”;
    string Password = “pa$$w0rd”;
    string Database = “startrack”;

    string ConnectionString = “Address=” + Server + “;”;
    ConnectionString += “User ID=” + Username + “;”;
    ConnectionString += “Password=” + Password + “;”;
    ConnectionString += “Initial Catalog=” + Database;

    SqlConnection SQLConnection = new SqlConnection();

    SQLConnection.ConnectionString = ConnectionString;
    SQLConnection.Open();

    string strSQLCommand = “BULK INSERT startrack.VRM.VendorSelection (rfpRef, rfpCreatedDate, rfpClosingDate, rfpStatus, projectTitle, projectStatus, projectOwner, invitedVendors, selectedVendor, evaluationCriteria, evaluationReport) WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’)”;

    SqlCommand command = new SqlCommand(strSQLCommand, SQLConnection);

    command.CommandText = strSQLCommand;
    command.CommandType = CommandType.Text;

    gv.DataSource = dt;
    gv.DataBind();

    command.Connection = SQLConnection;

    command.ExecuteNonQuery();//execute the ‘insert’ query.
    SQLConnection.Close();
    }

    }

    Your help will be greatly appreciated !

  32. Awesome article Amit..You have really explained it well. Good work, keep it up :)

  33. terence chua · · Reply

    Dear all,

    just wish to know is this code have any requirement?

    My idea is to have a web page to allow user to upload csv file from client machine.

    my web-hosting server will using asp.net with c#. and database using sqlserver.

  34. Lakshmi · · Reply

    Thank you. this code is really working.

  35. Brian A · · Reply

    Great code, but one thing I’d like to change is having the file uploaded to the server first. That is fine for individual webservers but it becomes problematic when you have a webfarm of 4 servers behind BigIP, where one doesn’t know to which of the four servers a file would be uploaded.

    Can you explain what changes would need to be made to the code to handle reading in a CSV file directly from the end user’s desktop? I want to have the user Browse… to a file on their own personal machine, click “Upload”, and have CSVReader process that file directly into the gridview and database without any need for storing it on the webserver first.

    Appreciate any help you can provide, and thanks,
    Brian

  36. Rrojon · · Reply

    This is very helpful.
    Thanks for it………….. :)
    I have a question
    -I have 50 column in my csv file now I want to some of column in the grid view, How can i do this?
    Thanks

    1. VIjin N · · Reply

      string strFileExtention = System.IO.Path.GetExtension(fupCsv.FileName).ToLower();
      if (strFileExtention == “.csv”)
      {
      //create object for CSVReader and pass the stream
      CSVReader reader = new CSVReader(fupCsv.PostedFile.InputStream);
      createEmailTable();
      string[] data;
      while ((data = reader.GetCSVLine()) != null)
      {
      DataRow dr = EmailTable.NewRow();
      dr["Email"] = data[57].ToString();
      EmailTable.Rows.Add(dr);
      }
      gvEmail.DataSource = EmailTable;
      gvEmail.DataBind();

      }

  37. Syamala · · Reply

    Hi this is very nice article.it is very helpful

  38. syamala · · Reply

    Hi everybody.
    I am new to this concept and i have to read some columns information from csv file using asp.net and that are bind it to grid view.How to do this.Plz Help me…..

    Thank you

  39. Rodrigo Pires · · Reply

    Awesome, helped me a lot.

    Thanks.

  40. VIjin N · · Reply

    Thanks

    Very helpfull

  41. I am new to this concept and i have to read some columns information from csv file using asp.net 2010 c# and that are bind it to grid view.How to do this.Plz Help me….

  42. Im using asp.net and vb.net

    Please find me solution to below question, i couldn’t solved

    outside the grid we have a button called Attach, when we click
    the button the files should browse and then selected file should be display
    in gridview.

    Thanks in advance

  43. Hi,

    It is working fine. Can any one tell me about the below

    //add name space System.IO.Stream
    21: public CSVReader(Stream filestream) : this(filestream, null) { }

  44. Nilay Shah · · Reply

    is this code will work for multiple csv upload & reading at a time?

  45. Great thanks,

    I cannot see that you are using the member “objStream” for anything though?!

    Can’t you just delete that?

  46. Hi,
    i had tried to work with the code given above. I tried to convert from C# to vb but there was an error prompt out where:
    from this piece of code in c#:
    “while ((data = reader.GetCSVLine()) != null)”
    i converted to vb :
    While (InlineAssignHelper(data, reader.GetCSVLine())) IsNot Nothing
    dt.Rows.Add(data)

    but the “inline assginhelper” prompt me error..

    anyone can help me??

    thanks

  47. hello sir when i m using the above code i gt error
    please help me resolve this error

    Line 152: private static object @__fileDependencies;
    Line 153:
    Line 154: [System.Diagnostics.DebuggerNonUserCodeAttribute()]
    Line 155: public secure_admin_restricted_csvreader_aspx() {
    Line 156: string[] dependencies;

  48. please help me to resolve this error

  49. Seena Thakur · · Reply

    superb code..i will give 5 star

  50. vickky · · Reply

    Hi sir,
    I want to restrict file upload and download from the desktop can you help

  51. Hi Amit,
    Your work is very good is enjoy it so much. Now the question I have is that within the grid view dynamic creation based on the csv, can I have a button to trigger a calendar control of which the date i choose is input in one of the textboxes. Once i have this data displaying on the grid view how can i make it editable such that I can update edit certain fields and save this changes to the db.

  52. deepesh · · Reply

    I wanted to upload semicolon delimited file ,how to do that since by default csv is saved as comma delimited?

  53. deepesh · · Reply

    I wanted to upload both (Comma and semicolon) delimited Csv file , how to do that since by default csv is saved as comma delimited?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 528 other followers