rowanpol - 2011-09-01 
Datafeed Manager - User Manual

Overview

Datafeed Manager is an office automation application designed to improve productivity by reducing the workload involved in data entry and spreadsheet maintenance tasks using pre-defined rules set by the user. This application was designed after identifying the common need of data analysts and data consumers to move parts of a data source (text file, spreadsheet..etc) to another data source - for example moving some of the monthly report details to a yearly report. Datafeed Manager is designed to be highly configurable so that individual ranges of data in a file or spreadsheet can be defined to e then used as part of rules for the transferral of data, therefore removing the need to manually copy and paste data from one file or spreadsheet to another.

Introduction

Datafeed Manager is a Windows .NET application compatible with version 3.5 of the .Net Framework. Because the application has been tested against this version of the .Net framework it is suggested you download this version of the .Net framework before installing the application. See the Installation instructions for more about this. The nature of the application whereby its purpose is to read and write data on your computer means that the application is highly dependant on the right permissions being granted to the application on that machine (also see Installation for more details), and the files you wish to maintain being located in the right directory as defined within the application (see below for more details). The format of the files should be either a CSV (comma delimeted ) file or a spreadsheet (XLS or similar). Should your files not be in a compatible format we recommend you convert those files into CSV format before using them with the application.

1. Key Concepts

  1. 1 Datafeed Profile

A Datafeed Profile represents a series of files or data sources that store data in similar locations and ranges for a similar purpose. An example might be a weekly spreadsheet that comes from a client or vendor company that contains sales data to be included in a monthly spreadsheet. A Datafeed Profile would normally be equivalent to the department or organization that sends you the data that needs to be then included in further files or spreadsheets that you compile. A Datafeed Profile might for example be a series of spreadsheets that come each month from ABC Company containing the monthly sales figures. This series of spreadsheets will usually have the same type of information stored in similar locations, for example a listing of monthly sales in the first column with a grand total always at the end. If we use a Datafeed Profile of ABC Company Monthly Sales, we are expecting any file read by that profile to have monthly sales in column A and a grand total in Column A at the last row of data.

1.2 Datafeed Element

A Datafeed Element is a single range or location in a file or spreadsheet. It effectively represents a cell or range of cells. It belongs to a Datafeed Profile because what is expected to reside in that location will differ depending on the series of files/spreadsheets (Datafeed Profile) it belongs to. An example of a Datafeed Element might be a monthly sales total, but only if reading using the Datafeed Profile of ABC Company Monthly Sales, which expects a spreadsheet for a given month that always has a grand total in the same location in the spreadsheet for each month.

1.3 Datafeed Manager

The Datafeed Manager is a user screen that allows you to create or edit the collection of Datafeed Elements that belong to a given Datafeed Profile and to set the filename mask and location that defines what files represent the series of files belonging to that Datafeed Profile. The filename mask might for example be <<company name="">> monthly sales <<month and="" year="">>. For example if you received monthly sales figures in a spreadsheet from ABC Company the filename mask might select files such as ABC Company Monthly Sales January.CSV, ABC Company Monthly Sales February.CSV..etc.</month></company>

Even if a filename mask is set, you will still need to set the folder from which these files are to be read. You could create a dedicated directory where files are transferred to by FTP or detached from emails with all the files in that directory being files or spreadsheets that have a consistent purpose and format i.e. all have the same Datafeed Profile.

1.4 Manual Installation

Because DatafeedManager is a .Net application, the main steps in successfully deploying the application will be installing the .Net framework and setting up a correct folder structure with placeholder files stored in locations expected by the application upon loading. No registry keys are used by the application, so no registration or de-registration of registry keys should be required.

Extract the provided zip file to the folder C:\Program Files on your local machine.

This should create new folders to create the following folder structure on your machine:

Under C:\Program Files, a new folder called “DatafeedManager”

It is recommended that the different file types used are kept in separate folders to avoid confusion between the different xml files being used by the application.

