Solved

import CSV file to a table in SQL Server

Posted on 2004-09-22
6
862 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
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now