1

I am using CFSPREADSHEET to read an XLS file into a query. Then reordering the columns as necessary. However, the query-of-query I am using retains alphabetical column order.

The XLS file has the fields in the correct order, but when converted to a query the fields are alphabetized.

I output the results to a CSV using this nifty UDF and the fields are still alphabetized as proof that the q-of-q is not adhering to my request.

In order to correct this issue I'm trying to use a q-of-q to reformat the columns in their proper order.

<cfspreadsheet action="read" src="#local.tmpPath##local.tmpFile#" excludeheaderrow="true" headerrow="1" sheet="1" query="local.qTemp" />

<!--- Change column order around as necessary --->
<cfquery name="local.qTemp" dbtype="query">
    SELECT
            Name, Address, City
    FROM
            [local].qTemp
</cfquery>

<cfset local.newCSV = QueryToCSV(
    query = local.qTemp,
    fields = local.qTemp.columnlist,
    createHeaderRow = true,
    delimiter = '|'
) />

<cffile action="write" charset="windows-1252" file="#local.tmpPath#foo.csv" output="#replace(local.newCSV, """", "", "ALL")#" nameconflict="overwrite" />

The column order when I output is:

  • Address,City,Name

Instead of the query order:

  • Name,Address,City

I read this post about the topic, but it didn't address my issue specifically (unless I am reading it wrong).

justacoder
  • 2,684
  • 6
  • 47
  • 78
  • Why does the column order matter? – Miguel-F May 11 '17 at 15:41
  • Using a bulk insert method for SQL Server and I was told the columns have to exist in a specific order. I am only following directives. – justacoder May 11 '17 at 15:43
  • 1
    How is the bulk insert method getting called with the data from your query? What I'm getting at here is that you can define the order when you output the query results using the specific column names. – Miguel-F May 11 '17 at 15:51
  • @Miguel-F, The bulk insert occurs after this block which I do not have access to and cannot be modified currently. – justacoder May 11 '17 at 16:07
  • 1
    The CFDump may be what's alphabetizing the columns. What is the final format you need for your bulk insert? – CFMLBread May 11 '17 at 16:23
  • As @PMascari said, sometimes cfdump "lies". It favors user-friendly presentation over accuracy, so it could be there is no problem at all. That said, I do not see anything in the snippet that shows exactly *how* you are using this QoQ in relation to bulk insert. Hard to tell whether or not there is an issue without seeing that piece ... Could you please update your question with that information? – Leigh May 11 '17 at 16:40
  • @Leigh, I added more descriptions to the problem. The bulk insert portion isn't as important to this task as it exists within its own legacy code so I have to shoehorn this new code for compatibility. – justacoder May 11 '17 at 17:51
  • @AngrySpartan - Maybe I missed it, but ... the updates do not really clarify the "alleged" issue you mentioned with bulk insert :-) Are you sure there actually IS a problem here - or are you just assuming that because of the cfdump? Like we mentioned - cfdump lies ;-) It *displays* the query columns alphabetically, but they are still ordered correctly internally. Use the tip in the blog to see the *actual* column order, ie `queryObj.getColumnNames()`. – Leigh May 11 '17 at 18:01
  • 1
    See http://trycf.com/gist/611a60f247d3faad8938ac92dab99fde/acf2016?theme=monokai – Leigh May 11 '17 at 18:25
  • @Leigh, this is an excellent resource tool! In addition, I used `arraytolist(query.getColumnNames())` for the fieldList when I executed QueryToCSV() for shiggles (extra step that I can keep in for this need). That worked! Feel free to shape it as a fully answer so I can give you credit. – justacoder May 11 '17 at 19:52
  • Yeah, it is an awesome site - great for quick tests and collaborative efforts! In fairness, @PMascari pointed out the issue with cfdump, so I think they should post it as an "answer". Thanks though. – Leigh May 11 '17 at 20:32
  • @PMascari, please create a full post for your answer so I can give you credit, per Leigh. – justacoder May 11 '17 at 20:35
  • 2
    Ohh... just saw the latest updates with the CSV code. The QoQ actually had nothing to do with the problem. (That is why I kept asking to see the relevant text file/bulk insert code ;-) You will see the same issue with the original query too. `query.columnList` ALWAYS lists query columns in alphabetical order. It is a long standing gripe of many CF dev's. To get the real order you either have to use the undocumented `query.getColumnNames()` or build your own list from `GetMetaData()`. – Leigh May 11 '17 at 21:09
  • Since the real issue turned out to be something different (and it is a common question) I'm posting an reference answer for the next time this comes up. I think it is still worth posting a separate answer about cfdump since that too is a common gotcha when troubleshooting query issues. – Leigh May 16 '17 at 16:13

2 Answers2

4

(From comments...)

The QoQ actually has nothing to do with the problem. The same issue would occur with the original query. Unfortunately, query.columnList ALWAYS lists columns in alphabetical order. It is a long standing gripe of many CF dev's. To get the "real" order you must use a different technique such as:

  • The undocumented query.getColumnNames() method (OR)

  • Build your own list from the GetMetaData(query) results

    columns = [];
getMetaData(qry).each(function(elem,index){
    arrayAppend(columns, elem.name);
});
writeDump(var=arrayToList(columns), label="Real Column Order");
Leigh
  • 28,765
  • 10
  • 55
  • 103
-1

You can use 

<cfspreadsheet action="write" query="yourqueryname" filename="yourfilename">

it will output a spreadsheet with the column order of your query select statement

cfEngineers
  • 664
  • 6
  • 16