Click here to Skip to main content
15,887,450 members
Search within:


Visual Studio 2008 - Export Edited CSV
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
Hi,

I wondered whether you could help?

I am trying to create an application which will allow you to load in a csv/text file and then it would allow the user to create sql statements.

Eg.

The file would look like :

1 ‘Fred’ ‘Biggs’
2 ‘John’ ’Taylor’
....

You would also supply information like the table name, table name : DT_People

When you process the application, you would then click the button and it would produce :

SQL
INSERT INTO DT_PEOPLE VALUES(1,’Fred’,’Biggs’);
INSERT INTO DT_PEOPLE VALUES (2,’John’,’Taylor’);


I have managed to load the csv into a list view box and also export it from the list view box, it just just the text around which I’m having difficulty with.

Please could you help?

If you require the code so far, please email me and I will send it over.

Thanks in advance,


P.S.... So far ...


VB
Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click


    Dim saveto As String
    Dim table_name As String
    Dim created As String
    Dim created_by As String

    saveto = txtSaveTo.Text
    table_name = txtTable_Name.Text
    created = txtCreated.Text
    created_by = txtCreated_By.Text

    Using csv As New System.IO.StreamWriter((saveto), True)
        For Each oItem As ListViewItem In ListView.Items
            csv.WriteLine(String.Format("""{0}"",""{1}"",""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""{7}"",""{8}""", "INSERT INTO ", table_name, "VALUES (", oItem.Text, (created), (created_by), oItem.SubItems(0).Text, oItem.SubItems(1).Text, oItem.SubItems(2).Text))
        Next
    End Using

End Sub
Posted
Updated 15-Mar-13 2:53am
v2
Add a Solution
Comments
ZurdoDev 15-Mar-13 8:53am    
Where are you stuck?
[no name] 15-Mar-13 9:04am    
"it just just the text around which I’m having difficulty with" means what exactly?

1 solution

I think this is what you are looking for.

C#
' The following two statements
' remove surrounding apostrophes in the two name fields. Later,
' via String.Format, we will put them back after we have replaced
' any embedded apostrophes with double apostrophes. This handles
' names that contain embedded apostrophes. If you already did this
' when the input file was created, then you do not need the two 
' .Replace("'","''")
' methods in the WriteLine statement below.
Dim strTemp1 As String = oItem.SubItems(1).Text.Substring(1, oItem.SubItems(1).Text.Length - 2) 
Dim strTemp2 As String = oItem.SubItems(2).Text.Substring(1, oItem.SubItems(2).Text.Length - 2)
csv.WriteLine(String.Format("{0} {1} {2}'{3}','{4}','{5}',{6},'{7}','{8}'{9}", _
    "INSERT INTO", table_name, "VALUES(", _
    oItem.Text, created, created_by, _
    oItem.SubItems(0).Text, _
    strTemp1.Replace("'", "''"), _
    strTemp2.Replace("'", "''"), _
    ");"))


