Solved

import CSV file to a table in SQL Server

Posted on 2004-09-22
6
870 Views
Last Modified: 2010-05-18
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
Comment
Question by:dn_learner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Expert Comment

by:netjkus
ID: 12125414
   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
 
LVL 17

Accepted Solution

by:
AerosSaga earned 400 total points
ID: 12125507
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12125526
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dn_learner
ID: 12126458
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12126736
#    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
 
LVL 22

Expert Comment

by:neeraj523
ID: 14613692
i have tried this code
but i am getting error this error ,
External table is not in the expected format.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question