vb Collection to DataTable | C# Online Compiler

vb Collection to DataTable by lvasquez

Imports System
Imports System.Data
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports System.Xml


Public Module Module1
	Public Sub Main()
		
		Dim strDetail as String = ""
		Dim myDataTable as DataTable = ParseDelimSeparatedVariables("", vbTab, Nothing, True)

		
		For Each row As DataRow In myDataTable.Rows
			strDetail = row("ID")
		Next row
		
	End Sub
	
	
	Function ParseDelimSeparatedVariables(data as String, delimStr As String, schema As DataTable, firstRowIsHeader As Boolean) As DataTable
	
			Const SchemaColumnName As String = "Column Name"
			Const DefaultState As Integer = 0
			Const Instring As Integer = 1
			Const FirstQuote As Integer = 2
	
			Const Quote As Char = """"c
			If delimStr.Length = 0 Then delimStr = ","
			If delimStr.Length <> 1 Then Throw New Exception("Delimiter must be a single character")
			
			Dim delim As Char = delimStr(0)
	
			Dim state As Integer = DefaultState
			Dim firstRow As Boolean = True
			If Not firstRowIsHeader Then
				firstRow = False
			End If
			Dim columnValue As New StringBuilder()
	
			Dim emptySchema As Boolean = schema Is Nothing OrElse schema.Rows.Count = 0
			Dim outputCollection As New DataTable()
	
			If Not emptySchema Then
				For Each schemaRow As DataRow In schema.Rows
					Dim colName As String = schemaRow(SchemaColumnName).ToString
					outputCollection.Columns.Add(colName, GetType(String))
				Next
			End If
	
			Dim row As DataRow = Nothing
			Dim colIndex As Integer = 0
			Using sw As New StringReader(data)
				While True
					Dim line As String = sw.ReadLine()
					If line Is Nothing Then Exit While
	
					' If we're not processing a CRLF in the middle of a string, we want to move
					' onto the next row; if we are, we keep the current row and column since we're
					' still writing to that 'cell'.
					If state <> Instring Then
						row = outputCollection.NewRow
						colIndex = 0
					End If
					For Each ch As Char In line
						Select Case ch
							Case delim ' ie. 'ch' is the specified delimiter - "," or "\t"
								Select Case state
									Case Instring
										columnValue.Append(delim)
									Case Else ' Covers 'default' and 'first quote'.
										If firstRow Then
											If emptySchema Then
												If firstRowIsHeader Then
													Dim colName As String = columnValue.ToString
													outputCollection.Columns.Add(colName, GetType(String))
												End If
											Else
												If firstRowIsHeader Then
													Dim schemaColName As String = schema.Rows(colIndex).Item(SchemaColumnName).ToString
													Dim colName As String = columnValue.ToString
													If colName <> schemaColName Then
														Throw New Exception("Column name mismatch. Column '" & colName & "' dosen't match schema name of '" & schemaColName & "'")
													End If
												End If
											End If
										Else
											If Not firstRowIsHeader Then
												Dim colName As String = "Column " & outputCollection.Columns.Count
												outputCollection.Columns.Add(colName, GetType(String))
											End If
											row.Item(colIndex) = columnValue.ToString
										End If
	
										columnValue.Length = 0
										state = DefaultState
										colIndex += 1
								End Select
							Case Quote
								Select Case state
									Case FirstQuote
										state = Instring
										columnValue.Append(Quote)
									Case Instring
										state = FirstQuote
									Case Else
										' If we find a quote in the middle of a non-quoted cell, it's
										' a literal quote; otherwise (ie. at the start of a cell), it
										' means the cell value is wrapped - go into 'Instring' state
										If columnValue.Length > 0 Then
											columnValue.Append(Quote)
										Else
											state = Instring
										End If
								End Select
							Case Else
								columnValue.Append(ch)
						End Select
					Next
	
					If firstRow Then
						If emptySchema Then
							If firstRowIsHeader Then
								Dim colName As String = columnValue.ToString
								outputCollection.Columns.Add(colName, GetType(String))
							End If
						Else
							If firstRowIsHeader Then
								Dim schemaColName As String = schema.Rows(colIndex).Item(SchemaColumnName).ToString
								Dim colName As String = columnValue.ToString
								If colName <> schemaColName Then
									Throw New Exception("Column name mismatch. Column '" & colName & "' dosen't match schema name of '" & schemaColName & "'")
								End If
							End If
						End If
						firstRow = False
						columnValue.Length = 0
						state = DefaultState
					Else
						' If we're still in the middle of the string we want to include the CRLF in the
						' actual value that we're writing and leave the state at 'Instring'
						If state = Instring Then
							columnValue.Append(vbCrLf)
						Else
							If Not firstRowIsHeader Then
								Dim colName As String = "Column " & outputCollection.Columns.Count
								outputCollection.Columns.Add(colName, GetType(String))
							End If
									
							row.Item(colIndex) = columnValue.ToString
							outputCollection.Rows.Add(row)
							columnValue.Length = 0
							state = DefaultState
						End If
					End If
	
				End While
	
			End Using
			
			Return outputCollection
	
	End Function
	
End Module

​x
 
Imports System
Imports System.Data
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports System.Xml
​
​
Public Module Module1
    Public Sub Main()
        
        Dim strDetail as String = ""
        Dim myDataTable as DataTable = ParseDelimSeparatedVariables("", vbTab, Nothing, True)
​
        
        For Each row As DataRow In myDataTable.Rows
            strDetail = row("ID")
        Next row
        
    End Sub
    
    
    Function ParseDelimSeparatedVariables(data as String, delimStr As String, schema As DataTable, firstRowIsHeader As Boolean) As DataTable
    
            Const SchemaColumnName As String = "Column Name"
            Const DefaultState As Integer = 0
            Const Instring As Integer = 1
            Const FirstQuote As Integer = 2
    
            Const Quote As Char = """"c
            If delimStr.Length = 0 Then delimStr = ","
            If delimStr.Length <> 1 Then Throw New Exception("Delimiter must be a single character")
            
            Dim delim As Char = delimStr(0)
    
            Dim state As Integer = DefaultState
            Dim firstRow As Boolean = True
            If Not firstRowIsHeader Then
                firstRow = False
            End If
            Dim columnValue As New StringBuilder()
    
            Dim emptySchema As Boolean = schema Is Nothing OrElse schema.Rows.Count = 0
            Dim outputCollection As New DataTable()
    
            If Not emptySchema Then
                For Each schemaRow As DataRow In schema.Rows
                    Dim colName As String = schemaRow(SchemaColumnName).ToString
                    outputCollection.Columns.Add(colName, GetType(String))
                Next
            End If
    
            Dim row As DataRow = Nothing
            Dim colIndex As Integer = 0
            Using sw As New StringReader(data)
                While True
                    Dim line As String = sw.ReadLine()
                    If line Is Nothing Then Exit While
    
                    ' If we're not processing a CRLF in the middle of a string, we want to move
                    ' onto the next row; if we are, we keep the current row and column since we're
                    ' still writing to that 'cell'.
                    If state <> Instring Then
                        row = outputCollection.NewRow
                        colIndex = 0
                    End If
                    For Each ch As Char In line
                        Select Case ch
                            Case delim ' ie. 'ch' is the specified delimiter - "," or "\t"
                                Select Case state
                                    Case Instring
                                        columnValue.Append(delim)
                                    Case Else ' Covers 'default' and 'first quote'.
                                        If firstRow Then
                                            If emptySchema Then
                                                If firstRowIsHeader Then
                                                    Dim colName As String = columnValue.ToString
                                                    outputCollection.Columns.Add(colName, GetType(String))
                                                End If
                                            Else
                                                If firstRowIsHeader Then
                                                    Dim schemaColName As String = schema.Rows(colIndex).Item(SchemaColumnName).ToString
                                                    Dim colName As String = columnValue.ToString
                                                    If colName <> schemaColName Then
                                                        Throw New Exception("Column name mismatch. Column '" & colName & "' dosen't match schema name of '" & schemaColName & "'")
                                                    End If
                                                End If
                                            End If
                                        Else
                                            If Not firstRowIsHeader Then
                                                Dim colName As String = "Column " & outputCollection.Columns.Count
                                                outputCollection.Columns.Add(colName, GetType(String))
                                            End If
                                            row.Item(colIndex) = columnValue.ToString
                                        End If
    
                                        columnValue.Length = 0
                                        state = DefaultState
                                        colIndex += 1
                                End Select
                            Case Quote
                                Select Case state
                                    Case FirstQuote
                                        state = Instring
                                        columnValue.Append(Quote)
                                    Case Instring
                                        state = FirstQuote
                                    Case Else
                                        ' If we find a quote in the middle of a non-quoted cell, it's
                                        ' a literal quote; otherwise (ie. at the start of a cell), it
                                        ' means the cell value is wrapped - go into 'Instring' state
                                        If columnValue.Length > 0 Then
                                            columnValue.Append(Quote)
                                        Else
                                            state = Instring
                                        End If
                                End Select
                            Case Else
                                columnValue.Append(ch)
                        End Select
                    Next
    
                    If firstRow Then
                        If emptySchema Then
                            If firstRowIsHeader Then
                                Dim colName As String = columnValue.ToString
                                outputCollection.Columns.Add(colName, GetType(String))
                            End If
                        Else
                            If firstRowIsHeader Then
                                Dim schemaColName As String = schema.Rows(colIndex).Item(SchemaColumnName).ToString
                                Dim colName As String = columnValue.ToString
                                If colName <> schemaColName Then
                                    Throw New Exception("Column name mismatch. Column '" & colName & "' dosen't match schema name of '" & schemaColName & "'")
                                End If
                            End If
                        End If
                        firstRow = False
                        columnValue.Length = 0
                        state = DefaultState
                    Else
                        ' If we're still in the middle of the string we want to include the CRLF in the
                        ' actual value that we're writing and leave the state at 'Instring'
                        If state = Instring Then
                            columnValue.Append(vbCrLf)
                        Else
                            If Not firstRowIsHeader Then
                                Dim colName As String = "Column " & outputCollection.Columns.Count
                                outputCollection.Columns.Add(colName, GetType(String))
                            End If
                                    
                            row.Item(colIndex) = columnValue.ToString
                            outputCollection.Rows.Add(row)
                            columnValue.Length = 0
                            state = DefaultState
                        End If
                    End If
    
                End While
    
            End Using
            
            Return outputCollection
    
    End Function
    
End Module

Last Run:	9:26:48 pm
Compile:	0.189s
Execute:	0.016s
Memory:	16kb
CPU:	0.016s

View IL Code