• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

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.
0
dn_learner
Asked:
dn_learner
1 Solution
 
netjkusCommented:
   protected System.Web.UI.HtmlControls.HtmlInputFile sampleFile;
....

    if ( sampleFile.PostedFile.FileName.Length > 0 )
     {
//     .. PERFORM Validations..
     }else
       //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.

0
 
AerosSagaCommented:
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"
           'Else
           If UploadFile.PostedFile.ContentLength > 40000 Then
               lblMessage.Text = "The Size of file is greater than 4 MB"
           ElseIf strFileNameOnly = "" Then
               Exit Sub
           Else
               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")
       myData.Fill(myDataset)

       DataGrid1.DataSource = myDataset.Tables(0).DefaultView
       DataGrid1.DataBind()
   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/vnd.ms-excel"
       ' 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.
       DataGrid1.RenderControl(hw)
       ' Write the HTML back to the browser.
       Response.Write(tw.ToString())
       ' End the response.
       Response.End()
       lblMessage.Text = "For any more information , feel free to contact ...!!!"
   End Sub

Regards,

Aeros
0
 
AerosSagaCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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.
0
 
AerosSagaCommented:
#    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.

Regards,

Aeros
0
 
neeraj523Commented:
i have tried this code
but i am getting error this error ,
External table is not in the expected format.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now