import csv into sql table 1

[mwa]

I'm in process of writing an asp.net app that allows the users to upload a csv file to the server. After the file is uploaded, I need to take the data from the csv file and insert it into a sql server table. I've got the upload part working, but what's the best way to insert the data into the table?

mwa
<><

 

[ca8msm]

You could read the data into a DataTable and loop through each row inserting the records one at a time (this may be the safest solution as if any records contain an error you could identify which record(s) were affected and inform the user).

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[mwa]

Sounds good to me... How would that be done?

mwa
<><

 

[ca8msm]

Do you know how to read data in from the csv file?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[mwa]

No.

mwa
<><

 

[ca8msm]

OK - you can use the text driver to get the data into a DataTable. e.g.

        Dim MyConnection As System.Data.Odbc.OdbcConnection
        Dim MyDataTable As New DataTable
        Dim MyDataAdaptor As System.Data.Odbc.OdbcDataAdapter
        Dim strMyConnection, strSavedFolder, strFilename As String
        strSavedFolder = "c:\inetpub\[URL unfurl="true"]wwwroot\MyApp\"[/URL]
        strFilename = "MyFile.csv"
        strMyConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strSavedFolder & ";"
        MyConnection = New Odbc.OdbcConnection(strMyConnection)
        MyDataAdaptor = New System.data.Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", MyConnection)
        MyDataAdaptor.Fill(MyDataTable)

Then, you would need to loop through each row in the DataTable and run an insert command for each row.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[mwa]

OK. I've got that working and I can put the data from the csv into a datagrid on the screen (just for kicks!). Now how do I insert the data into my sql table?

mwa
<><

 

[ca8msm]

OK - here's an example using the Oracle Data Client but you can easily adapt it for which db you are using:

        Dim MyDataRow As DataRow
        Dim strSQL As String = ""
        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
        Dim strConnection As String = CType(configurationAppSettings.GetValue("MyConnectionString", GetType(System.String)), String)
        Dim sqlConnection As OracleClient.OracleConnection
        Dim sqlCommand As OracleClient.OracleCommand

        For Each MyDataRow In MyDataTable.Rows
            strSQL = "INSERT INTO MYTABLE (MYFIELD) VALUES ('" & MyDataRow(0) & "')"
            sqlConnection = New OracleClient.OracleConnection(strConnection)
            sqlConnection.Open()
            sqlCommand = New OracleClient.OracleCommand(strSQL, sqlConnection)
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()
        Next

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[mwa]

That worked great. Thanks for all of your help...

mwa
<><