Using Text Files to Connect Excel with Systems Data

Excel-based planning and scheduling systems need data.   Best practices in developing Excel systems say:  “Separate data storage, calculation and reporting.”

A database the best place to store data.  Our Excel tool needs to bring it in, perform calculations and then send it back out.  The output tables can then feed reporting and, perhaps, update the system.

So, what is the best way to integrate with system data?

The simplest method is using text files.  Text files are fast, reliable and universal.  They are flat files of ASCII characters that can form a table with rows represented by lines and columns separated by some delimiter character.

Surprising to many, text files provide a much better way of connecting with Excel than do other Excel files.  The process is faster, more robust and virtually every software system can export data in a text file format.

How to Import Text Files into Excel

Select the cell and worksheet where you want the imported data.  Use a blank workbook, or better, use our Query Template in the Fast Excel Development Template.   See a video tutorial for using the Query Template.  As a convention, we always bring data in with the first heading in Cell A10.

On the data menu, select “Get External Data | From Text”.

The first thing you need to do is find the input file.  Excel wants to know the full filepath and file name.  If an import is not working on a Excel system that has been developed on someone else’s computer, then it is likely that it can’t find the import file.

Once you have selected the correct file, you will see this three part wizard.  The first step is to indicate if the file is delimited or fixed width and the language coding of the characters.  We recommend you always use tab delimited text files whenever possible, so select delimited.

Click next and you will be asked to select the delimiter character.  The most common characters are Tab or Comma.  We recommend tab as this character should never occur naturally in clean data, whereas commas are far more likely.

The next screen asks you to select the data format.  We use “General” for anything that can be measured, “Text” for everything else and “Date” if the date is in a string form rather than Excel 1900 number format (eg. “40507” equals 25-Nov-2010).

Be consistent with field headings that are going to crop up in multiple tables.  You will have trouble with lookups and finding item codes if one is imported as “General” and another as “Text”.  “General” will also remove leading edge zeroes from item codes and import them incorrectly.  Use “Text” for anything that is not a quantity of something.

Unnecessary fields can be skipped.

Click finish and you will get to this screeen. Check that the data is going into the correct cell.  This is the top left corner of the data table.

Before you click “OK”, you will need to adjust “Properties…”

We want to automate the refresh function of the import.  When the data changes, you will want to get a new text file that over-writes the old one with the same name.  Refresh will bring fresh data into the Excel system as frequently as you like.

In Properties, you should uncheck the “Prompt for file name on refresh” option. If this is checked, then Excel will bug you for the name of the text file import every time you refresh and interrupt our production scheduling routine.

You have some options for an automatic refresh, but we typically do not use this as the refresh needs to be inserted into a sequence of planning calculation function so we will call for the refresh programmatically.

The “Adjust column” option is better to be taken off; this will avoid annoying column widths that expand to accommodate long item descriptions and other field data strings.

Our Fast Excel Development Method uses pastedown macros that take a formula that is defined in row 8 and paste the results into every row of a table.  The results are replaced by values to improve stability and calculation speed.

This means that the “Fill down formulas in columns adjacent to data” is not required.

Set the properties as you see here and then click “OK” and “Ok” again on the previous screen.

You now have a table full of data in the Excel that is connected to a text file.  As the text file changes, the data will also update.  This is the fastest and most reliable way to connect an Excel planning and scheduling system with data from an MRP or ERP system.

Using the Fast Excel Query Template

Here you can download the Fast Excel Development Template and watch tutorial videos that go into detail about performing this text file import, automatically refreshing data and performing calculations.

The “Create Names” button you see here is a way to automatically create Dynamic Named Ranges that are invaluable in building production planning and scheduling systems in Excel.

The “Update” button is linked to the Query(“SheetName”) macro that will perform the refresh and activate the pastedown macros by the side of the imported data.

The Fast Excel Development Template is the way we begin each development of an Excel planning system.  It works like software and uses modular macro functions to automate many of the common features of a planning and scheduling system.

Since we started using the Fast Excel Development Template, we have saved thousands of hours of development time and brought ever more powerful systems to our clients’ operations.

You can download the template for free (as a free registered user) and benefit from a flexible platform and development environment.

We provide consulting and coaching support to help you use the template and build your own production scheduling system.