Select column with Excel *** value

Question

Good morning friends!

I need some help.

I am making a "Select" of an Excel spreadsheet in VB.Net to import the data in the database but a column is not bringing the data correctly. This column has both numbers and *’s; when it is numbers it brings the values, but when it is 's (, , *, ****) brings {}, I would like the whole column to return as String. It is possible?

--Method importing the 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

--Example of the column

Answer 1

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

Supposing that the Planilla1 contains its data and that the Planulha2 will serve as support for this data processing, the goal is to take the data of the first spreadsheet as they are and paste in the second in text format.

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

Follows 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