Monday, 26 April 2010

Delimited Text file To DataSet

Imports System
Imports System.Data
Imports System.IO

Public Class TextToDataSet

Public Sub New()
End Sub

''' Converts a given delimited file into a dataset.
''' Assumes that the first line
''' of the text file contains the column names.

''' The name of the file to open
''' The name of the
''' Table to be made within the DataSet returned
''' The string to delimit by
Public Shared Function Convert(ByVal File As String, ByVal TableName As String, ByVal delimiter As String) As DataSet
'The DataSet to Return
Dim result As New DataSet()

'Open the file in a stream reader.
Dim s As New StreamReader(File)

'Split the first line into the columns
Dim columns As String() = s.ReadLine().Split(delimiter.ToCharArray())

'Add the new DataTable to the RecordSet

'Cycle the colums, adding those that don't exist yet
'and sequencing the one 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("&", "")

'See if the column already exists
If Not result.Tables(TableName).Columns.Contains(columnname) Then
'if it doesn't then we add it here and mark it as added
added = True
'if it did exist then we increment the sequencer and try again.
System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
[next] = "_" + i.ToString()
End If
End While

'Read the rest of the data in the file.
Dim AllData As String = s.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 & vbLf.ToCharArray())

'Now add each row to the DataSet
Dim iCount As Integer = 0
For Each r As String In rows
iCount = iCount + 1
If iCount < rows.Length Then
'Split the row at the delimiter.
Dim items As String() = r.Split(delimiter.ToCharArray())
items = FixEmptyInArray(items)
'Add the item
End If

'Return the imported data.
Return result
End Function

Public Shared Function FixEmptyInArray(ByVal TheArray As Object) As String()
'This function gives max value of int array without sorting an array
For i As Integer = 0 To UBound(TheArray) - 1
If Trim(TheArray(i)) = "" Then
TheArray(i) = Nothing
End If
Return TheArray
End Function
End Class


No comments: