Visual Studio 2008 - 导出已编辑的CSV [英] Visual Studio 2008 - Export Edited CSV
问题描述
我想知道你是否可以提供帮助？
我正在尝试创建一个应用程序将允许您加载csv /文本文件，然后它将允许用户创建SQL语句。
例如。
文件看起来像：
1'Fred''Biggs'
2 'John''Taylor'
....
您还可以提供表名，表名等信息： DT_People
当你处理申请时，你会点击按钮，它会产生：
INSERT INTO DT_PEOPLE VALUES （ 1 ，'Fred'，'Biggs'）;
INSERT INTO DT_PEOPLE VALUES （ 2 ，'John'，'Taylor'）;
I我设法将csv加载到列表视图框中，并从列表视图框中导出它，它只是我遇到困难的文本。
请帮忙吗？
如果您需要到目前为止的代码，请给我发电子邮件，我会发送给它。
在此先感谢，
PS ...到目前为止...
私人 Sub btnGenerate_Click（ ByVal sender As System。对象， ByVal e As System.Even tArgs）句柄 btnGenerate.Click
Dim saveto 作为 字符串
Dim table_name 作为 字符串
Dim 创建作为 字符串
Dim created_by As String
saveto = txtSaveTo.Text
table_name = txtTable_Name.Text
created = txtCreated.Text
created_by = txtCreated_By.Text
使用 csv 作为 新 System.IO.StreamWriter（（saveto）， True ）
对于 每个 oItem 作为 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 ）。文本，oItem.SubItems（ 1 ）。Text，oItem.SubItems（< span class =code-digit> 2 ）。文本））
下一步
结束 使用
结束 Sub
我认为这就是你要找的。
' 以下两个语句
' 删除周围的撇号在两个名称字段中。稍后，
' 通过String.Format，我们将在替换
后将它们放回去任何带有 double 撇号的嵌入式撇号。这将处理包含嵌入式撇号的
' 名称。如果您已经创建输入文件时已经执行了
' ，那么您执行不需要两个
' .Replace（' ， ' ' ）
'下面的WriteLine语句中的方法。
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（，_ ' ，' ' < span class =code-string>），_
oItem.Text，created，created_by，_
oItem.SubItems（0）.Text，_
strTemp1.Replace（
strTemp2.Replace（' ， ' ' ），_
）; ））
注：
以上代码中的以下内容处理案例在名称中有一个嵌入的撇号（例如：O''Toole）：
strTemp1.Replace（''，'''''），_
strTemp2.Replace（''，'''''），_
经过测试：Visual Basic .NET 2012
__ ________________________________________________________________________________________________
在原始海报询问如何使程序生成动态INSERT语句之后，以下内容被添加到Solutiuon中，以便它可以为具有不同列的不同表生成INSERT语句。
____________________________________________________
以下代码来自我十多年前写的一个程序。它不是完整的程序，但足以向您展示如何获取所选数据库的表名，然后是所选表的列。使用此信息，您应该能够辨别如何创建程序以动态生成所需的INSERT语句。
Dim cn As新的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''和Table_Catalog = ''！DATABASENAME''按Ordinal_Position排序;
Const SELECTTABLES As String =SELECT table_name From INFORMATION_SCHEMA.Tables Where Table_Catalog =''！DATABASENAME''和NOT（table_name like''dt％''或table_name like''sys％''）Order By Table_Name;
Const SELECTDATABASES As String =SELECT DISTINCT name from sys.sysdatabases where name<>''master''和name<>''model''和name<>''msdb''和名称<>''tempdb''和名称<>''ReportServer
SQLExpress''和名称<>''ReportServer
< blockquote> SQLExpressTempDB''和名称<>''圣诞节''按名称排序;
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
调用OpenDatabase（MSDB）
调用LoadComboBox（SELECTDATABASES，cmbDatabases，name）
调用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;
如果（不是bDatabaseOpen）那么
尝试
cn.ConnectionString = CONNECTIONSTRING
cn.Open（）
bDatabaseOpen = True
Catch myException As SqlException
调用ShowODBCException（myException）
结束尝试
结束如果
结束子
PRivate Sub CloseDatabase（）
尝试
cn.Close（）
Catch
结束尝试
尝试
cn.Dispose（）
Catch
结束尝试
bDatabaseOpen = False
结束Sub
Private Sub cmbDatabases_SelectedValueChanged（ByVal sender As Object，ByVal e As System.EventArgs）处理cmbDatabases.SelectedValueChanged
cmbDatabases.Enabled = False
如果CType（cmbDatabases.SelectedItem，String）.Length> 0然后
调用OpenDatabase（CType（cmbDatabases.SelectedItem，String））
调用LoadComboBox（替换（SELECTTABLES，！DATABASENAME，CType（cmbDatabases.SelectedItem，String）），cmbTables，table_name）
如果cmbTables.Items.Count> 0然后
cmbTables.Focus（）
cmbTables.SelectedIndex = 0
结束如果
结束如果
结束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
试试
obcommand = New SqlCommand（strSQL，cn）
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
调用ShowODBCException（myException）
结束尝试
如果是bOK那么
而rs.Read（）
cmb.Items.Add（DirectCast（rs（strKeyName），String））
结束时
结束如果
尝试
rs.Close（）
Catch
结束尝试
obcommand.Dispose（）
结束Sub
Private Sub btnInsert_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理btnInsert.Click
调用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）& 插入& strTable& （
Dim strSQL As String = Replace（替换（SELECTCOLUMNS，！DATABASENAME，strDatabase），！TABLENAME，strTable）
尝试
obcommand =新的SqlCommand（strSQL，cn） ）
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
调用ShowODBCException（myException）
结束尝试
如果bOK则
虽然rs.Read（）
strInsert + = DirectCast（rs（column_name），String）&，
End while
strInsert = strInsert.Substring（0，strInsert。长度 - 2）&）& Chr（34）& ，_& vbNewLine& Chr（34）& 值（& Chr（34）&，_& vbNewLine
rs.Close（）
obcommand.Dispose（）
bOK = False
试试
obcommand = New SqlCommand（strSQL，cn）
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
Call ShowODBCException（myException）
End尝试
如果是bOK那么
而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
CaseINT，BIGINT， TINYINT，SMALLINT
如果bIsNullable那么
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然后& vbNewLine& _
str& strColumnName& =& Chr（34）& 空虚& Chr（34）& vbNewLine& _
Else& vbNewLine& _
str& strColumnName& = txt& strColumnName& .text& vbNewLine& _
结束如果& vbNewLine
否则
strInsert + = Chr（34）& 0& Chr（34）& ，txt& strColumnName& .text
结束如果
案例NVARCHAR，VARCHAR，CHAR，TEXT
如果bIsNullable那么
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然后& vbNewLine& _
str& strColumnName& =& Chr（34）& 空虚& 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& _
结束如果& vbNewLine
否则
strInsert + = Chr（34）& ''& Chr（34）& ，REPLACE（txt& strColumnName&。text.trim，& Chr（34）&"& Chr（34）&，& Chr（34）& '''''& Chr（34）&），& Chr（34）& ''& Chr（34）
结束如果
CaseDATETIME，SMALLDATETIME
Dim strFormat As String
如果strColumnName.ToUpper.IndexOf（TIME）> 0然后
strFormat =HH：mm：ss
否则
strFormat =yyyy-MM-dd
结束如果
如果bIsNullable那么
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然后& vbNewLine& _
str& strColumnName& =& Chr（34）& 空虚& Chr（34）& vbNewLine& _
Else& vbNewLine& _
str& strColumnName& = string.concat（& Chr（34）&''& Chr（34）&，FORMAT
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 :
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 ...
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
I think this is what you are looking for.
' 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
这篇关于Visual Studio 2008 - 导出已编辑的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
