Text Files as Input to Fast Excel Tools

This tutorial is about using text files as inputs to an Excel Planning System.

The Fast Excel Method shows you how to build integrated planning systems with Excel.  These planning systems have modules that are integrated with each other.  They can also be integrated with Manufacturing or Enterprise Resource Planning (MRP or ERP) Systems.

Text Files provide the simplest and most reliable way to do this.  Even if you are sharing data from one Excel workbook to another, character delimited text files are the best way to go.  They are faster, less prone to error and can be over-written and the connection to an Excel workbook will survive.

Here is an introduction to Import text Files to an Excel Workbook.  This tutorial will build on this, so if you don’t know how to import text into Excel then read this introduction first.

There are two sources of text file data that could be used as input to an Excel Planning System:

1. An automatic export of a data table or report from an Enterprise Resource Planning System (ERP) or its host database.
2. An output of a data table from an Excel System or Tool.

In both cases, this method uses a network folder location to share text files between users and any host system.

The Fast Excel Development Template has a sub-routine and button that copies the source text files from the network location to a folder on the user’s local machine.

The file locations for local and network drives are managed in the FilePath sheet.

A Fast Excel Planning System will have no data residing in a workbook.  The first step is to refresh the data connections with the source text files.  All calculations start from there.

Generate Text Files From Enterprise/ Manufacturing Resource Planning

This process is going to be highly dependent on your host ERP/MRP system and the database on which it sits.

To automatically generate text files, you will need to define the fields and tables or reports that match the source data for the Fast Excel Planning System.

Then ask your database or ERP system administrator to write a script or set a job that can dump these text files into your chosen network drive location.  This needs to be an automated job, as the data usually refreshes daily.

The preferred format for the text files is .txt, tab delimited.  It is also possible to use other delimiter characters such as comma, pipe.  However, tabs are least likely to occur as values in the source data.  This is the best choice.

Generate Text Files from Excel

If you are testing and developing a Fast Excel Planning System, or only have one user and hold all data in Excel, then you can generate the text files from Excel.

As an example, we are going to use the input data from the Capacity Planning Tool.

Download the Capacity Planning Tool.

Download the Input Text Files.

Download the Input Data Generator.

This is the Input Data for the Capacity Planning Tool.  Ethe example of how input data can sit on Query worksheets, colored in GREEN.  It has one sheet for each of the input files, plus a Query Template to bring in other text files.

See here for instructions on how to import text files.

The Query Template is part of the Fast Excel Development Template.  You can see a tutorial on the Query Template here.

Once you have the the Input Data Generator open, you can follow these steps:

1. Select the Input File Location as a file path on your hard drive.
2. Go to each of the work sheets and paste your data over the top.  Be sure to select the right sheet for your data (only BOM data on the BOM sheet).  Preserve the heading names.  Keep all data as a continuous table from column A, Row 10 at the top left corner.  with no empty columns in between data columns.
3. Repeat for all of the other sheets.
4. If your input data does not have the right sheet, then you can copy the Query Template and rename it to your desired data table name.
5. Hit the “Output Files” button.
6. Check your file location to see if the text files have been created.
7. Change the file location in the Fast Excel Planning Tool to the same location where you have created the text files.
8. Hit the “Refresh Input Files” button on the Fast Excel Planning Tool.
9. Voila.  The tool should run with the newly created text file input data.  You can now adjust the data in the Input File Tool and run the function in the Fast Excel Planning Tool.
10. If you get an error then it is likely that you have one of the following problems:
    1. Incorrect file path in “Input File Location”
    2. Non-continuous table in one of the data sheets.
    3. Incorrect name for a data table sheet.

This is a general guide to using Input File Tool to generate text files that feed a Fast Excel Planning Tool.  The procedure is applicable for any Fast Excel Method Tool or System.

For help, please contact Support and please be detailed as to the problem you face.