純文字檔案(例如:txt,csv)轉成DataTable的方法

純文字檔案(例如:txt,csv)轉成DataTable的方法


Public Function TxtConvertToDataTable(File As String, TableName As String, delimiter As String) As DataTable
Dim dt As New DataTable()
Dim ds As New DataSet()
Dim s As New StreamReader(File, System.Text.Encoding.[Default])
'string ss = s.ReadLine();//skip the first line
Dim columns As String() = s.ReadLine().Split(delimiter.ToCharArray())
ds.Tables.Add(TableName)
For Each col As String In columns
Dim added As Boolean = False
Dim [next] As String = ""
Dim i As Integer = 0
While Not added
Dim columnname As String = col + [next]
columnname = columnname.Replace("#", "")
columnname = columnname.Replace("'", "")
columnname = columnname.Replace("&", "")

If Not ds.Tables(TableName).Columns.Contains(columnname) Then
ds.Tables(TableName).Columns.Add(columnname.ToUpper())
added = True
Else
i += 1
[next] = "_" + i.ToString()
End If
End While
Next

Dim AllData As String = s.ReadToEnd()
Dim rows As String() = AllData.Split(vbLf.ToCharArray())

For Each r As String In rows
Dim items As String() = r.Split(delimiter.ToCharArray())
ds.Tables(TableName).Rows.Add(items)
Next

s.Close()

dt = ds.Tables(0)

Return dt
End Function