import CSV file to a table in SQL Server

Hello everyone,

I need help with a situation that iam facing right now.

Here is the problem:

I have a tab limited or comma delimited CSV file. I want to read the contents of that CSV file and import it in one of the tables in the database using ASP.Net and VB.Net or C#.Net.

id firstname lastname
-- ---------- ----------
"1" "John" "Smith"
"2" "Louis" "Garcia"

I assume that the columns in the CSV files and the table in the database match the datatype.

What would be a good approach to do it ? If anyone have a code snippet that does the job, please post it.

Thank You.
Who is Participating?
AerosSagaConnect With a Mentor Commented:
This will import into a dataset and bind to a datagrid, just update your tables with an insert statement at that point.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Code :

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
       Dim intFileNameLength As Integer
       Dim strFileNamePath As String
       Dim strFileNameOnly As String

       If Not (UploadFile.PostedFile Is Nothing) Then
           strFileNamePath = UploadFile.PostedFile.FileName

           intFileNameLength = InStr(1, StrReverse(strFileNamePath), "\")

           strFileNameOnly = Mid(strFileNamePath, (Len(strFileNamePath) - intFileNameLength) + 2)
           Dim paths = Server.MapPath("/excelreading/")

           paths = paths & "Excel/"

           'If File.Exists(paths & strFileNameOnly) Then
           'lblMessage.Text = "Image of Similar name already Exist,Choose other name"
           If UploadFile.PostedFile.ContentLength > 40000 Then
               lblMessage.Text = "The Size of file is greater than 4 MB"
           ElseIf strFileNameOnly = "" Then
               Exit Sub
               strFileNameOnly = Session("AdminID") & "-" & Session("Acountry") & "-" & Format(Date.Today, "mm-dd-yyyy").Replace("/", "-") & ".xls"
               UploadFile.PostedFile.SaveAs(paths & strFileNameOnly)
               lblMessage.Text = "File Upload Success."
               Session("Img") = strFileNameOnly
           End If
       End If
       'End If

       Dim myDataset As New DataSet()
       Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & Server.MapPath("/excelreading/") & "excel/" & strFileNameOnly & ";" & _
       "Extended Properties=Excel 8.0;"

       ''You must use the $ after the object you reference in the spreadsheet
       Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
       myData.TableMappings.Add("Table", "ExcelTest")

       DataGrid1.DataSource = myDataset.Tables(0).DefaultView
   End Sub

   Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
       ' Set the content type to Excel.
       Response.ContentType = "application/"
       ' Remove the charset from the Content-Type header.
       Response.Charset = ""
       ' Turn off the view state.
       Me.EnableViewState = False

       Dim tw As New System.IO.StringWriter()
       Dim hw As New System.Web.UI.HtmlTextWriter(tw)

       ' Get the HTML for the control.
       ' Write the HTML back to the browser.
       ' End the response.
       lblMessage.Text = "For any more information , feel free to contact ...!!!"
   End Sub


   protected System.Web.UI.HtmlControls.HtmlInputFile sampleFile;

    if ( sampleFile.PostedFile.FileName.Length > 0 )
//     .. PERFORM Validations..
       //error and return;

      sampleFile.PostedFile.InputStream.Position = 0;
            byte[] buffer = new byte [sampleFile.PostedFile.ContentLength];

            sampleFile.PostedFile.InputStream.Read(buffer, 0, buffer.Length);      
            // create a business component to read this buffer and send each row to the back end using stored proc.

We have used an XML file and the code sample is too much to paste here. Also, you can search google for reading the content from the buffer/file to get columns (as it is fixed)

Hope this helps.

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

dn_learnerAuthor Commented:
Hi Aerosaga,

Your code looks good; i still to have to try it on my machine. As you know iam in my intermediate stage of programming with .Net .

Here is another scenario.

I have multiple CSV files in one folder called "FileUploads". The CSV files are double quoted and tab delimited.
The first file contains the header columns and the data for each of the columns.
The second file, third and fourth file  just contains rows of data without header columns.

How should i read the data from those multiple CSV files and write to a table in SQL Server 2000 ?

 Please post your snippet of code.

Thank You.
#    OLE DB

    *  Standard:
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

          "HDR=Yes;" indicates that the first row contains columnnames, not data **********************

          "IMEX=1;" tells the driver to always read "intermixed" data columns as text

          TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

The connection string & driver handle the column names as indicated by the asticks above.  You will  have to iterate through each file and update.  As far as the double quotes, etc you will just have to strip them out if they come through on import.  Try the routine on one file, and go from there.  There are just too many variables at this point for me to give you any solid code.


i have tried this code
but i am getting error this error ,
External table is not in the expected format.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.