How to create a text file, so when it opens in Excel, the lines are grouped together?

Question

I am collecting some data through a Perl script. Data needs to be reviewed and processed by others using Excel. I am currently writing data as a tab delimited text file, and Excel can open it just fine.

There, the data hierarchy, however, would be easier if reviewers saw a tree rather than a flat list. That is, instead of presenting data in columns,

foo foo1 foo foo2 foo foo3 bar bar1 bar bar2 ... 

present it as a tree with a mouse click for extension:

 foo foo1 foo2 foo3 bar bar1 bar2 ... 

The Excel group function (found in 2007 in the Data> Structure> Group section) is a good match for this presentation, which is a bit easier to work than pivot tables.

What is the easiest way to go from this flat list of columns to this grouped list? Ideally, I could write the data in text form so that Excel automatically applies the grouping when it was imported. Alternatively, if there were a small number of steps that the reviewer could apply after importing the data, for example, using a macro or template, this would also be normal.

Answer 1

Since you are already using perl, I suggest you create the excel file directly in perl using the excellent CPAN Spreadsheet :: WriteExcel module that supports Excel Symbols .

It works something like this:

  . . $worksheet->write('A2', 'foo'); $worksheet->write('B3', 'foo1'); $worksheet->write('B4', 'foo2'); $worksheet->write('B5', 'foo3'); $worksheet->set_row(2, undef, undef, 0, 1, 1); $worksheet->set_row(3, undef, undef, 0, 2); $worksheet->set_row(4, undef, undef, 0, 2); $worksheet->set_row(5, undef, undef, 0, 2); . . 

Answer 2

• Select all rows, including column headings, in the list that you want to filter.

  ShowTip Click the upper left cell of the range, and then drag it to the lower right cell.

• From the Data menu, select Filter, and then Advanced Filter.
• In the Advanced Filter dialog box, select Filter List in place.

• Check the box next to “Unique entries only” and click “OK.”

  The filtered list is displayed and duplicate rows are hidden.

• From the Edit menu, select Clipboard.

  The Clipboard taskbar is displayed.

• Verify that the selected filter list is still selected, and then click Copy Copy.

  The filtered list is highlighted using bounding loops, and the selection is displayed as an item at the top of the clipboard.

• From the "Data" menu, select "Filter" and then "Show All".

  The original list is displayed again.

• Press the DELETE key.

  The original list has been deleted.

• In the clipboard, click an item in the filtered list.

  The filtered list is displayed in the same place as the original list.

Answer 3

Recent versions of Excel (2003 is what we use here) can use the xlsx XML format. The xlsx file is a zip XML file. If you want to create a file that opens in Excel with the right settings, try this: first create a template file with the desired grouping. Save it as xlsx. Unzip the file using standard zip software. Look what's inside. I did not work with the grouping specifically, but all the commands in your table will be in xml form, you will need to find out where the grouping is installed. Then it is a matter of making appropriate changes to the xml and re-sealing it. A little effort, but you can use this method to programmatically create files that are pre-grouped. There may be Perl libraries specifically designed for this, I don't know.

Answer 4

A CSV file is also very easy to generate. OpenOffice allows you to choose how to parse things, but excel excludes comma-separated columns (without any quotation) and CRLF delimited rows.

 A1,A2,A3 B1,B2,B3 

and etc.