Ordering file read in via ODBC

[justforgroups]

Hi,
An awkward problem here I suspect!

Creating a pivot from a text file, read in via ODBC. This works fine, so naturally they want to change it

Currently:

    Set objMyPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    With objMyPivotCache
      .Connection = ConnectString
      .CommandText = "Select * From " & lv_src
      .CommandType = xlCmdSql
    End With

lv_src has the value 'data.txt' and is a tab-delimited text file.

What is required is to order the incoming data by one of the fields. Something like this I suppose:

Proposed:

      .CommandText = "Select * From " & lv_src & " [B]Order By [" & C_key & "] ASC[/B]"

c_key has the value 'prod id'

However every variant of "Order By" I'm trying gives some sort of error.
Would very much appreciate any advice! I'm assuming Order By is the right way to go, but am not sure if the dot in the file name is causing problems, or is it just the syntax of Order By.

Thanks.

 

[xenou]

It sometimes helps (for obscure reasons I cannot decipher) to concat all the elements first:

[COLOR="RoyalBlue"]strSQL[/COLOR] = "SELECT * FROM Table1 WHERE Field1 = " & x & ";"
....
.CommandText = [COLOR="RoyalBlue"]strSQL[/COLOR]

Nevertheless. there is no reason for an order by clause. You can do ordering in the pivot table within the field properties for the row or column element.

 

[justforgroups]

Thanks for the suggestion. The column needs to be sorted before the pivot is displayed, unfortunatly

Something has changed anyway due to your suggestion! I'm getting this error: "[Microsoft][OBDC Driver]Too few parameters. Expected 1"

The SQL statement looks like this (from debug):
"Select * From data.txt Order By [PROD ID];"

Thanks.

 

[xenou]

Thanks for the suggestion. The column needs to be sorted before the pivot is displayed, unfortunatly

A pivot table is nothing more than displayed source data. There is no before or after. The error you are getting is because of the [prod id] not being recognized and being interpreted as a parameter. Have you tried sorting the pivot field?

 

[xenou]

The more I think about the more it seems obvious to me that the sorting is handled by the pivot table. That would be the appropriate solution. Right-click on the field, go to the field properties, and set the desired sort field - there's no need for anything in vba or fiddling with the command text here.

That said, another try might be to use explict field names rather than the * syntax for all fields. Maybe ODBC can't recognize the sort field if you haven't passed in a field list. Or maybe its not recognizing the first row as headers. It's trial and error, I guess.

 

[xenou]

Here's another idea for you. When I look at the pivot table command text the fields are given backticks;

SELECT test2.`Field 1`, test2.`Field 2`, test2.`Field 3`
FROM test2.txt test2
ORDER BY test2.`Field 1`, test2.`Field 2`

Maybe it would work for you with that syntax:

SELECT * FROM data.txt data ORDER BY data.`Prod ID`