Reusing Excel content in Flare

This forum is for all Flare issues related to importing files or projects.
Post Reply
oceanclub
Sr. Propeller Head
Posts: 277
Joined: Thu Oct 10, 2013 4:45 am
Location: Dublin

Reusing Excel content in Flare

Post by oceanclub »

I have a request where information on fields shown on screen is stored in the Excel sheet data dictionary; this would for example contain the names of fields and descriptions of them and be update periodically.

When I perform a Flare build, I want to be able to pull content from these Excel fields (only selected particular rows) and use it in topics.

Is it possible to automate that process?

P.
Psider
Propellus Maximus
Posts: 811
Joined: Wed Jul 06, 2011 1:32 am

Re: Reusing Excel content in Flare

Post by Psider »

In flare 2018 you can import excel and slurp it into topics or snippets. And you can base the import on visible rows/columns or all data (assuming I'm reading it right - I haven't tried myself).

So theoretically you could filter the data to the rows you need, then slurp the excel file into snippets, keeping the excel file linked so that it refreshes the snippets every build. Then the snippets would be added into your topics and would update automatically.

I'm not sure what options there are for breaking the excel data into snippets, although breaking by rows seems a likely option.
jjw
Sr. Propeller Head
Posts: 133
Joined: Thu May 08, 2014 4:18 pm
Location: Melbourne

Re: Reusing Excel content in Flare

Post by jjw »

I don't think you could use the Flare import tool directly to pick out specific rows. You would have to do some pre-processing in Excel to mirror the rows and columns you wanted on a different worksheet and then import that worksheet.

From what I can tell from a quick play, Flare will create one file per worksheet. You can specify the maximum number of rows to import - but you still only get one file, it just discards any extra rows. So, if you want a snippet per row, you'll have to write each row to a different worksheet first.
oceanclub
Sr. Propeller Head
Posts: 277
Joined: Thu Oct 10, 2013 4:45 am
Location: Dublin

Re: Reusing Excel content in Flare

Post by oceanclub »

Thanks all, this sounds promising at least! I'll play around and see what happens.

P.
Psider
Propellus Maximus
Posts: 811
Joined: Wed Jul 06, 2011 1:32 am

Re: Reusing Excel content in Flare

Post by Psider »

I had a quick play because importing excel sounds like it could be useful.

It is possible to extract each row to a separate snippet. It's also possible to select specific rows and columns as the last step in the import process.

I did have it hang initially, but I think that was because I set it to create a snippet for each row and also selected use the first row as the table header - which doesn't really make a lot of sense - if you have a single row, you're unlikely to want a header row for each one (well, at least I can't think of a likely use case...).

There does seem to be a bug when splitting by row (Split topics or snippets by maximum rows: 1) where the last row of the spreadsheet doesn't get a snippet created, so you need to have a final row with say a space in it, so that your last row of data is snippified. Don't de-select the blank row in the wizard or Filters tab, because as new data is added, the de-selected row doesn't move, which makes sense because Flare only knows that you deselected the row for some reason, not why you deselected it.

If you're doing this, you want to make sure items are only added to the end, as the row number is the primary key. So if you import a spreadsheet you'll get snippets named like "Sheet1_3" (3rd row of sheet 1 - it uses the sheet name if you gave it one). If you then add a row between row 2 and 3 and re-import, the new row 3 will become your "Sheet1_3" snippet, which could mean you now have wrong data in the topics containing Sheet1_3.flsnp.

Similarly with deleting rows, this will potentially screw up the content in particular topics.

I haven't played with the import hidden data settings yet.

So it's possible to import data rows into snippets, but you'll need to be really careful about adding/deleting data in the source excel file, which might not really work out for a data dictionary, depending on how it's managed and what you're trying to achieve.
Rona Kwestel
Sr. Propeller Head
Posts: 212
Joined: Wed Apr 04, 2007 11:50 am

Re: Reusing Excel content in Flare

Post by Rona Kwestel »

Is anyone having issues where attempting to import an Excel spreadsheet of reasonable size/complexity just hangs? I have an Excel file that has 9 worksheets (2 of them hidden), and I tried instructing Flare to just import one of the non-hidden ones as a topic, and even still, it just gets indefinitely stuck in the "Import Progress" phase after clicking on Import... in the Flare UI. Even clicking on Cancel doesn't seem to allow it to recover; I end up having to kill it in the Task Manager. My suspicion is that it's related to cell formulas that check the contents of hidden cells to determine the value of the given cell. I tried both checking and unchecking the Import hidden rows/columns option, but it made no difference. I was able to successfully import a tiny spreadsheet with no cell references or hidden data, confirming that the core functionality works, but the spreadsheet I want to import is more complicated. I will open a ticket with MadCap, but wanted to see if anyone else has insight here.
Rona Kwestel
Sr. Propeller Head
Posts: 212
Joined: Wed Apr 04, 2007 11:50 am

Re: Reusing Excel content in Flare

Post by Rona Kwestel »

I figured out the problem, and am posting it here in case anyone else runs across it.

Somehow, the spreadsheet I was attempting to import had one worksheet in it that had an extremely large number of empty columns (the columns were deep into the triple letters). I'm not sure exactly how it got into this state, but in order for Flare not to attempt to process thousands of empty columns, I needed to delete all the excess columns (or rows) that were "added" to the worksheet (perhaps just by virtue of scrolling well beyond the "end" of the sheet).

As described here, pressing Ctrl+End in the worksheet will select the last cell in the sheet. If it's beyond where the actual data stops, select all the extraneous columns (and/or rows) and delete them. Select cell A1 and save the changes, then press Ctrl+End again just to make sure that Excel now shows the actual last cell in the worksheet; if not, repeat the previous steps until no excess columns/rows remain.

Hope this is helpful to anyone else for whom Flare keeps hanging and/or crashing upon trying to import a spreadsheet.
Rona Kwestel
Sr. Propeller Head
Posts: 212
Joined: Wed Apr 04, 2007 11:50 am

Re: Reusing Excel content in Flare

Post by Rona Kwestel »

One other bug I discovered, for which I have no good solution, is that if a worksheet has hidden columns that you tell Flare to ignore upon import, and there are column headers that span several columns among which are some of those hidden columns that will not get imported, Flare does not take into account the now removed hidden columns and the resulting colspan values are incorrect. You can manually correct them after the import is completed, but you would need to do this every time you re-import, and if this is part of your build process, it kills any automation you hoped to achieve.
Rona Kwestel
Sr. Propeller Head
Posts: 212
Joined: Wed Apr 04, 2007 11:50 am

Re: Reusing Excel content in Flare

Post by Rona Kwestel »

Just noting that both bugs have been reported to MadCap.
Post Reply