CSV to SQL Server

Igor Krupitsky
Rate me:
5.00/5 (12 votes)
11 Jul 2023CPOL1 min read
14.3K
631
This application lets you copy tables from folder with CSV files to SQL Server
I developed this application to help me migrate CSV files to SQL Server. I hope someone else will also find this code useful.
Download source files - 178.3 KB
Introduction

I developed this application to help me migrate CSV files to SQL Server. It was too much trouble importing the files using SSIS and SSMS import flat file feature.
The application assumes that the first row contains column names. I hope someone else will also find this code useful.
Background

This application is fairly simple: you select the folder where CSV files reside, select the SQL Server database you want to copy the tables, select the tables you want to copy and click Copy tables. The application will create tables in the SQL server database and copy the data.
The imported files will be of varchar(max) / varchar(max) type (depending on Use nvarchar setting). But if Shrink table is checked, the application will attempt to detect and change data types based on the data.
Other features:
Create target table - will create table if it does not exist
Add rec_id - will add primary key called rec_id identity(1,1)
Delete before insert - will delete all records before coping data
Drop table if exists
Delimiter - column data delimiter. Comma (,) is default.
Check New Rec - check tables that different source and destination count
Check All - check all tables
Uncheck All - uncheck all tables
Script to File - will log to file instead of executing the SQL
Using the Code

The application uses TextFieldParser to read CSV files.
VB.NET
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Runtime.InteropServices

