Public Function GetCSVDataTable(ByVal filePath As String) As DataTable
' The dataset to return
Dim dt As DataTable = Nothing
Try
'--------------------------------------------------------------------
' Create "Excel Like" Column Headers
'--------------------------------------------------------------------
Dim columns() As String = {"A", "B", "C", "D", "E", "F", "G", "H"}
'--------------------------------------------------------------------
' Create dataset and datatable to hol;d the csv data
'--------------------------------------------------------------------
Dim ds As New DataSet
Dim mTablename As String = "table1"
' Add the new Datatable to the DataSet
ds.Tables.Add(mTablename)
dt = ds.Tables("table1")
'--------------------------------------------------------------------
' Add columns to datatable
'--------------------------------------------------------------------
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
'--------------------------------------------------------------------
' Open the CSV file with a stream reader
'--------------------------------------------------------------------
Dim sr As New StreamReader(filePath)
'--------------------------------------------------------------------
' Read the entire CSV string into one big string
'--------------------------------------------------------------------
Dim allData As String = sr.ReadToEnd()
'--------------------------------------------------------------------
' Split off each CSV 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
'--------------------------------------------------------------------
Dim rows As String() = allData.Split(vbCr.ToCharArray)
'--------------------------------------------------------------------
' Add each row to the Dataset until _MaxRowsCount met
'--------------------------------------------------------------------
Dim rowCount As Integer = 0
For Each rowValue As String In rows
rowCount += 1
If rowCount > Me._MaxRowsCount Then Exit For
'--------------------------------------------------------------------
' Remove quotation field markers
'--------------------------------------------------------------------
Dim row As String = rowValue.ToString().Replace("""", "")
'--------------------------------------------------------------------
' Split the row at the delimiter
'--------------------------------------------------------------------
Dim rowItems As String() = row.Split(",".ToCharArray())
'--------------------------------------------------------------------
' set array to Me._MaxColsCount - 1
'--------------------------------------------------------------------
ReDim Preserve rowItems(Me._MaxColsCount - 1)
'--------------------------------------------------------------------
' Add the rowItems to a new DataSet.DataTable.DataRow
'--------------------------------------------------------------------
ds.Tables(mTablename).Rows.Add(rowItems)
Next
' Cleanup - Release StreamReader Resources
sr.Close()
sr.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
' Return the imported data
Return dt
End Function
Private Sub ValidateDataInGrid()
' cell init
dataGridViewRow.Cells(iCol).Style.BackColor = _colorBackLnum
sb = New StringBuilder
dataGridViewRow.Cells(iCol).ToolTipText = ""
dataGridViewRow.Cells(iCol).Tag = ""
' cell rules
If x.Length < 17 Then
sb.Append("Field is too short must be 17 charaters." + vbCrLf)
dataGridViewRow.Cells(iCol).Style.BackColor = Me._colorErrorManual
cellErrorCount += 1
ElseIf x.Length > 17 Then
sb.Append("Field is too long must be 17 characters." + vbCrLf)
dataGridViewRow.Cells(iCol).Style.BackColor = Me._colorErrorManual
cellErrorCount += 1
End If
' cell right-clickable error
If compareDtPointer < _CompareDt.Rows.Count Then
Dim lnumber = _CompareDt.Rows(compareDtPointer)("Field").ToString.Replace("-", "").ToUpper
If x.ToUpper <> lnumber Then
sb = New StringBuilder
sb.Append("Right click to change Field to " + lnumber + "." + vbCrLf)
cellErrorCount += 1
dataGridViewRow.Cells(iCol).Tag = "rightClickable"
dataGridViewRow.Cells(iCol).Style.BackColor = _colorRightClickable
End If
End If
' cell results
dataGridViewRow.Cells(iCol).Value = x
Dim err As String = sb.ToString
If err <> "" Then
dataGridViewRow.Cells(iCol).ToolTipText = err
End If
End Sub
Dim _CellRow As Integer = -1
Dim _CellCol As Integer = -1
Private Sub gvCSV_CellMouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles gvCSV.CellMouseDown
'--------------------------------------------------------------------
'right mouse click?
'--------------------------------------------------------------------
If e.Button = Windows.Forms.MouseButtons.Right Then
'--------------------------------------------------------------------
'yes, if right clickable
' get column & row of grid cell
' Isolate last string from tooltip message:
' e.g. "Right click to change Field to L2345678901234567." --> "L2345678901234567"
' change cell text to "L2345678901234567"
'--------------------------------------------------------------------
Dim col As Integer = e.ColumnIndex
Dim row As Integer = e.RowIndex
If gvCSV.Item(col, row).Tag.ToString.ToLower = "rightclickable" Then
Dim toolTip As String = gvCSV.Item(col, row).ToolTipText.Replace(vbCrLf, "").Replace(".", "")
Dim toolTipArray As String() = toolTip.Split(" ".ToCharArray)
Dim x As String = toolTipArray(toolTipArray.Length - 1)
gvCSV.Item(col, row).Value = x
gvCSV.Rows(row).Cells(col).Style.BackColor = Me._colorEdited
End If
End If
End Sub
Private Sub gvCSV_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gvCSV.CellEnter
_CellCol = e.ColumnIndex
_CellRow = e.RowIndex
End Sub
Private Sub gvCSV_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles gvCSV.EditingControlShowing
If Not e.Control Is Nothing Then 'gvCSV.CurrentCell.ColumnIndex = 1 And Not
Dim tb As TextBox = CType(e.Control, TextBox)
'---add an event handler to the TextBox control---
AddHandler tb.KeyPress, AddressOf gvCSV_KeyPress
AddHandler tb.TextChanged, AddressOf gvCSV_TextChanged
End If
End Sub
Private Sub gvCSV_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles gvCSV.KeyDown
'Nothing to do
End Sub
Private Sub gvCSV_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles gvCSV.KeyPress
'--------------------------------------------------------------------
'Allow only A-Z or Numeric (supress all other keys
'--------------------------------------------------------------------
If e.KeyChar Like "[A-z]" Or IsNumeric(e.KeyChar) Then
e.Handled = False
Else
e.Handled = True
End If
End Sub
Private Sub gvCSV_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFilePath.TextChanged
'Nothing to do
End Sub
Private Sub gvCSV_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gvCSV.CellEndEdit
'--------------------------------------------------------------------
'chane cell backcolor to "Edited"
'--------------------------------------------------------------------
If _CellRow <> -1 And _CellRow <> -1 Then
gvCSV.Rows(_CellRow).Cells(_CellCol).Style.BackColor = Me._colorEdited
'No! -> Application.DoEvents()
End If
End Sub