[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

import CSV file to a table in SQL Server

Posted on 2004-09-22
6
Medium Priority
?
876 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 1600 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
Technology Partners: 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

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.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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