For example
\DatafeedManager\Datafeed Profile List\
\DatafeedManager\Datafeed Profile Files\
\DatafeedManager\Datafeed Mappings List\
\DatafeedManager\Log\
\DatafeedManager\Source Files\
\DatafeedManager\Dest Files\

For minimal operation the following files are required:

\DatafeedManager\Datafeed Profile List\DatafeedProfilesList.xml

\DatafeedManager\Datafeed Profile Files\DatafeedProfile.xsd

Plus at least one Datafeed Profile file
I.e. testfilesource.xml, testfiledest.xml

\DatafeedManager\Datafeed Mappings List\DatafeedMappingList.xml

\DatafeedManager\Log\DatafeedManagerLog.txt

\DatafeedManager\Source Files\TransactionList1.xml

\DatafeedManager\Dest Files\TransactionList2.xml

If you are using Windows 7 you may experience a known issue with that version of Windows whereby all files are set to read-only and you cannot unset the read-only state despite having administrator privileges. If you experience this issue, right click on the executable file “DatafeedManager” and choose “Properties”, select the Compatibility tab and set the file to run in compatibility mode for Windows XP.

If you make these changes please note then when you run the program you will prompted with a message asking if you want the application to make changes to your computer. Choose “Yes” for this option.

2. Configuring General Settings

Start up the application and wait for the initial screen to load (Datafeed Profile Selector). This screen looks like screenshot fig 2.1 below. Please find the General Settings button towards the bottom left part of the screen and click on it to open up the General Settings screen.

Fig 2.1 Datafeed Profile Selector screen - General Settings button

The General Settings screen looks like the screenshot below (fig 2.2). This screen is used to specify file locations for storing file types used by the application and SMTP email settings.

You need to check the checkbox labelled “Send Mail” if you want to enable the email functionality. Please note that the application will pause for a few seconds while any email is sent.

Fig 2.2 General Settings screen

Use this screen to specify the following settings:

Profile List Location - this is where you store a particular file (DatafeedProfilesList.xml) that is used to keep track of the Datafeed Profiles currently being used by the application.

Datafeed Profiles Location - this is where you store the following files:
1. DatafeedProfile.xsd - a special file that is used to validate the file structure of the Datafeed Profile files.
2. “Datafeed Profile” files - this is a set of files whereby there is one file per Datafeed Profile that you create. These files will have the filename extension of “xml”.

Log File Location - This is where a text file log is to be stored that records various warnings and errors that is generated during execution of the program. This file can be opened using a text editor like Notepad for diagnostic purposes.

Email Address for Notifications - if you elect to have an email sent when the application finishes processing a file, an email will be sent to this address.

Smtp Host for Notifications - this is the stmp address for the mail server that will be used to send the notification email. You need to get these details from your email provider. Both Gmail and Hotmail have such functionality, the Hotmail SMTP address is used as an example in the screenshot.

Smtp Username for Notifications: This may also need to be provided by your email service, but is typically your email address.

Smtp Password for Notifications: The password you use to log in to your email account. Please be aware of any security risks when using your password in this manner.

Smtp Client Port: This detail should also be provided by your email provider. Port 587 is the port used by Hotmail for example.

3. Creating a Datafeed Profile

Fig 3.1 Datafeed Profile Selector screen

To create a new Datafeed Profile start by clicking on the “New” button in the top right hand portion of the Datafeed Profile Selector screen. A new blank Datafeed Profile Manager screen will appear.

You can now edit the blank Datafeed Profile that has been created. See “Configuring Datafeed Profiles” for more information.

4. Configuring Datafeed Profiles

If you have just created a new Datafeed Profile as per the steps in “Creating a Datafeed Profile” then the Datafeed Profile Manager screen should already be open.

If you need to configure an existing Datafeed Profile then go to the top right section of the Datafeed Profile Selector screen and click the button labelled “Configure” (see figure 3.1). This will open the Datafeed Profile Manager screen pre-populated with the details of the Datafeed Profile selected in the drop down box to the left of the “Configure” button.

