question

RashmiGupta avatar image
0 Votes"
RashmiGupta asked Viorel-1 edited

Select query does not work when column name has parenthesis in excel sheet

Hi,
I am trying to fill the data of an excel sheet in a data table by using select query. When the sheet has parenthesis or any other special characters in its column names, I replace each one with "#" but execution results in error.

  Private Sub ReadExcel
     Dim sqlReadExcel as String = "Select [ModelCode] as ModelCode,[ListPrice] as ListPrice,[DiscountMultiplier] as Property39,[Cost] as Cost,[LeadTime#days#] as 
           LeadTime,[Manufacturer] as Manufacturer from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'"
    
      If sqlReadExcel <> Nothing Then
    
             Dim oDatabaseconnString As String
             Dim dbConnection As OleDbConnection
             Dim oDataAdapter As OleDbDataAdapter
             Dim excelPath  as String = "E:\excelFile.xlsx"
             oDatabaseconnString = "PROVIDER='Microsoft.ACE.OLEDB.12.0;Data Source='" & excelPath & "';Extended Properties='Excel 8.0;'"
             dbConnection = New OleDbConnection(oDatabaseconnString)
             dbConnection.Open()
             oDataAdapter = New OleDbDataAdapter(sqlReadExcel, dbConnection)
             Try
                 oDataAdapter.Fill(dtExcelData)
             Catch ex As Exception
             Finally
                 oDataAdapter.Dispose()
                 oDataAdapter = Nothing
             End Try
    
             dbConnection.Close()
         End If
    
       End Sub

The execution of 16th line results in exception. The exception message is "Specified cast is not valid."


Any suggestions what should I do?
Thanks in advance


dotnet-csharpoffice-vba-devdotnet-runtimeoffice-access-devdotnet-standard
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Why did you replace the special characters, and does it work if you do not include the special columns into SELECT statement?


0 Votes 0 ·

It was suggested in a post on stackoverflow. Even if I don't replace, I get same error. I tried removing all types of special characters from the column name in the query but did not succeed n got same error.

0 Votes 0 ·

Did you try these experimental queries too?

Select ModelCode from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'

and

Select ModelCode from [PPIF 2.6 Price Format$]

What happens in each case?

0 Votes 0 ·
Show more comments

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

After the experiments, add the problematic column. If it is "LeadTime (days)" in Excel, then:

Select ModelCode, [LeadTime (days)] from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'

Use the exact name of the column.

Also try adding two parameters to connection string:

oDatabaseconnString = "PROVIDER= . . .;HDR=YES;IMEX=1"


· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried it n problem got solved. Thankyou

0 Votes 0 ·

Hi @Viorel-1 ,
I was experimenting with column names having special characters at different positions. When I put the special characters in the beginning or ending of column name or enclose with
parenthesis like Cost($), execution results in error even if the connection string is same as you suggested. But, it works fine when they are placed between the column name like Lead@Time.
Any idea how to resolve the issue? Or should I put it as a separate question?

0 Votes 0 ·

Did you check if all columns are returned in case of "select * from ..."?


0 Votes 0 ·

Yes, it is working fine.

0 Votes 0 ·
Show more comments