A Handy Guide on How to Quickly Import Excel Data to MadCap Flare

Want to maximize the use of your existing data to create content in MadCap Flare? Keep reading.

One rainy morning in Copenhagen my boss had just given me the task of creating individual release note topic files based on the information in the company ERP system. Ahead of me I had a five-hour task of copying and pasting. There must be a better way, I thought to myself.

I got a cup of coffee, and started researching ways to automate the information transfer from the ERP system to my topics in MadCap Flare. After some time, I had found a solution: by exporting the ERP data to a CSV file, and then modifying that file in Excel, I could automatically generate the topics I needed.

Note: Flare’s native Excel import lets you import spreadsheets as tables into topics and snippets easily, keeping the source and target in sync. Learn more about MadCap Flare’s native Excel import functionality.

My requirements were different though. I needed to create one topic per line in the Excel sheet, where each column contained data such as “Title” and “Content”. The data in the “Title” column had to be imported to the <h1> element in the topic, and the “Content” column had to be imported to the first <p> element.

After several tries, I was able to complete the task in just 30 minutes - 10 times faster than doing the task manually. To show you how to get data from Excel to Flare using my approach, I’ll walk you through a simple example that you can extend to fit your needs. I hope that by sharing this with you that you too can save time and energy by using Excel to speed up your work in Flare.

Generating topics using Excel

Let’s walk through the process of setting up an Excel document from which you can generate Flare topic files.

Set Up the Excel Document

To set up the Excel document, follow these steps:

Open Excel. Add columns called Title, Content, File Name, HTML, Escaped HTML and Batch Command.

Add a couple of lines with titles and content. For example:

Now set up the formulas in the cells, following these steps:

In the File Name column, set the name of the topic file as you want to save it, for example, "Topic A.htm". You can use a formula to set the name automatically based on the topic title, for example:

In the HTML column you are going to build the file content, based on the description:

In the Escaped HTML column, you need to escape the > and < symbols by prefixing them with ^ using Excel’s SUBSTITUTE() function, like this:

Finally, in the BATCH Command column, create the DOS command that you will subsequently paste into the command prompt to generate your files:

Generating Flare Topic Files

To generate the topic files:

1. Open the command prompt [WINDOWS+R], CMD, [ENTER]
2. Browse to the folder where you want to create the files, using the CD command.
3. Go to the Excel sheet, and copy the BATCH Command column.
4. To create the files, paste the data into the command prompt.

Speeding Up the Process​

You can also use the free Kaizen Plugin to do the same thing, but faster.

Start by creating an Excel file with two columns: "Title" and "Content", then follow these steps:

1. Sign up, download and install the Kaizen Plugin.
2. Open your Flare project.
3. Add a new topic file, and name it [[Title]].htm
4. Replace the text “Delete this text and replace it with your own content.” with [[Content]].
5. Open the Kaizen Plugin tab, and click Import Data (Excel).
6. Select the Excel file and click Execute to generate the files.

The new topic files now appear in the same folder as the template file.

Now that you know the method for generating one Flare topic per line from your Excel data, you can use it to make good use of your existing data. Try using this method to generate topic files and snippets, target files, and TOC files.