4.1 Naming a Profile

Make sure you name your Datafeed Profile an appropriate name that reflects the kind of data you are processing. To do this specify a new Datafeed Profile name in the textbox in the top right corner of the Datafeed Profile Manager screen that is located next to the button labelled “Rename Profile”. Click on that button after you have specified the new Datafeed Profile name.
Please note the following:
Naming a profile that contains the string “New Profile” will result in a profile that will be overwritten with a blank profile upon opening in the Datafeed Profile Manager. This is because the words New Profile are reserved for when a placeholder profile is created prior to the user renaming the profile to something more specific to their needs. Therefore to avoid your profile being overwritten please do not use the string “New Profile” when naming Datafeed Profiles.

4.2 Datafeed Profile Selection Criteria

A Datafeed Profile consists of a selection of files that you wish to process. Therefore you must select the location and filename convention that dictates what this file selection will be. Go to the top right portion of the Datafeed Profile Manager screen as indicated by the rounded box in the screenshot below (See Fig 4.1)

Fig 4.1 Datafeed Profile File Selection Criteria area of the Datafeed Profile Manager screen.

The first textbox directly under the heading “Profile File Selection Criteria” specifies the file location of the files where the files to be processed will be stored. Use the button on the right of the textbox (labelled as “…”) to open a Windows folder dialog box. Here you can choose the directory where your files will be kept.

In the textbox labelled “Filename Mask” enter the string (sequence of characters) you will require in a filename for a file to be selected as a file to process. An example is you might want all files in that directory with the filename CSV to be processed in that directory. In that scenerio you would specify “CSV” as your filename mask. You need to use a wildcard character (“”) to indicate where you filename mask starts or ends. For example:

*CSV - any sequence of characters in the filename are selected so long as the filename ends with the characters “CSV”. Acceptable files would include ”Data001.CSV”, ”ABCSTUFF.CSV”, ”TonysData.csv”..etc

ABC* - any sequence of characters in the filename are selected so long as the filename starts with the characters “ABC”. Acceptable files would include ”ABCFILE01.TXT”, ”ABCSTUFF.TXT”, ”ABCzzz.TXT”..etc

After you have determined the location and naming convention to be used for selecting your files click on the “Apply” button to save the changes.

  1. Adding / Editing Datafeed Elements

In the Datafeed Profile Manager go to the bottom left area of the screen as shown in the screenshot below (see Fig 5.1)

You can open the Datafeed Profile Manager in the following way: go to the top right section of the Datafeed Profile Selector screen and click the button labelled “Configure” (see figure 3.1). This will open the Datafeed Profile Manager screen pre-populated with the details of the Datafeed Profile selected in the drop down box to the left of the “Configure” button.

Fig 5.1 Datafeed Element maintenance area of the Datafeed Profile Manager screen.

When you start with a new Datafeed Profile there will be an existing placeholder Datafeed Element you can edit. If you want to add a new Datafeed Element to your existing list of Datafeed Elements in a Datafeed Profile then click the button labelled “Add New Datafeed Element”.

In the panel that is headed with “Add or Edit Datafeed Element” you can specify the attributes of your Datafeed Element. A Datafeed Element essentially describes a cell or range of cells within your data. The best example of this is when you import a CSV file into Excel and you can see your data apportioned into cells distributed with columns and rows. Cells specified for Datafeed Elements have the following format.

Columns - Alphabetical where A is column 1, B is column 2 and so on.
Rows - Numbers, where 1 is the first row, 2 is the second row and so on.

The column is specified first, followed directly by the row number.

i.e.
[A-Z][0-9] e.g. A1 is column 1 row 1, C10 is the cell at the third column and the tenth row..etc

Specify whether the Datafeed Element is for a cell or a range by selecting the appropriate value from the drop down box located near the “start cell” and “end cell” textboxes.