Public Class Form1
    Inherits System.Windows.Forms.Form

    Private bStop As Boolean = False

    Dim oAppSetting As New AppSetting
    Friend WithEvents chkHideNotSelected As System.Windows.Forms.CheckBox
    Dim oSelTables As New Hashtable
    Dim oSqlScriptWriter As System.IO.StreamWriter = Nothing

    Friend WithEvents selDelimiter As ComboBox
    Friend WithEvents chkShrinkTable As CheckBox
    Friend WithEvents btnDeleteFolderCache As Button
    Friend WithEvents btnShrinkTables As Button
    Friend WithEvents chkRec_id As CheckBox
    Friend WithEvents chkNvarChar As CheckBox
    Friend WithEvents chkScriptToFile As CheckBox
    Friend WithEvents ToolTip1 As ToolTip
    Friend WithEvents chkBulkInsert As CheckBox
    Friend WithEvents Label3 As Label

    Private Sub frmExport_Load(sender As System.Object, e As System.EventArgs) _
                               Handles MyBase.Load

        txtFolderPath.Text = oAppSetting.GetSetting("FolderPath")
        txtConnectTo.Text = oAppSetting.GetSetting("ConnectTo")
        oSelTables = GetSelectedTablesFromReg()

        chkCreateTable.Checked = GetBoolSetting("chkCreateTable", True)
        chkDropTable.Checked = GetBoolSetting("chkDropTable", True)

        chkRec_id.Checked = GetBoolSetting("chkRec_id", True)
        chkDeleteData.Checked = GetBoolSetting("chkDeleteData", True)

        chkHideNotSelected.Checked = GetBoolSetting("chkHideNotSelected", False)
        chkShrinkTable.Checked = GetBoolSetting("chkShrinkTable", True)

        chkCreateTable_CheckedChanged()

        Dim sDelimiter As String = oAppSetting.GetSetting("Delimiter")
        If sDelimiter <> "" Then
            Try
                selDelimiter.SelectedIndex = selDelimiter.FindString(sDelimiter)
            Catch ex As Exception
                'Ignore
            End Try
        End If

        If selDelimiter.SelectedIndex = -1 Then
            selDelimiter.SelectedIndex = 0
        End If

        ToolTip1.SetToolTip(chkScriptToFile, "Files will be created in CSV folder")
        ToolTip1.SetToolTip(chkRec_id, "Cannot use BULK INSERT")
        'Bulk Insert

    End Sub

    Private Function GetBoolSetting_
            (ByVal sKey As String, ByVal bDefault As Boolean) As Boolean
        Dim s As String = oAppSetting.GetSetting(sKey)
        If s = "1" Then Return True
        If s = "0" Then Return False
        Return bDefault
    End Function

    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        If System.IO.Directory.Exists(txtFolderPath.Text) = False Then
            txtFolderPath.Text = ""
        End If

        Windows.Forms.Application.DoEvents()
        SetTableGrid(False)

        Dim sSortedColumn As String = oAppSetting.GetSetting("SortedColumn")
        If sSortedColumn <> "" Then
            Dim sSortOrder As String = oAppSetting.GetSetting("SortOrder")
            If sSortOrder = "Ascending" Then
                dgTables.Sort(dgTables.Columns(sSortedColumn), _
                              System.ComponentModel.ListSortDirection.Ascending)
            Else
                dgTables.Sort(dgTables.Columns(sSortedColumn), _
                              System.ComponentModel.ListSortDirection.Descending)
            End If
        End If
    End Sub

    Private Function GetCsvDelimeter2() As String
        Dim sFieldTerm As String = selDelimiter.Text

        Select Case Trim(sFieldTerm)
            Case "Tab" : Return "\t"
            Case Else : Return CChar(sFieldTerm) '|
        End Select
    End Function

    Private Function GetCsvDelimeter() As Char
        Dim sFieldTerm As String = selDelimiter.Text

        Select Case Trim(sFieldTerm)
            Case "Tab" : Return CChar(vbTab)
                'Case "c", "" : Return CChar(",")
            Case Else : Return CChar(sFieldTerm) '|
        End Select
    End Function

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) _
                                  Handles Me.FormClosing

        Dim oHash As New Hashtable
        oHash("FolderPath") = txtFolderPath.Text
        oHash("ConnectTo") = txtConnectTo.Text
        oHash("Delimiter") = selDelimiter.SelectedText

        oHash("chkCreateTable") = IIf(chkCreateTable.Checked, "1", "0").ToString()
        oHash("chkDropTable") = IIf(chkDropTable.Checked, "1", "0").ToString()

        oHash("chkRec_id") = IIf(chkRec_id.Checked, "1", "0").ToString()
        oHash("chkDeleteData") = IIf(chkDeleteData.Checked, "1", "0").ToString()

        oHash("chkHideNotSelected") = _
               IIf(chkHideNotSelected.Checked, "1", "0").ToString()
        oHash("chkShrinkTable") = IIf(chkShrinkTable.Checked, "1", "0").ToString()

        If Not dgTables.SortedColumn Is Nothing Then
            oHash("SortedColumn") = dgTables.SortedColumn.Name
            oHash("SortOrder") = dgTables.SortOrder.ToString()
        End If

        Dim oTables As List(Of String) = GetSelectedTables()
        Dim sTables As String = String.Join(",", oTables.ToArray)

        oHash("SelectedTables") = sTables

        oAppSetting.SaveSettings(oHash)
    End Sub

    Private Function GetSelectedTablesFromReg() As Hashtable

        Dim oSelTables As New Hashtable
        Dim sSelectedTables As String = oAppSetting.GetSetting("SelectedTables")
        If sSelectedTables <> "" Then
            Dim oSelectedTables As String() = Split(sSelectedTables, ",")
            For i As Integer = 0 To oSelectedTables.Length - 1
                Dim sTable As String = oSelectedTables(i)
                oSelTables(sTable) = True
            Next
        End If
        Return oSelTables
    End Function

    Private Sub btnConnect_Click_
    (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect1.Click

        FolderBrowserDialog1.SelectedPath = txtFolderPath.Text
        FolderBrowserDialog1.ShowDialog()

        If FolderBrowserDialog1.SelectedPath = "" Then
            dgTables.Rows.Clear()
            Exit Sub
        End If

        If FolderBrowserDialog1.SelectedPath = "" Then
            Exit Sub
        End If

        txtFolderPath.Text = FolderBrowserDialog1.SelectedPath

        SetTableGrid(True)
    End Sub

    Sub SetTableGrid(ByVal bRefresh As Boolean)

        If txtFolderPath.Text = "" Then
            Exit Sub
        End If

        Dim sSortOrder As String = ""
        Dim sSortedColumn As String = ""
        If bRefresh Then
            'Update oSelTables by selected tables from grid
            UpdateSelectedTables()

            If Not dgTables.SortedColumn Is Nothing Then
                sSortedColumn = dgTables.SortedColumn.Name
                sSortOrder = dgTables.SortOrder.ToString()
            End If
        End If

        Dim dStart As DateTime = Now
        Dim oSqlServerTables As System.Data.DataTable = GetSqlServerTables()

        If (dStart - Now).TotalSeconds > 10 Then
            Log("GetSqlServerTables " & (dStart - Now).TotalSeconds)
        End If

        dStart = Now

        Dim oTable As Data.DataTable = GetFilesTable(txtFolderPath.Text)

        If (dStart - Now).TotalSeconds > 10 Then
            Log("GetFilesTable " & (dStart - Now).TotalSeconds)
        End If

        If oTable Is Nothing Then
            Exit Sub
        End If

        'Update Checked, DestRowCount
        For iRow As Integer = 0 To oTable.Rows.Count - 1
            Dim sTableName As String = oTable.Rows(iRow)("Name").ToString()

            oTable.Rows(iRow)("Checked") = oSelTables.ContainsKey(sTableName)

            If Not oSqlServerTables Is Nothing Then
                Dim oRows As Data.DataRow() = _
                    oSqlServerTables.Select("Name='" & sTableName & "'")
                If oRows.Length > 0 Then
                    oTable.Rows(iRow)("DestRowCount") = oRows(0)("Rows")
                End If
            End If
        Next

        dgTables.DataSource = oTable
        dgTables.Update()

        Dim oCol As DataGridViewCheckBoxColumn = _
            DirectCast(dgTables.Columns("Checked"), DataGridViewCheckBoxColumn)
        oCol.TrueValue = True
        oCol.SortMode = DataGridViewColumnSortMode.Automatic
        oCol.Width = 35
        oCol.HeaderText = ""

        dgTables.Columns("DestRowCount").Visible = Not oSqlServerTables Is Nothing

        UpdateDataColumn("DateModified", "", "Date Modified")
        UpdateDataColumn("Size", "#,#", "Size")
        UpdateDataColumn("RowCount", "#,#", "Src Row Count")
        UpdateDataColumn("DestRowCount", "#,#", "Dest Row Count")

        SetupBackground()

        If sSortedColumn <> "" Then
            If sSortOrder = "Ascending" Then
                dgTables.Sort(dgTables.Columns(sSortedColumn), _
                         System.ComponentModel.ListSortDirection.Ascending)
            Else
                dgTables.Sort(dgTables.Columns(sSortedColumn), _
                         System.ComponentModel.ListSortDirection.Descending)
            End If
        End If

    End Sub

    Private Sub UpdateSelectedTables()

        oSelTables = New Hashtable
        Dim oTables As List(Of String) = GetSelectedTables()

        For i As Integer = 0 To oTables.Count - 1
            Dim sTable As String = oTables(i).ToString
            oSelTables(sTable) = True
        Next
    End Sub

    Private Sub SetupBackground()
        For iRow = 0 To dgTables.RowCount - 1
            Dim sSrcCount As String = _
                dgTables.Rows(iRow).Cells("RowCount").Value.ToString()
            Dim sDstCount As String = _
                dgTables.Rows(iRow).Cells("DestRowCount").Value.ToString()
            If sSrcCount <> "" AndAlso sDstCount <> "" Then
                If CInt(sSrcCount) = CInt(sDstCount) Then
                    dgTables.Rows(iRow).Cells("DestRowCount").Style.BackColor = _
                                                              Color.LightBlue
                Else
                    dgTables.Rows(iRow).Cells("DestRowCount").Style.BackColor = _
                                                              Color.LightPink
                End If
            Else
                dgTables.Rows(iRow).Cells("DestRowCount").Style.BackColor = Color.White
            End If
        Next
    End Sub

    Private Sub CheckCompare(sCol1 As String, sCol2 As String)
        For iRow = 0 To dgTables.RowCount - 1
            Dim sColVal1 As String = dgTables.Rows(iRow).Cells(sCol1).Value.ToString()
            Dim sColVal2 As String = dgTables.Rows(iRow).Cells(sCol2).Value.ToString()
            If sColVal1 <> "" AndAlso sColVal2 <> "" AndAlso sColVal1 <> sColVal2 Then
                dgTables.Rows(iRow).Cells("Checked").Value = True
            Else
                dgTables.Rows(iRow).Cells("Checked").Value = False
            End If
        Next
    End Sub

    Private Function GetFilesTable(ByVal sFolderPath As String) As Data.DataTable
        If chkHideNotSelected.Checked = False Then
            Return GetFilesTable2(sFolderPath)
        End If

        Dim oTable As Data.DataTable = GetFilesTable2(sFolderPath)
        For i As Integer = oTable.Rows.Count - 1 To 0 Step -1
            Dim sTableName As String = oTable.Rows(i)("Name").ToString()
            If oSelTables.ContainsKey(sTableName) = False Then
                oTable.Rows(i).Delete()
            End If
        Next

        Return oTable
    End Function

    Private Function GetFilesTable2(ByVal sFolderPath As String) As Data.DataTable

        'Try to get list if files from cache
        Dim sTempFilePath As String = GetTempFileName(sFolderPath, "CsvCopyXml")
        Dim ds As New System.Data.DataSet()
        If IO.File.Exists(sTempFilePath) Then
            Dim oFileInfo As New IO.FileInfo(sTempFilePath)
            If DateTime.Now.Subtract(oFileInfo.LastWriteTime).Hours > 2 Then
                'File is 2 hours old - delete
                System.IO.File.Delete(sTempFilePath)
            Else
                btnDeleteFolderCache.Visible = True
                txtFolderPath.Width = txtConnectTo.Width - 100
                ds.ReadXml(sTempFilePath)
                Return ds.Tables(0)
            End If
        End If

        txtFolderPath.Width = txtConnectTo.Width
        btnDeleteFolderCache.Visible = False

        Dim dStart As DateTime = DateTime.Now

        Dim oTable As New Data.DataTable
        oTable.Columns.Add(New Data.DataColumn_
               ("Checked", System.Type.GetType("System.Boolean"))) '<--
        oTable.Columns.Add(New Data.DataColumn("Name"))
        oTable.Columns.Add(New Data.DataColumn_
               ("DateModified", System.Type.GetType("System.DateTime")))
        oTable.Columns.Add(New Data.DataColumn_
               ("Size", System.Type.GetType("System.Int64")))
        oTable.Columns.Add(New Data.DataColumn_
               ("RowCount", System.Type.GetType("System.Int64")))
        oTable.Columns.Add(New Data.DataColumn_
               ("DestRowCount", System.Type.GetType("System.Int64"))) '<--

        Dim oFiles As String()

        Try
            oFiles = System.IO.Directory.GetFiles(sFolderPath)
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        End Try

        For i As Integer = 0 To oFiles.Length - 1
            Dim sFilePath As String = oFiles(i)
            Dim oFileInfo As New IO.FileInfo(sFilePath)
            Dim sTableName As String = IO.Path.GetFileNameWithoutExtension(sFilePath)
            If oFileInfo.Extension.ToLower() = ".csv" Then
                Dim iRowCount As Integer = GetRecCount(sFolderPath, sTableName)

                If iRowCount > 0 Then
                    'remove header row from the row count
                    iRowCount += -1
                End If

                Dim oDataRow As DataRow = oTable.NewRow()
                oDataRow("Name") = sTableName
                oDataRow("DateModified") = oFileInfo.LastWriteTime
                oDataRow("Size") = oFileInfo.Length

                If iRowCount > 0 Then
                    oDataRow("RowCount") = iRowCount
                End If

                oTable.Rows.Add(oDataRow)
            End If
        Next

        Dim oDuration As TimeSpan = DateTime.Now.Subtract(dStart)
        If oDuration.Seconds > 5 Then
            'Save to Cache if it took more than 5 seconds to get the list of tables
            ds.Tables.Add(oTable)
            ds.WriteXml(sTempFilePath, XmlWriteMode.WriteSchema)

            btnDeleteFolderCache.Visible = True
            txtFolderPath.Width = txtConnectTo.Width - 100
        End If

        Return oTable
    End Function

    Private oRowCount As New Hashtable()

    Private Function GetRecCount_
        (ByVal sFolderPath As String, ByVal sTableName As String) As Integer
        Dim sFilePath As String = IO.Path.Combine(sFolderPath, sTableName & ".csv")
        Return CountLinesInFile(sFilePath)
    End Function

    Public Function CountLinesInFile(filePath As String) As Integer

        If IO.File.Exists(filePath) = False Then
            Return 0
        End If

        Dim i As Integer = 0
        Using reader As New System.IO.StreamReader(filePath)
            While reader.ReadLine() IsNot Nothing
                i += 1
            End While
        End Using
        Return i
    End Function

    Private Function GetTempFileName(ByVal sKey As String, sExt As String) As String
        Dim oRegex As New Regex(String.Format("[{0}]", _
        Regex.Escape(New String(IO.Path.GetInvalidFileNameChars()))), _
        RegexOptions.Compiled)
        Dim sFileName As String = oRegex.Replace(sKey, "-") & "." & sExt
        Return IO.Path.Combine(GetTempFolderPath(), sFileName)
    End Function

    Private Function GetSqlServerTables() As Data.DataTable

        If txtConnectTo.Text = "" Then
            Return Nothing
        End If

        Dim cn As OleDbConnection = New OleDbConnection(txtConnectTo.Text)

        Try
            cn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        End Try

        Dim sSql As String = "SELECT TABLE_SCHEMA, _
        TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
        Dim oTable As Data.DataTable = cn.GetOleDbSchemaTable_
        (OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

        Dim oRetTable As New Data.DataTable
        oRetTable.Columns.Add(New Data.DataColumn("Name"))
        oRetTable.Columns.Add("Rows", System.Type.GetType("System.Int64"))

        For i As Long = 0 To oTable.Rows.Count - 1
            Dim sSchema As String = oTable.Rows(i)("TABLE_SCHEMA") & ""
            Dim sTName As String = oTable.Rows(i)("TABLE_NAME") & ""
            Dim sKey As String = sSchema & "." & sTName

            If sSchema <> "sys" Then
                If sSchema = "" Or sSchema = "dbo" Then
                    sKey = sTName
                End If

                Try
                    Dim cmd As New OleDbCommand("sp_MStablespace '" & sKey & "'", cn)
                    Dim dr As OleDbDataReader = cmd.ExecuteReader()
                    If dr.Read Then
                        Dim iRowCount As Integer = _
                            CInt(dr.GetValue(dr.GetOrdinal("Rows")))
                        If iRowCount > 0 Then
                            Dim oDataRow As DataRow = oRetTable.NewRow()
                            oDataRow("Name") = sKey
                            oDataRow("Rows") = iRowCount
                            oRetTable.Rows.Add(oDataRow)
                        End If
                    End If
                    dr.Close()
                Catch ex As Exception
                    'Do Nothing
                End Try
            End If

        Next

        cn.Close()
        Return oRetTable
    End Function

    Private Function GetFolderPath() As String
        Return txtFolderPath.Text
    End Function

    Private Sub UpdateDataColumn_
        (sColName As String, sFormat As String, sHeaderText As String)
        Dim oCol As DataGridViewColumn = dgTables.Columns(sColName)
        If sFormat <> "" Then oCol.DefaultCellStyle.Format = sFormat
        If sHeaderText <> "" Then oCol.HeaderText = sHeaderText
    End Sub

    Protected Function EditConnectionString(ByVal sConnectionString As String) As String
        Try
            Dim oDataLinks As Object = CreateObject("DataLinks")
            Dim cn As Object = CreateObject("ADODB.Connection")

            cn.ConnectionString = sConnectionString
            oDataLinks.hWnd = Me.Handle

            If Not oDataLinks.PromptEdit(cn) Then
                'User pressed cancel button
                Return ""
            End If

            cn.Open()

            Return cn.ConnectionString

        Catch ex As Exception
            MsgBox(ex.Message)
            Return ""
        End Try
    End Function

    Function GetSelectedTables() As List(Of String)
        Dim oRet As New List(Of String)

        For Each oRow As DataGridViewRow In dgTables.Rows
            Dim oCheckbox As DataGridViewCheckBoxCell = _
                DirectCast(oRow.Cells.Item(0), DataGridViewCheckBoxCell)

            If oCheckbox.Value.ToString = oCheckbox.TrueValue.ToString() Then
                Dim sName As String = oRow.Cells(1).Value.ToString()
                oRet.Add(sName)
            End If
        Next

        Return oRet
    End Function

    Private Sub btnCopy_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles btnCopy.Click

        If dgTables.Rows.Count = 0 Then
            MsgBox("Please connect to the source database.")
            Exit Sub
        End If

        Dim oTables As List(Of String) = GetSelectedTables()
        If oTables.Count = 0 Then
            MsgBox("Please select tables to copy.")
            Exit Sub
        End If

        If oTables.Count = 0 Then
            Exit Sub
        End If

        Dim cn As OleDbConnection = New OleDbConnection(txtConnectTo.Text)

        Try
            cn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        ProgressBar2.Maximum = oTables.Count
        bStop = False

        For i As Integer = 0 To oTables.Count - 1
            ProgressBar2.Value = i
            ProgressBar2.Refresh()
            Windows.Forms.Application.DoEvents()

            Dim sTable As String = oTables(i).ToString
            If Len(sTable) > 128 Then
                Log("Table name " & sTable & " is " & Len(sTable) & _
                    " characters long. It can be 128 max.")
            Else
                CopyTable(sTable, cn)

                If chkShrinkTable.Checked AndAlso chkScriptToFile.Checked = False Then
                    Dim dStart As DateTime = DateTime.Now
                    Try
                        Shrink(cn, sTable)
                        Dim oDuration As TimeSpan = DateTime.Now.Subtract(dStart)
                        Log("Shrunk table " & sTable & " in " & _
                             oDuration.Seconds & " seconds")
                    Catch ex As Exception
                        Log("Could not shrink table " & sTable & ". " & ex.Message)
                    End Try
                End If

                If bStop Then
                    Exit For
                End If
            End If
        Next

        ProgressBar2.Value = 0
        cn.Close()
        SetTableGrid(True)
    End Sub

    Private Sub btnShrinkTables_Click(sender As Object, e As EventArgs) _
                                      Handles btnShrinkTables.Click

        Dim cn As OleDbConnection = New OleDbConnection(txtConnectTo.Text)

        Try
            cn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        For iRow = 0 To dgTables.RowCount - 1
            Dim bChecked As Boolean = CBool(dgTables.Rows(iRow).Cells("Checked").Value)
            If bChecked Then
                Dim dStart As DateTime = DateTime.Now
                Dim sTable As String = dgTables.Rows(iRow).Cells("Name").Value.ToString()

                If chkScriptToFile.Checked Then
                    'ExecuteCommand will log to file instead of executing the SQL

                    Dim sScriptFilePath As String = _
                    System.IO.Path.Combine(txtFolderPath.Text, sTable & "_Shrink.sql")

                    If IO.File.Exists(sScriptFilePath) Then
                        IO.File.Delete(sScriptFilePath)
                    End If

                    oSqlScriptWriter = New System.IO.StreamWriter(sScriptFilePath)
                Else
                    oSqlScriptWriter = Nothing
                End If

                Try
                    Shrink(cn, sTable)
                    Dim oDuration As TimeSpan = DateTime.Now.Subtract(dStart)
                    Log("Shrunk table " & sTable & " in " & _
                                        oDuration.Seconds & " seconds")
                Catch ex As Exception
                    Log("Could not shrink table " & sTable & ". " & ex.Message)
                End Try

                If oSqlScriptWriter IsNot Nothing Then
                    'Cleanup log file
                    oSqlScriptWriter.Close()
                    oSqlScriptWriter = Nothing
                End If

            End If
        Next

        cn.Close()
    End Sub

    Private Sub OpenConnections(ByRef cn As OleDbConnection)
        If cn.State <> ConnectionState.Open Then
            cn.Open()
        End If
    End Sub

    Private Sub CopyTable(ByVal sTableName As String, ByRef cn As OleDbConnection)

        Dim dStart As DateTime = DateTime.Now
        Dim bDestTableExists As Boolean = False
        Dim iDestRecCount As Integer = 0

        If chkScriptToFile.Checked Then
            'ExecuteCommand will log to file instead of executing the SQL

            Dim sScriptFilePath As String = _
                System.IO.Path.Combine(txtFolderPath.Text, sTableName & ".sql")

            If IO.File.Exists(sScriptFilePath) Then
                IO.File.Delete(sScriptFilePath)
            End If

            oSqlScriptWriter = New System.IO.StreamWriter(sScriptFilePath)
        Else
            oSqlScriptWriter = Nothing
        End If

        Try
            Dim cm As New OleDbCommand("SELECT Count(*) _
                      FROM " & PadColumnName(sTableName), cn)
            iDestRecCount = Integer.Parse(cm.ExecuteScalar().ToString())
            bDestTableExists = True
        Catch ex As Exception
            'Ignore - assume table dos not exist
        End Try

        Dim bDropTable As Boolean = chkCreateTable.Checked _
                       AndAlso chkDropTable.Checked AndAlso bDestTableExists

        If chkDeleteData.Checked AndAlso iDestRecCount > 0 _
                                 AndAlso bDropTable = False Then
            Log("Deleteting data from table: " & sTableName)

            OpenConnections(cn)
            Dim sSql As String = "DELETE FROM " & PadColumnName(sTableName)

            Try
                ExecuteCommand(cn, sSql)
            Catch ex As Exception
                Log(ex.Message & vbTab & "SQL: " & sSql)
            End Try
        End If

        Dim sFolderPath As String = GetFolderPath()
        Dim sFilePath As String = IO.Path.Combine(sFolderPath, sTableName & ".csv")
        Dim iLineCount As Integer = GetRecCount(sFolderPath, sTableName)
        If iLineCount = 0 Then
            'Nothing to copy - Exit
            Exit Sub
        End If

        If bDropTable Then
            Log("Drop table: " & sTableName)

            Dim sSql As String = "DROP TABLE " & PadColumnName(sTableName)

            Try
                ExecuteCommand(cn, sSql)
                bDestTableExists = False
            Catch ex As Exception
                Log("Could not drop table: " & sTableName & ", " & ex.Message & vbTab)
            End Try
        End If

        Log("Copying " & iLineCount & " rows from table: " & sTableName)

        InsertFromFile(sFilePath, sTableName, cn, iLineCount,
                           chkCreateTable.Checked And bDestTableExists = False)

        Log("Copied table " & sTableName & vbTab & " in " & GetDuration(dStart))

        If oSqlScriptWriter IsNot Nothing Then
            'Cleanup log file
            oSqlScriptWriter.Close()
            oSqlScriptWriter = Nothing
        End If

    End Sub

    Private Sub InsertFromFile(ByVal sFilePath As String,
                               ByVal sTableName As String,
                               ByRef cn As OleDbConnection,
                               ByVal iLineCount As Integer,
                               ByVal bCreateTable As Boolean)

        ProgressBar1.Maximum = iLineCount
        lbCount.Visible = True
        btnStop.Visible = True

        Dim sCreateColumns As String = ""

        If chkRec_id.Checked AndAlso chkBulkInsert.Checked = False Then
            sCreateColumns += "rec_id int not null primary key clustered identity(1,1)"
        End If

        Dim sSqlHeader As String = ""
        Dim oColumns As New Hashtable
        Dim iRow As Long = 0
        Dim oTextFieldParser As _
            New Microsoft.VisualBasic.FileIO.TextFieldParser(sFilePath)
        oTextFieldParser.TextFieldType = FileIO.FieldType.Delimited
        oTextFieldParser.SetDelimiters(GetCsvDelimeter())

        While Not oTextFieldParser.EndOfData
            iRow += 1

            Dim sValues As String = ""

            Dim oFields As String() = oTextFieldParser.ReadFields()
            For iCol As Integer = 0 To oFields.Length - 1
                Dim sCol As String = Trim(oFields(iCol) & "")

                If iRow = 1 Then
                    oColumns(iCol) = sCol
                    If sSqlHeader <> "" Then sSqlHeader += ", "
                    sSqlHeader += PadColumnName(sCol)

                    If sCreateColumns <> "" Then sCreateColumns += ", "
                    sCreateColumns += PadColumnName(sCol) & " " & _
                                                    GetNvarChar() & "(max) NULL"
                Else
                    If iRow = 2 AndAlso iCol = 0 Then

                        If bCreateTable Then
                            Dim sSql As String = "create table " & _
                                PadColumnName(sTableName) & " (" & sCreateColumns & ")"
                            ExecuteCommand(cn, sSql)
                        End If

                        If chkBulkInsert.Checked Then
                            Exit While
                        End If
                    End If

                    If oColumns.ContainsKey(iCol) Then
                        'Dim sColumn As String = oColumns(iCol).ToString()
                        If sValues <> "" Then sValues += ", "
                        If Trim(sCol) = "" Then
                            sValues += "null"
                        Else
                            If chkNvarChar.Checked Then
                                sValues += "N"
                            End If

                            sValues += "'" & (sCol & "").Replace("'", "''") & "'"
                        End If

                    End If
                End If
            Next

            If iRow > 1 Then
                Dim sSql As String = _
                    "insert into [" & sTableName & "] (" & sSqlHeader & ")" &
                    " values (" & sValues & ")"
                ExecuteCommand(cn, sSql)

                If bStop Then
                    Exit While
                End If
            End If

            ProgressBar1.Value = CInt(iRow)
            lbCount.Text = iRow.ToString()
            lbCount.Refresh()
            Windows.Forms.Application.DoEvents()
        End While

        If chkBulkInsert.Checked Then
            Dim sSql As String = "BULK INSERT [" & sTableName & "]" & vbCrLf &
                " FROM '" & sFilePath & "'" & vbCrLf &
                " WITH (FIRSTROW = 2, FIELDTERMINATOR = '" & _
                        GetCsvDelimeter2() & "', ROWTERMINATOR = '\n')"
            ExecuteCommand(cn, sSql, 60 * 60) ' one hour
        End If

        oTextFieldParser.Close()

        ProgressBar1.Value = 0
        lbCount.Visible = False
        lbCount.Text = ""
        btnStop.Visible = False
    End Sub

    Function GetNvarChar() As String

        If chkNvarChar.Checked Then
            Return "nvarchar"
        Else
            Return "varchar"
        End If

    End Function

    Private Function CreateDataTable(ByRef oTable As System.Data.DataTable,
                                     ByVal sTableName As String,
                                     ByRef cn As OleDbConnection) As String

        If oTable Is Nothing Then
            Return ""
        End If

        Dim sCreateColumns As String = ""

        If chkRec_id.Checked Then
            sCreateColumns += "rec_id int not null primary key clustered identity(1,1)"
        End If

        For i As Integer = 0 To oTable.Columns.Count - 1
            Dim sCol As String = oTable.Columns(i).ColumnName
            If sCreateColumns <> "" Then sCreateColumns += ", "
            sCreateColumns += PadColumnName(sCol) & " " & GetNvarChar() & "(max) NULL"
        Next

        Dim sSql As String = "create table " & _
            PadColumnName(sTableName) & " (" & sCreateColumns & ")"
        ExecuteCommand(cn, sSql)

        Return ""
    End Function
    Private Function GetDuration(ByVal dStart As DateTime) As String
        Dim oDuration As TimeSpan = DateTime.Now.Subtract(dStart)
        Return (New DateTime(oDuration.Ticks)).ToString_
        ("HH 'hrs' mm 'mins' ss 'secs'").Replace("00 hrs", "").Replace("00 mins", "").Trim()
    End Function

    Private Function ContainsClosingDoubleQuote(ByVal oLine As String()) As Boolean
        For i As Integer = 0 To oLine.Length - 1
            Dim sVal As String = Trim(oLine(i) & "")
            If Microsoft.VisualBasic.Left(sVal, 1) <> """" _
               AndAlso Microsoft.VisualBasic.Right(sVal, 1) = """" Then
                Return True
            End If
        Next
        Return False
    End Function

    Private Sub Log(s As String)

        If txtLog.Text = "" Then
            txtLog.Text = s
        Else
            txtLog.AppendText(vbCrLf & s)
        End If

        txtLog.Visible = True
        txtLog.ScrollToCaret()
        txtLog.Refresh()
    End Sub

    Private Function GetTempFolderPath() As String
        Dim sFolder As String = Application.StartupPath()
        Dim sXmlFolder As String = System.IO.Path.Combine(sFolder, "CsvCopyXml")

        If Not System.IO.Directory.Exists(sXmlFolder) Then
            System.IO.Directory.CreateDirectory(sXmlFolder)
        End If

        Return sXmlFolder
    End Function

    Private Sub btnConnect2_Click(sender As Object, e As EventArgs) _
                                  Handles btnConnect2.Click
        Dim sConnectionString As String = txtConnectTo.Text

        If sConnectionString = "" Then
            sConnectionString = "Provider=SQLOLEDB.1"
        End If

        sConnectionString = EditConnectionString(sConnectionString)
        If sConnectionString = "" Then
            Exit Sub
        End If

        txtConnectTo.Text = sConnectionString

        SetTableGrid(True)
    End Sub

    Private Sub btnCheckAll_Click(sender As Object, e As EventArgs) _
                                  Handles btnCheckAll.Click
        For iRow = 0 To dgTables.RowCount - 1
            dgTables.Rows(iRow).Cells("Checked").Value = True
        Next
    End Sub

    Private Sub btnUncheckAll_Click(sender As Object, e As EventArgs) _
                                    Handles btnUncheckAll.Click
        For iRow = dgTables.RowCount - 1 To 0 Step -1
            dgTables.Rows(iRow).Cells("Checked").Value = False
        Next
    End Sub

    Private Sub btnCheckNew_Click(sender As Object, e As EventArgs) _
                                  Handles btnCheckNew.Click
        CheckCompare("DestRowCount", "RowCount")
    End Sub

    Private Sub dgTables_Sorted(sender As Object, e As EventArgs) Handles dgTables.Sorted
        SetupBackground()
    End Sub

    Private Sub txtFolderPath_KeyUp(sender As Object, e As KeyEventArgs) _
                                    Handles txtFolderPath.KeyUp
        If e.KeyCode = Keys.Enter Then
            SetTableGrid(True)
        End If
    End Sub

    Private Sub txtConnectTo_KeyUp(sender As Object, e As KeyEventArgs) _
                                   Handles txtConnectTo.KeyUp
        If e.KeyCode = Keys.Enter Then
            SetTableGrid(True)
        End If
    End Sub

    Private Sub chkCreateTable_CheckedChanged(sender As Object, e As EventArgs) _
                                      Handles chkCreateTable.CheckedChanged
        chkCreateTable_CheckedChanged()
    End Sub

    Private Sub chkCreateTable_CheckedChanged()
        chkDropTable.Visible = chkCreateTable.Checked
        chkRec_id.Visible = chkCreateTable.Checked
        chkNvarChar.Visible = chkCreateTable.Checked
    End Sub

    Private Sub btnCancel_Click_
    (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnStop_LinkClicked(sender As Object, _
            e As LinkLabelLinkClickedEventArgs) Handles btnStop.LinkClicked
        bStop = True
    End Sub

    Private Sub chkCopyFiles_CheckedChanged(sender As Object, e As EventArgs)
        SetTableGrid(True)
    End Sub

    Private Sub chkHideSelected_CheckedChanged(sender As Object, e As EventArgs) _
                                Handles chkHideNotSelected.CheckedChanged
        SetTableGrid(True)
    End Sub

    Private Sub btnDeleteFolderCache_Click(sender As Object, e As EventArgs) _
                Handles btnDeleteFolderCache.Click
        Dim sTempFilePath As String = GetTempFileName(txtFolderPath.Text, "CsvCopyXml")

        If IO.File.Exists(sTempFilePath) = False Then
            Exit Sub
        End If

        If MsgBox("Delete cache? Did you add a file to the folder within two hours? _
        The cache file is used for two hours if it takes 5 seconds or more _
        to get the list of tables.", vbYesNo) = vbNo Then
            Exit Sub
        End If

        IO.File.Delete(sTempFilePath)
        Windows.Forms.Application.DoEvents()
        SetTableGrid(False)

        txtFolderPath.Width = txtConnectTo.Width
        btnDeleteFolderCache.Visible = False
    End Sub

    Friend Sub Shrink(ByRef cn As Data.OleDb.OleDbConnection, ByVal sTable As String)
        Dim oTable As DataTable = GetColumnsDataTable(cn, sTable)

        If oTable.Rows.Count = 0 Then
            Exit Sub
        End If

        For iRow As Integer = 0 To oTable.Rows.Count - 1
            Dim oRow As DataRow = oTable.Rows(iRow)
            Dim sName As String = oRow("Name") & ""
            Dim sDataType As String = oRow("DataType") & ""
            Dim sColumnSize As String = oRow("ColumnSize") & ""
            Dim sNumeric As String = oRow("Numeric") & ""
            Dim sPeriod As String = oRow("Period") & ""
            Dim sNull As String = oRow("Null") & ""
            Dim sStartsWithZero As String = oRow("StartsWithZero") & ""
            Dim sDate As String = oRow("Date") & ""

            Dim sLength As String = oRow("Length") & ""
            Dim iLength As Integer = 0
            If sLength <> "" Then
                iLength = CInt(sLength)
            End If

            Dim sNewDataType As String = ""

            If sDate = "Y" Then
                If iLength > 10 Then
                    sNewDataType = "datetime"
                Else
                    sNewDataType = "date"
                End If

            ElseIf sNumeric = "Y" _
            AndAlso ((sPeriod <> "Y" AndAlso sStartsWithZero = "Y") = False) Then

                If sPeriod = "Y" Then
                    sNewDataType = "decimal(10,2)"
                Else
                    If iLength > 9 Then
                        sNewDataType = "bigint"
                    Else
                        sNewDataType = "int"
                    End If

                End If

            ElseIf sLength <> "" AndAlso sColumnSize <> sLength Then
                sNewDataType = sDataType & "(" & RoundUp(sLength) & ")"
            End If

            If sNewDataType <> "" Then
                Dim sSql As String = "alter table " & _
                PadTableName(sTable) & " alter column [" & sName & "] " & sNewDataType
                Dim sError As String = ExecuteCommand(cn, sSql)
                If sError <> "" Then
                    If sLength <> "" AndAlso sColumnSize <> sLength Then
                        sNewDataType = sDataType & "(" & RoundUp(sLength) & ")"
                        sSql = "alter table " & PadTableName(sTable) & _
                               " alter column [" & sName & "] " & sNewDataType
                        sError = ExecuteCommand(cn, sSql)
                    End If
                End If

                If sError <> "" Then
                    Log(sSql & vbCrLf & vbTab & sError)
                End If
            End If
        Next

    End Sub

    Function GetTable(ByRef cn As Data.OleDb.OleDbConnection, ByVal sSql As String) _
                      As System.Data.DataTable
        Dim ds As DataSet = New DataSet
        Dim ad As New OleDbDataAdapter(sSql, cn)
        ad.Fill(ds)
        Return ds.Tables(0)
    End Function

    Public Function PadColumnName(ByVal s As String) As String
        Return "[" & s & "]"
    End Function

    Function PadTableName(ByVal s As String) As String
        Return "[" & s & "]"
    End Function

    Function PadQuotes(ByVal s As String) As String
        If s = "" Then
            Return ""
        End If
        Return (s & "").Replace("'", "''")
    End Function

    Friend Function GetColumnsDataTable(ByRef cn As Data.OleDb.OleDbConnection, _
                                        ByVal sTableName As String) As DataTable
        Dim oDataTable As New DataTable

        oDataTable.Columns.Add(New DataColumn_
                   ("Name", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("DataType", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("ColumnSize", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("Length", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("Numeric", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("Date", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("Period", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("Null", System.Type.[GetType]("System.String")))
        oDataTable.Columns.Add(New DataColumn_
                   ("StartsWithZero", System.Type.[GetType]("System.String")))

        If Trim(sTableName) = "" Then
            Return oDataTable
        End If

        Dim sSql As String = ""

        If sTableName.IndexOf(".") <> -1 Then
            sSql = "select * from INFORMATION_SCHEMA.COLUMNS _
            where TABLE_SCHEMA + '.' + TABLE_NAME = '" & PadQuotes(sTableName) & "'"
        Else
            sSql = "select * from INFORMATION_SCHEMA.COLUMNS _
            where TABLE_NAME = '" & PadQuotes(sTableName) & "' order by ordinal_position"
        End If

        Dim oComputedColumns As Hashtable = GetComputedColumns(cn, sTableName)
        Dim oTable As DataTable = GetTable(cn, sSql)

        Dim oColumnNulls As Hashtable = _
        GetColumnFunc(cn, sTableName, oTable, "NULL", CombineHash(oComputedColumns))
        Dim oExcludeCols As Hashtable = _
        CombineHash(oComputedColumns, oColumnNulls) 'exlude Computed and Null Columns

        Dim oColumnLength As Hashtable = _
            GetColumnLength(cn, sTableName, oTable, oExcludeCols)
        Dim oColumnNumeric As Hashtable = _
            GetColumnFunc(cn, sTableName, oTable, "ISNUMERIC", oExcludeCols)
        Dim oColumnDate As Hashtable = _
            GetColumnFunc(cn, sTableName, oTable, "ISDATE", _
            CombineHash(oExcludeCols, oColumnNumeric))
        Dim oColumnPeriod As Hashtable = _
            GetColumnFunc(cn, sTableName, oTable, "Period", Nothing, oColumnNumeric)
        Dim oColumnStartsWith0 As Hashtable = _
            GetColumnFunc(cn, sTableName, oTable, "StartsWith0", Nothing, oColumnNumeric)

        For iRow As Integer = 0 To oTable.Rows.Count - 1
            Dim sColumn As String = oTable.Rows(iRow)("COLUMN_NAME")

            If oComputedColumns.ContainsKey(sColumn) = False Then
                Dim sDataType As String = oTable.Rows(iRow)("DATA_TYPE") & ""
                'Dim bAllowDBNull As Boolean = _
                     oTable.Rows(iRow)("IS_NULLABLE") & "" = "YES"
                Dim sColumnSize As String = _
                    oTable.Rows(iRow)("CHARACTER_MAXIMUM_LENGTH") & ""

                If sDataType = "decimal" OrElse sDataType = "numeric" Then
                    Dim sPrecision As String = oTable.Rows(iRow)("NUMERIC_PRECISION") & ""
                    Dim sScale As String = oTable.Rows(iRow)("NUMERIC_SCALE") & ""
                    sDataType += " (" & sPrecision & ", " & sScale & ")"

                ElseIf sDataType = "text" OrElse sDataType = "image" Then
                    sColumnSize = ""

                ElseIf sColumnSize = "-1" Then
                    sColumnSize = "max"
                End If

                Dim sCol As String = sColumn.ToLower()

                Dim oDataRow As DataRow = oDataTable.NewRow()

                oDataRow("Name") = sColumn
                oDataRow("DataType") = sDataType
                oDataRow("ColumnSize") = sColumnSize
                oDataRow("Length") = oColumnLength(sCol)
                oDataRow("Numeric") = oColumnNumeric(sCol)
                oDataRow("Date") = oColumnDate(sCol)
                oDataRow("Period") = oColumnPeriod(sCol)
                oDataRow("Null") = oColumnNulls(sCol)
                oDataRow("StartsWithZero") = oColumnStartsWith0(sCol)

                oDataTable.Rows.Add(oDataRow)
            End If
        Next

        Return oDataTable
    End Function

    Private Function CombineHash(ByVal a As Hashtable, ByVal b As Hashtable) As Hashtable

        For Each o As DictionaryEntry In b
            Dim sKey As String = o.Key
            a(sKey.ToLower()) = o.Value
        Next

        Return a
    End Function

    Private Function CombineHash(ByVal a As Hashtable) As Hashtable

        Dim oRet As New Hashtable

        For Each o As DictionaryEntry In a
            Dim sKey As String = o.Key
            oRet(sKey.ToLower()) = o.Value
        Next

        Return oRet
    End Function

    Private Function GetColumnLength(ByRef cn As Data.OleDb.OleDbConnection,
                                     ByVal sTableName As String,
                                     ByRef oTable As DataTable,
                                     ByRef oExcludeColumns As Hashtable) As Hashtable

        Dim sColumns As String = ""

        For iRow As Integer = 0 To oTable.Rows.Count - 1
            Dim sColumn As String = oTable.Rows(iRow)("COLUMN_NAME")
            If oExcludeColumns.ContainsKey(sColumn.ToLower()) = False Then
                Dim sDataType As String = LCase(oTable.Rows(iRow)("DATA_TYPE") & "")
                If sDataType = "nvarchar" OrElse sDataType = "varchar" _
                                          OrElse sDataType = "char" Then
                    If sColumns <> "" Then sColumns += ", "
                    sColumns += "max(len([" & sColumn & "])) as [" & sColumn & "]"
                End If
            End If
        Next

        Dim oColumns As New Hashtable

        If sColumns = "" Then
            Return oColumns
        End If

        Dim sColSql As String = "select " & sColumns & " from " & PadTableName(sTableName)
        Dim tdColumns As DataTable = GetTable(cn, sColSql)
        If tdColumns.Rows.Count > 0 Then
            For iCol As Integer = 0 To tdColumns.Columns.Count - 1
                Dim sCol As String = tdColumns.Columns(iCol).ColumnName.ToLower()
                Dim sVal As String = tdColumns.Rows(0)(iCol) & ""
                oColumns(sCol) = sVal
            Next
        End If

        Return oColumns
    End Function

    Private Function GetColumnFunc(ByRef cn As Data.OleDb.OleDbConnection,
                                   ByVal sTableName As String,
                                   ByRef oTable As DataTable,
                                   ByVal sFunc As String,
                                   ByVal oExcludeColumns As Hashtable,
                                   Optional ByVal oIncludeColumns _
                                            As Hashtable = Nothing) As Hashtable

        If oIncludeColumns IsNot Nothing AndAlso oIncludeColumns.Count = 0 Then
            Return New Hashtable
        End If

        Dim sColumns As String = ""

        For iRow As Integer = 0 To oTable.Rows.Count - 1
            Dim sColumn As String = oTable.Rows(iRow)("COLUMN_NAME") & ""

            If (oExcludeColumns IsNot Nothing _
                AndAlso oExcludeColumns.ContainsKey(sColumn.ToLower()) = False) OrElse
               (oIncludeColumns IsNot Nothing _
                AndAlso oIncludeColumns.ContainsKey(sColumn.ToLower()) = True) Then

                Dim sDataType As String = LCase(oTable.Rows(iRow)("DATA_TYPE") & "")
                If sDataType = "nvarchar" _
                   OrElse sDataType = "varchar" OrElse sDataType = "char" Then
                    If sColumns <> "" Then sColumns += ", "

                    If sFunc = "Period" Then
                        'check if any record has period
                        sColumns += "max(case when CHARINDEX('.', [" & sColumn & "]) <> 0 _
                                     then 1 else 0 end) as [" & sColumn & "]"

                    ElseIf sFunc = "StartsWith0" Then
                        'at least one record starts with zero
                        sColumns += "max(case when SUBSTRING_
                                    (isnull([" & sColumn & "],''), 1, 1) = '0' _
                                    then 1 else 0 end) as [" & sColumn & "]"

                    ElseIf sFunc = "NULL" Then
                        'all rows are null
                        sColumns += "min(case when [" & sColumn & "] IS NULL _
                                     then 1 else 0 end) as [" & sColumn & "]"

                    Else
                        'make sure all rows are nulls or numeric
                        sColumns += "min(case when [" & sColumn & "] IS NULL OR " _
                                     & sFunc & "([" & sColumn & "]) = 1 _
                                     then 1 else 0 end) as [" & sColumn & "]"
                    End If

                End If
            End If
        Next

        Dim oColumns As New Hashtable

        If sColumns = "" Then
            Return oColumns
        End If

        Dim sColSql As String = "select " & sColumns & " from " & PadTableName(sTableName)

        'sMsg += "<p>" & sColSql & "</p>" & vbCrLf

        Dim tdColumns As DataTable = GetTable(cn, sColSql)
        If tdColumns.Rows.Count > 0 Then
            For iCol As Integer = 0 To tdColumns.Columns.Count - 1
                Dim sCol As String = tdColumns.Columns(iCol).ColumnName.ToLower()
                Dim sVal As String = tdColumns.Rows(0)(iCol) & ""
                If sVal = "1" Then
                    oColumns(sCol) = "Y"
                End If
            Next
        End If

        Return oColumns
    End Function

    Protected Function GetComputedColumns(ByRef cn As Data.OleDb.OleDbConnection,
                                          ByVal sTableName As String,
                                          Optional ByVal sColumnName As String = "") _
                                          As Hashtable
        Dim oRet As New Hashtable
        Dim sSql As String = "SELECT name, definition FROM sys.computed_columns "

        If sTableName.IndexOf(".") = -1 Then
            sSql += " WHERE OBJECT_NAME(object_id) = '" & PadQuotes(sTableName) & "'"
        Else
            sSql += " WHERE OBJECT_SCHEMA_NAME(object_id) + '.' + _
                      OBJECT_NAME(object_id) = '" & PadQuotes(sTableName) & "'"
        End If

        If sColumnName <> "" Then
            sSql += " AND name = '" & PadQuotes(sColumnName) & "'"
        End If

        Try
            Dim oTable As DataTable = GetTable(cn, sSql)
            For iRow As Integer = 0 To oTable.Rows.Count - 1
                Dim sColumn As String = oTable.Rows(iRow)("name") & ""
                Dim sDef As String = oTable.Rows(iRow)("definition") & ""
                oRet(sColumn) = sDef
            Next

        Catch ex As Exception
            'Ignore becase of earlier versions of SQL Server 
            'that did not support computed columns
        End Try

        Return oRet
    End Function

    Private Function ExecuteCommand(ByRef cn As Data.OleDb.OleDbConnection, _
                                    ByVal sSql As String) As String

        If oSqlScriptWriter IsNot Nothing Then
            oSqlScriptWriter.WriteLine(sSql)
        Else
            Dim cm As New OleDbCommand(sSql, cn)
            Try
                cm.ExecuteNonQuery()
            Catch ex As Exception
                Return ex.Message
            End Try
        End If

        Return ""
    End Function

    Private Function ExecuteCommand(ByRef cn As Data.OleDb.OleDbConnection, _
            ByVal sSql As String, iCommandTimeout As Integer) As String

        If oSqlScriptWriter IsNot Nothing Then
            oSqlScriptWriter.WriteLine(sSql)
        Else
            Dim cm As New OleDbCommand(sSql, cn)
            cm.CommandTimeout = iCommandTimeout
            Try
                cm.ExecuteNonQuery()
            Catch ex As Exception
                Return ex.Message
            End Try
        End If

        Return ""
    End Function

    Private Function RoundUp(ByVal s As String) As String
        Dim i As Integer = RoundUp2(s)

        If chkNvarChar.Checked Then
            If i > 4000 Then
                Return "max"
            End If
        Else
            If i > 8000 Then
                Return "max"
            End If
        End If

        Return i.ToString()
    End Function

    Private Function RoundUp2(ByVal s As String) As Integer
        Dim num As Integer = CInt(s)

        If num = 0 Then
            'Nulls or dats in columns
            Return 100
        End If

        If (num < 10) Then Return Math.Ceiling(num / 10) * 10
        If (num < 100) Then Return Math.Ceiling(num / 100) * 100
        If (num < 1000) Then Return Math.Ceiling(num / 1000) * 1000

        Return num * 2
    End Function

End Class
Other

The application is using OLEDB to connect to the database. So you might need to install the latest OLE DB driver for SQL Server.
History

11^th July, 2023: Initial version
12^th July, 2023: Using TextFieldParser
13^th July, 2023: Script to File
14^th July, 2023: Bulk Insert
License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Written By
Igor Krupitsky
Web Developer
United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.