ASP.NET(vb.net) & Upload and Import CSV to Database

Author: admin | Posted: 19-06-2010 | Category: ASP.NET(vb.net) & CSV

ASP.NET(vb.net) & Upload and Import CSV to Database - In this article we will example scripts ASP.NET how to upload CSV to server and import CSV to database.

ShotDev Focus:
- ASP.NET(vb.net) & Upload and Import CSV to Database

Example

AspNetUploadCsvToDatabase.aspx

view plain copy to clipboard print ?

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then
Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)
'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub
'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function InsertToDatabase(ByVal dt)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1
Try
strSQL = "INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('"&dt.Rows(i)("CustomerID")&"','"&dt.Rows(i)("Name")&"','"&dt.Rows(i)("Email")&"'" & _
" ,'"&dt.Rows(i)("CountryCode")&"','"&dt.Rows(i)("Budget")&"','"&dt.Rows(i)("Used")&"')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Not Insert <br>"
End Try
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Function
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick" value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then

Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))

'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)

'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub

'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable

Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"

objConn = New OleDbConnection(strConnString)
objConn.Open()

Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName

dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)

dtAdapter = Nothing

objConn.Close()
objConn = Nothing

Return dt '*** Return DataTable ***'

End Function

Function InsertToDatabase(ByVal dt)

Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()

'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1

Try
strSQL = "INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('"&dt.Rows(i)("CustomerID")&"','"&dt.Rows(i)("Name")&"','"&dt.Rows(i)("Email")&"'" & _
" ,'"&dt.Rows(i)("CountryCode")&"','"&dt.Rows(i)("Budget")&"','"&dt.Rows(i)("Used")&"')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Not Insert <br>"
End Try

Next

objCmd = Nothing
objConn.Close()
objConn = Nothing

End Function

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick"  value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>

Screenshot