In the case of a “cell”, only the start range value is used. The end range value is set to “n/a”. Click Save to save the datafeed element to the data feed element collection that belongs to the profile.

You can edit a datafeed element by selecting one of the datafeed elements from the datafeed element collection and clicking on “edit data feed element”.

Fig 5.2 Datafeed Element Collection

Datafeed elements can be used for either identifying a source location of values, or a destination location of values. In the case of a datafeed element being a destination, effectively only the start cell is used as that marks the beginning of any cell range that will be populated with values from the source file.

  1. Files Already Processed / Files to Process

Note that in the bottom right corner of the Datafeed Profile Manager are two lists of files. The handling of processing of files is done on the initial Datafeed Profile Selector screen, however the two listings on the Datafeed Profile Manager screen show which files have had all their Datafeed Elements processed and which still need to be done.

  1. Setting a Datafeed Mapping

Once Datafeed Profiles and Datefeed Elements have been set up within those profiles (see sections 3-6), you can now map the transfer of data from one file to another using “Datafeed Mappings”. When on the initial Datafeed Profile Selector screen note the middle section of the screen dedicated to setting Datafeed Mappings (see fig 7.1 below).

Figure 7.1 Setting a Datafeed Mapping

Notice the set of drop down menus. The drop down menus on the left are for setting the source data for the mapping. Choose a Datafeed Profile from the “Source” drop down menu. The “Source Element” menu should then be populated with all the available Datafeed Elements for that Datafeed Profile. Repeat the same process for the destination data feed profile and data feed elements.

The Datafeed Mapping dictates how data will be transferred from one file to another. Ultimately a range or a single cell of data (source data feed element) is being copied from one file (source data feed profile), and then set a specific location (destination data feed element) in another file (destination data feed profile).

Fig 7.2 source and destination data feed elements when processing a data feed mapping.

Once the combination of source and destination data feed profiles and data feed elements has been set up, select a “list group” for the data feed mapping to belong to. This is simply a way that data feed mappings can be filtered in the data feed mappings list. By filtering the data feed mappings you can create groups of data feed mappings that get processed as a set.

Once you have set the “list group” click on “Set Mapping” to add the Datafeed Mapping.

8.Processing Datafeed Mappings

Once you have set up Data feed Mappings (see section 7), you can then process data feed mappings you select to transfer data between files.

Once you have set up data feed mappings you can either select one data feed mapping to process from the data grid, or you can check the “Process All” checkbox which will process all the data feed mappings in the data grid. If you apply the filter on the top of the datagrid, just the filtered mappings will be processed (I.e. data feed mappings not in the view will not be processed).

Fig 8.1: Selecting data feed mappings to process.

Once your selection of data feed mappings has been made, click on “Process Selected”.
The selected files will be processed. Destination files will be written to the folder assigned to the destination data feed profile for each data feed mapping. Rather than overwriting files new files are created that have the following naming convention:

<<original destination="" data="" feed="" profile="" file="" name="">>_<<date time="" as="" integer="">>_new</date></original>

Please note the following exceptions:

Files are processed in alphanumeric order, therefore you should use a naming convention for your files that results them being ordered in the desired sequence. Files that contain the string “_new” will not be processed as they are deemed to have been the result of the processing of an existing file that has the same file name without the “_new”.

Known issues:

Naming a profile that contains the string “New Profile” will result in a profile that will be overwritten with a blank profile upon opening in the Datafeed Profile Manager. This is because the words New Profile are reserved for when a placeholder profile is created prior to the user renaming the profile to something more specific to their needs. Therefore to avoid your profile being overwritten please do not use the string “New Profile” when naming Datafeed Profiles.

Files are processed in alphanumeric order, therefore you should use a naming convention for your files that results them being ordered in the desired sequence.

Files that contain the string “_new” will not be processed as they are deemed to have been the result of the processing of an existing file that has the same file name without the “_new”.