Unidata UDT.OPTIONS to include column headers when using TO DELIM?

[Ian McGowan]

I'm writing a utility to dump Unidata queries to a "real" excel spreadsheet, and running into a tricky problem with getting column names.  Is there a UDT.OPTION or some other trick to have Unidata write the column headers as row one of the output file?  UDT.OPTIONS 91 ON is handy for example to request OCONV'ing of the data before writing.  I'm hoping there's another option or keyword to request headers.

I can scan words in the original query and throw away anything after "list" or "sort" that's not a dictionary item, and that lines up 99% of the time, but when it doesn't work it's hard to explain to the users what's going on.

If folks are interested, I can post the code on pickwiki.com once it's working.  Right now I have two approaches - 1) convert a delimited file on disk to xlsx, and 2) take a 2-D dynamic array representing rows and columns and convert it to xlsx.

Appreciate any words of advice!

Ian

[Ian McGowan]

So, not sure if this will be helpful or not, but just in case someone has a need to produce excel extracts from Unidata (that are a little richer than either CSV or TSV), I posted a couple of programs on Pickwiki at: http://www.pickwiki.com/index.php/ConnectToMicrosoftExcel

The first program uses something called SpreadsheetML to dump a single XML file that Excel can open, that has formatting, deals with numbers and dates, and can do formulas.  There are two big problems with this approach: 1) The files get huge, quite quickly, and 2) Office 2013 and up displays an alarming message when the user opens the file.  Still, it's a pretty simple approach.

The second goes all the way and creates a native XLSX workbook from scratch.  This is very tedious, and I'd still be doing it, apart from some very nice blog posts at http://polymathprogrammer.com/about/ that help explain what's going on inside the somewhat complicated Open XML formats.  Starting from a regular excel file and trying to untangle the XML is a lost cause.

Both unfortunately have to "peek" back at the query that produced the delimited file in the first place, to set column headers, and to know which columns are dates and numbers. I have the luxury of knowing which query produced the output file, but in general you might not have that.  Still trying to find that magical UDT.OPTION to help with that ;-)

Hope this helps someone in the future trying to get their data out of a Pick-based system, or at least saves someone a little time!

Ian