Select column with value *** of Excel

1

Good morning, friends!

I need some help.

I'm doing a "Select" from an Excel spreadsheet in VB.Net to import the data into the bank however a column is not bringing the data properly. This column has both numbers as * 's; when they are numbers it brings the values, but when they are 's (, , * , ****) brings {}, I would like the whole column to return as String. Is it possible?

- Method that imports spreadsheet

Dim dtbSheet As New DataTable
    Dim wrkConnectionString As String = String.Format(CONNECTION_STRING_OLEDB_EXCEL, pPathFile)
    Dim wrkSQL As String = String.Format("SELECT * FROM [{0}$]", pSheet)

    'Intancia conexão
    Dim wrkConnection As OleDbConnection = Nothing
    Dim wrkCmd As OleDbCommand = Nothing
    Dim wrkDa As OleDbDataAdapter = Nothing

    Try
        wrkConnection = New OleDbConnection(wrkConnectionString)
        wrkCmd = New OleDbCommand(wrkSQL, wrkConnection)
        wrkDa = New OleDbDataAdapter(wrkCmd)
        wrkDa.Fill(dtbSheet)
    Finally
        If wrkConnection IsNot Nothing AndAlso wrkConnection.State = ConnectionState.Open Then wrkConnection.Close()
    End Try

    Return dtbSheet

- Column example

    
asked by anonymous 14.07.2016 / 15:28

1 answer

0

As you are selecting data from a spreadsheet, a simple solution is to treat this data before this selection, which can be done by VB.Net , using a macro > Excel or VBA (less indicated in this case since you are using VB.Net ).

Assuming Sheet1 contains your data and Plan2 will support this data handling , the goal is to get the data of the first worksheet as they are and paste in the second in text format.

The code below refers to the code in VBA or a Excel macro , which may be tailored for you , or depending on your use of the spreadsheet, this macro could be triggered "always" before your system picks up this data.

Follow the code:

Private Sub CopiarColarEspecial()

Sheets("Planilha1").Select

Range("A1:A90").Select

Selection.Copy

Sheets("Planilha2").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

End Sub
    
15.07.2016 / 04:59