ASP.NET Import CSV to Database |
ASP.NET Import CSV to Database เป็นการเขียน ASP.NET ใช้ ADO.NET อ่านไฟล์ CSV และทำการ INSERT ลงใน Database
Language Code :  VB.NET || C#
Framework : 1,2,3,4
csv/customer.csv
CustomerID,Name,Email,CountryCode,Budget,Used
"C001","Win Weerachai","[email protected]","TH","1,000,000.00","600,000.00"
"C002","John Smith","[email protected]","EN","2,000,000.00","800,000.00"
"C003","Jame Born","[email protected]","US","3,000,000.00","600,000.00"
"C004","Chalee Angel","[email protected]","US","4,000,000.00","100,000.00"
AspNetCsvToDatabase.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim dt As DataTable
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable("csv/","customer.csv")
'*** Insert to Database ***'
InsertToDatabase(dt)
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>ThaiCreate.Com ASP.NET - CSV</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 04