Note:
The following in the above code handles the case where there is an embedded apostrophe in a name (example: O'Toole):
strTemp1.Replace("'", "''"), _
strTemp2.Replace("'", "''"), _


Tested: Visual Basic .NET 2012

__________________________________________________________________________________________________
The following was added to the Solutiuon after the original poster asked about making the program to generate the INSERT statements dynamic so that it could generate INSERT statements for different tables with different columns.
____________________________________________________


The following code is from a program that I wrote more than 10 years ago. It is not the complete program but is enough to show you how to get the tables names of a selected database and then the columns for a selected table. Using this information, you should be able to discern how to create a program to dynamically generate the INSERT statements that you require.

Dim cn As New SqlConnection
Dim bDatabaseOpen As Boolean = False
Const SELECTCOLUMNS As String = "SELECT column_name, is_nullable,data_type,character_maximum_length, numeric_precision,numeric_scale From INFORMATION_SCHEMA.Columns Where Table_Name = '!TABLENAME' And  Table_Catalog='!DATABASENAME' Order By Ordinal_Position;"
Const SELECTTABLES As String = "SELECT table_name From INFORMATION_SCHEMA.Tables Where Table_Catalog='!DATABASENAME' and NOT (table_name like 'dt%' or table_name like 'sys%') Order By Table_Name;"
Const SELECTDATABASES As String = "SELECT DISTINCT name From sys.sysdatabases  Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb' and name<>'ReportServer$SQLExpress' and name<>'ReportServer$SQLExpressTempDB' and name<>'Christmas' Order by name;"

Private Sub Form_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.Height = 344
    If Me.DesignMode Then Exit Sub
    Call OpenDatabase("MSDB")
    Call LoadComboBox(SELECTDATABASES, cmbDatabases, "name")
    Call CloseDatabase()
End Sub
Private Sub OpenDatabase(ByVal strDatabase As String)
    Dim CONNECTIONSTRING As String = "Database=" & strDatabase & ";Server=sage\sqlexpress;Connect Timeout=4;Integrated Security=True;Net=dbmssocn;Application Name=SQLGenerator;"
    If (Not bDatabaseOpen) Then
        Try
            cn.ConnectionString = CONNECTIONSTRING
            cn.Open()
            bDatabaseOpen = True
        Catch myException As SqlException
            Call ShowODBCException(myException)
        End Try
    End If
End Sub
PRivate Sub CloseDatabase()
    Try
        cn.Close()
    Catch
    End Try
    Try
        cn.Dispose()
    Catch
    End Try
    bDatabaseOpen = False
End Sub
Private Sub cmbDatabases_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbDatabases.SelectedValueChanged
    cmbDatabases.Enabled = False
    If CType(cmbDatabases.SelectedItem, String).Length > 0 Then
        Call OpenDatabase(CType(cmbDatabases.SelectedItem, String))
        Call LoadComboBox(Replace(SELECTTABLES, "!DATABASENAME", CType(cmbDatabases.SelectedItem, String)), cmbTables, "table_name")
        If cmbTables.Items.Count > 0 Then
            cmbTables.Focus()
            cmbTables.SelectedIndex = 0
        End If
    End If
End Sub
Private Sub LoadComboBox(ByVal strSQL As String, ByRef cmb As ComboBox, ByVal strKeyName As String)
    Dim obcommand As SqlCommand = Nothing
    Dim rs As SqlDataReader = Nothing
    Dim bOK As Boolean = False
    Try
        obcommand = New SqlCommand(strSQL, cn)
        rs = obcommand.ExecuteReader
        bOK = True
    Catch myException As SqlException
        Call ShowODBCException(myException)
    End Try
    If bOK Then
        While rs.Read()
            cmb.Items.Add(DirectCast(rs(strKeyName), String))
        End While
    End If
    Try
        rs.Close()
    Catch
    End Try
    obcommand.Dispose()
End Sub

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Call CreateInsert(CType(cmbDatabases.SelectedItem, String), CType(cmbTables.SelectedItem, String))
End Sub

Sub CreateInsert(ByVal strDatabase As String, ByVal strTable As String)
    Dim obcommand As SqlCommand = Nothing
    Dim rs As SqlDataReader = Nothing
    Dim bOK As Boolean = False
    Dim intTotal As Integer
    Dim intDecimal As Integer
    Dim strColumnName As String
    Dim strType As String
    Dim bIsNullable As Boolean
    Dim strPrepareData As String = ""
    Dim StrPrepareData2 As String = ""
    Dim strInsert As String = "strSQL=String.Concat(" & Chr(34) & "Insert into " & strTable & "("
    Dim strSQL As String = Replace(Replace(SELECTCOLUMNS, "!DATABASENAME", strDatabase), "!TABLENAME", strTable)
    Try
        obcommand = New SqlCommand(strSQL, cn)
        rs = obcommand.ExecuteReader
        bOK = True
    Catch myException As SqlException
        Call ShowODBCException(myException)
    End Try
    If bOK Then
        While rs.Read()
            strInsert += DirectCast(rs("column_name"), String) & ", "
        End While
        strInsert = strInsert.Substring(0, strInsert.Length - 2) & ") " & Chr(34) & " , _" & vbNewLine & Chr(34) & "Values (" & Chr(34) & ", _" & vbNewLine
        rs.Close()
        obcommand.Dispose()
        bOK = False
        Try
            obcommand = New SqlCommand(strSQL, cn)
            rs = obcommand.ExecuteReader
            bOK = True
        Catch myException As SqlException
            Call ShowODBCException(myException)
        End Try
        If bOK Then
            While rs.Read()
                strColumnName = DirectCast(rs("column_name"), String)
                strType = DirectCast(rs("data_type"), String).ToUpper
                bIsNullable = CBool(DirectCast(rs("is_nullable"), String).ToUpper = "YES")
                Select Case strType
                    Case "INT", "BIGINT", "TINYINT", "SMALLINT"
                        If bIsNullable Then
                            strInsert += "str" & strColumnName
                            strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
                            StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
                                "if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
                                "   str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
                                "Else" & vbNewLine & _
                                "   str" & strColumnName & "=txt" & strColumnName & ".text" & vbNewLine & _
                                "End If" & vbNewLine
                        Else
                            strInsert += Chr(34) & "0" & Chr(34) & " , txt" & strColumnName & ".text"
                        End If
                    Case "NVARCHAR", "VARCHAR", "CHAR", "TEXT"
                        If bIsNullable Then
                            strInsert += "str" & strColumnName
                            strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
                            StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
                                "if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
                                "   str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
                                "Else" & vbNewLine & _
                                "   str" & strColumnName & "=string.concat(" & Chr(34) & "'" & Chr(34) & ", REPLACE(txt" & strColumnName & ".text," & Chr(34) & "'" & Chr(34) & "," & Chr(34) & "''" & Chr(34) & ") , " & Chr(34) & "'" & Chr(34) & ")" & vbNewLine & _
                                "End If" & vbNewLine
                        Else
                            strInsert += Chr(34) & " '" & Chr(34) & ", REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "'" & Chr(34) & "," & Chr(34) & "''" & Chr(34) & ") , " & Chr(34) & "'" & Chr(34)
                        End If
                    Case "DATETIME", "SMALLDATETIME"
                        Dim strFormat As String
                        If strColumnName.ToUpper.IndexOf("TIME") > 0 Then
                            strFormat = "HH:mm:ss"
                        Else
                            strFormat = "yyyy-MM-dd"
                        End If
                        If bIsNullable Then
                            strInsert += "str" & strColumnName
                            strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
                            StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
                                "if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
                                "   str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
                                "Else" & vbNewLine & _
                                "   str" & strColumnName & "=string.concat(" & Chr(34) & "'" & Chr(34) & ", FORMAT$(CDATE(txt" & strColumnName & ".text)," & Chr(34) & strFormat & Chr(34) & ") , " & Chr(34) & "'" & Chr(34) & ")" & vbNewLine & _
                                "End If" & vbNewLine
                        Else
                            strInsert += Chr(34) & "'" & Chr(34) & " , " & "FORMAT$(CDATE(txt" & strColumnName & ".text.trim)," & Chr(34) & strFormat & Chr(34) & ")" & " , " & Chr(34) & "'" & Chr(34)
                        End If
                    Case "DECIMAL", "NUMERIC"
                        intTotal = CInt(rs("numeric_precision"))
                        intDecimal = CInt(rs("numeric_scale"))
                        If bIsNullable Then
                            strInsert += "str" & strColumnName
                            strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
                            StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
                                "if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
                                "   str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
                                "Else" & vbNewLine & _
                                "   str" & strColumnName & "=FORMAT$(CDBL(REPLACE(REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "$" & Chr(34) & "," & Chr(34) & Chr(34) & ")," & Chr(34) & "," & Chr(34) & "," & Chr(34) & Chr(34) & "))," & Chr(34) & "".PadLeft(intTotal, CChar("#")) & DirectCast(IIf(intDecimal > 0, ".", ""), String) & "".PadLeft(intDecimal, CChar("#")) & Chr(34) & ")" & vbNewLine & _
                                "End If" & vbNewLine
                        Else
                            strInsert += Chr(34) & "0" & Chr(34) & " , FORMAT$(CDBL(REPLACE(REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "$" & Chr(34) & "," & Chr(34) & Chr(34) & ")," & Chr(34) & "," & Chr(34) & "," & Chr(34) & Chr(34) & "))," & Chr(34) & "".PadLeft(intTotal, CChar("#")) & DirectCast(IIf(intDecimal > 0, ".", ""), String) & "".PadLeft(intDecimal, CChar("#")) & Chr(34) & ")"
                        End If
                    Case Else
                        Debug.WriteLine("Unknown Data Type: [" & strColumnName & "] " & DirectCast(rs("data_type"), String).ToUpper)
                        strInsert += "[" & strColumnName & "] " & DirectCast(rs("data_type"), String).ToUpper
                End Select
                strInsert += " , " & Chr(34) & "," & Chr(34) & " , _" & vbNewLine
            End While
            strInsert = strInsert.Substring(0, strInsert.Length - 11) & " & " & Chr(34) & ");" & Chr(34) & ")" & vbNewLine
        End If
    End If
    Try
        rs.Close()
    Catch
    End Try
    Try
        obcommand.Dispose()
    Catch
    End Try
    Clipboard.SetDataObject(strPrepareData & vbNewLine & vbNewLine & StrPrepareData2 & vbNewLine & vbNewLine & strInsert, True)
    MsgBox("SQL Insert is on the clipboard", MsgBoxStyle.Information, "SQL")
