Solved

How to read CSV file

Posted on 2004-09-23
4
294 Views
Last Modified: 2012-06-27
Hello,
  Can some one tell me how i can read CSV file and store the information in SQl database.

My CSV file has this type of information

Physician_ID      LastName      FirstName      MI
4      Ananth      Uma       
122      Baluch      John      D
97      Bing      Arthur      G
5      Bohl      Robert      D


My sql databse has same fields as these columns have.


please help me.

Thanks in advance
0
Comment
Question by:mfazi
  • 2
4 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12133841
Create a dataset from it, then if you want it displayed bind it to a datagrid, or just perform your update from there:

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

Accepted Solution

by:
AerosSaga earned 100 total points
ID: 12133858
Heres a more simplistic sample:

You can svae excel into datatable and then get an array from there

'Establish a connection to the data source.
Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\Book2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet4$]", objConn)

Dim ds As New DataSet("ExcelDS")

da.Fill(ds, "ExcelSheet")

Dim dt As DataTable
dt = ds.Tables("ExcelSheet")

Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine("{0} {1}", _
drCurrent("Col1").ToString, _
drCurrent("Col2").ToString)
Next

objConn.Close()

If the first row of the Excel Worksheet does not contain column names then remove the HDR=Yes
Extended Property from the connection string.
0
 
LVL 33

Expert Comment

by:raterus
ID: 12134813
Or you could just write a DTS package if you are doing this over and over again with the same file
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 14536440
Checkout this link:

A Simplified SQL-CSV Import/Export Functionality
http://www.codeproject.com/aspnet/ImportExportCSV.asp

How To Read a CSV-text-file using VB.NET and save into SQL Server
http://www.dotnetspider.com/technology/kbpages/1082.aspx


HTH, Nauman.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert html to string 6 38
ASP.NET gridview select textbox on focus 2 57
What namespace do I need to import? 2 38
Please explain the parts of these 2 LINQ expressions 3 53
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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