如何使用visualstudio和oracle读取带有特殊字符的excel列名

Question

晚安，

我正在上传一个excel到oracle，我发现excel的一些列的名称中有特殊字符，这些字符是：

"FECHA/CONV"；"与"；N°CODIGO"；

如果我删除了特殊字符，它可以很好地工作，但我不能编辑excel文件，我只需要阅读它，

我该如何做才能使visual不会在mappeo和excel表的选择中产生错误。

我将感谢任何帮助。

我的确切错误是：没有值​​已经为一些设置的参数设置了。

谢谢，

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim _connString As String = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.100.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORACLE))); User Id=USER; password=PASS;"
Dim rutacarpeta As String
Dim NombreArchivo As String
Dim tablaBBDD As String
'Dim ot As OracleTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
Using conn As OracleConnection = New OracleConnection(_connString)
conn.Open()
Dim ot As OracleTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
Try
rutacarpeta = "C:2021"
NombreArchivo = "FILE_0.xlsx"
tablaBBDD = "FILE_TEST"
Dim xlsxConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;';"
xlsxConn = String.Format(xlsxConn, rutacarpeta + NombreArchivo).Trim()
Using excel_con As OleDbConnection = New OleDbConnection(xlsxConn)
excel_con.Open()
'Dim hoja As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)()
Dim command As OleDbCommand = New OleDbCommand("SELECT FECHA/CONV,CODIGO,ACUERDO FROM [FILE$]", excel_con)
Dim dr As OleDbDataReader
dr = command.ExecuteReader()
Using bulkCopy As OracleBulkCopy = New OracleBulkCopy(conn)
bulkCopy.BulkCopyTimeout = 90000
bulkCopy.DestinationTableName = tablaBBDD
bulkCopy.ColumnMappings.Add(New OracleBulkCopyColumnMapping("FECHA/CONV", "FECHA"))
bulkCopy.ColumnMappings.Add(New OracleBulkCopyColumnMapping("N°CODIGO", "CODIGO"))
bulkCopy.ColumnMappings.Add(New OracleBulkCopyColumnMapping("ACUERDO", "ACUERDO"))
bulkCopy.WriteToServer(dr)
ot.Commit()
bulkCopy.Close()
End Using
End Using
Catch ex As Exception
Try
ot.Rollback()
Catch ex1 As Exception
MessageBox.Show(ex1.Message)
End Try
MessageBox.Show(ex.Message)
End Try
End Using
End Sub

Answer 1

当我指出你引用了表名时，你可能会大吃一惊，因为它包含一个特殊的字符，这很有效。。你只是忘了引用列名

SELECT [FECHA/CONV],[CODIGO],[ACUERDO] FROM [FILE$]

他们并不都需要引用，但这不会有什么坏处。此外，如果您在设置批量复制列映射时在oracle方面遇到任何错误，也许可以考虑将excel名称别名为更简单的名称，并使用

SELECT [FECHA/CONV] as A,CODIGO,ACUERDO FROM [FILE$]
...
...Add(New OracleBulkCopyColumnMapping("A", "FECHA"))