Public Function Convert(ByVal mFile As String, ByVal mTablename As String, ByVal delimiter As String) As DataSet
' The dataset to return
Dim ds As New DataSet
' Open the file with a stream reader
Dim sr As New StreamReader(mFile)
' Split the first line into the fields and add to string array called columns
Dim columns As String() = sr.ReadLine().Split(delimiter.ToCharArray())
' Add the new Datatable to the DataSet
ds.Tables.Add(mTablename)
' Cylcle the columns, adding those that do not exist yet and sequencing the ones that do
For Each col As String In columns
Dim added As Boolean = False
Dim _next As String = ""
Dim i As Integer = 0
While Not added
' Build the column name and remove any unwanted characters
Dim columnname As String = col + _next
columnname = columnname.Replace("#", "")
columnname = columnname.Replace("'", "")
columnname = columnname.Replace("&", "")
columnname = columnname.Replace("""", "")
' See if the column already exists
If Not ds.Tables(mTablename).Columns.Contains(columnname) Then
ds.Tables(mTablename).Columns.Add(columnname)
added = True
Else
' If it did exist then we increment the sequencer and try again
i = i + 1
_next = "_" + i.ToString()
End If
End While
Next
' Read the rest of the data in the file
Dim allData As String = sr.ReadToEnd()
' Split off each row at the Carriage Return / Line Feed
' Default line ending in most windows exports
' You may have to edit this to match your particular file
' This will work for Excel, Access etc default exports.
Dim rows As String() = allData.Split(vbCr.ToCharArray)
' Add each row to the Dataset
For Each rowValue As String In rows
' Remove quotation field markers
Dim row As String = rowValue.ToString().Replace("""", "")
' Split the row at the delimiter
Dim items As String() = row.Split(delimiter.ToCharArray())
' Add the item to the dataset
ds.Tables(mTablename).Rows.Add(items)
Next
' Cleanup - Release StreamReader Resources
sr.Close()
sr.Dispose()
' Return the imported data
Return ds
End Function
Regards