End Sub
 
Share this answer
 
Posted
Updated 17-Mar-13 11:44am
v8
Comments
Member 9913928 17-Mar-13 12:40pm    
Hi Mike Meinz, thanks a lot, much appreciated!

Do you have any idea about how the program can adapt to different amount of columns? Is there any way to make the formatting flexible?

I thought about something like :

Dim column(columnnumber) As String
Dim columnnumber as integer

column_title = txtColumn_Name.Text

Dim column(columnnumber) as string

column(columnnumber) = txtColumn_Name.Text

columnnumber = columnnumber + 1

I've tried this however the variable seems to get made, then the value assigned but then goes back to 'nothing' when debugging! Any ideas?

Cheers
Mike Meinz 17-Mar-13 12:52pm    
Need to look in database at the designated TABLE to determine Data Type of each column. Then, as you parse each row from the input data from the disk file, use that information with a FOR statement to generate column name portion and value portion of SQL statement that corresponds to that column. After processing all columns, using a FOR statement concatenate all name portions and value portions respectively together to create final SQL INSERT statement.

Please do not enter additional questions in a Solution. It is considered bad form on CodeProject.com
Member 9913928 17-Mar-13 13:12pm    
Hi Mike, Thanks a lot however each table is of a different size (ie different number of columns).

The code above is also used with a list view :

ListView.Columns.Add(column_title)
ListView.View = View.Details
txtColumn_Name.Text = ""
ListView.Refresh()

Because an update statement requires the column header, would it be possible to assign the column title (entered by user) to a variable or to be read from the list view column titles.

UPDATE DT_TABLE
SET COLUMN_A = 'DATA_A', COLUMN_B = 'DATA_B'
WHERE COLUMN_C = DATA_C

Can you think of a way to do this???

Thanks again
Mike Meinz 17-Mar-13 17:46pm    
To Solution 1, I added some sample code that will help you make you application dynamic. Study the code and use what you learn to help you develop your application.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 17 Mar 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900