Remove From My Forums

Need to Import from csv not xls

Question

User709978785 posted

I use the following to import from an Excel xls file. I need to do the exact same thing, but using a csv file.

Can anyone help?

                'excel connection STRING
                Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & Server.MapPath("~/Import/ExcelUpload.xls") & ";" & _
                "Extended Properties=Excel 8.0;"

                ' create your excel connection object using the connection string
                Dim objXConn As New OleDbConnection(xConnStr)
                objXConn.Close()

                objXConn.Open()

                ' Get Sheet Name
                Dim dtSheetName As DataTable = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                'Use name of first sheet
                Dim sSheetName As String = dtSheetName.Rows(0)("TABLE_NAME").ToString

                ' retrieve the Select command for the Spreadsheet
                Dim objCommand As New OleDbCommand("SELECT * FROM [" + (sSheetName + "]"), objXConn)


                '  create a DataReader
                Dim reader As OleDbDataReader
                reader = objCommand.ExecuteReader()

Friday, August 31, 2012 3:31 PM

Answers

User1410450975 posted

there is two way to import CSV file in Database one from code behind using .Net and second one is DTS or SSIS (Sql option) i provide you both link you can check and Pick best one for you

First Approch

http://www.mindfiresolutions.com/Importing-data-from-a-CSV-file-into-a-column-with-multiple-commas-447.php

http://www.daniweb.com/software-development/vbnet/threads/52051/importing-csv-file-to-sql-server-using-vb.net

http://www.shotdev.com/aspnet/aspnet-vbnet-csv/aspnet-vbnet-read-csv-file/

Second Approch

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

http://blog.sqlauthority.com/2011/11/02/sql-server-import-csv-into-database-transferring-file-content-into-a-database-table-using-csvexpress/

http://www.codeproject.com/Articles/290242/Import-Data-from-a-Text-or-CSV-file-into-SQL-Serve

Following are the Example Code use this if its help you

Imports System.IO
Imports System.Data.Sql
Imports System.Data.SqlClient


Partial Class ImportDelimitedFile


    Inherits System.Web.UI.Page


    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click


        DoFileUPload()
        DoFileSplittoArray()


    End Sub


    Public Sub DoFileSplittoArray()
        Dim FILENAME As String = Server.MapPath("tempACH/myfile.txt")


        Dim sr As StreamReader
        sr = File.OpenText(FILENAME)
        Dim contents As String
        While sr.Peek() <> -1
            contents = sr.ReadLine
            DoSQLInsert(contents.Split(DropDownListDelimitingCharacter.SelectedItem.Value))
        End While
        sr.close()
    End Sub


    Public Sub DoFileUPload()


        FileUpload1.PostedFile.SaveAs(Server.MapPath("tempACH/") & "myfile.txt")


    End Sub


    Public Sub DoSQLInsert(ByRef MyArray As String())


        Dim sqlconn As New SqlConnection("Connection_String_goes_here")
        Dim sqlcmd As New SqlCommand("INSERT INTO yourTable (fields) VALUES (@params)", sqlconn)


        date and time
        sqlcmd.Parameters.AddWithValue("@param", MyArray(0).ToString)
        

        sqlcmd.Connection.Open()
        sqlcmd.ExecuteScalar()
        sqlcmd.Connection.Close()
    End Sub


    Protected Function RemoveDollarSign(ByRef removefrom As String) As String
        Return Replace(removefrom, "$", "")
    End Function


End Class

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Saturday, September 1, 2012 12:55 AM

Answered by:

Need to Import from csv not xls

Question

Answers