Fort Myers-Naples, FL
VeraData Decision Labs has been working with Alteryx since 2008, allowing us to develop a few tips along the way to help others save time.
Within the Alteryx Platform, VeraData, like other organizations frequently uses “fixed field files” which can be an extremely time consuming process. Engineers often need to separate all columns in the data file preparation stages. The most popular method of doing this is the “fixed field ruler” or manual implementation. If your file contains a small amount of columns, you can complete this very quickly, but if you’re working on a file which contains 20 or 40 or more columns, this process can become very taxing.
I’d like to share a few ways in which you can drastically reduce the time you’re spending on fixed field files.
Tip - Don’t make changes in a customized Input Tool.
If you’re working with files that have identical layouts, you’ve most likely inserted the file in the input tool and separated the fields using the special ruler. When you begin working with the second file, refrain from using the new input tool.
Try this instead:
- Copy the previous tool
- Open its configuration
- Paste the new file in the string “Connect a File or Database”
- Click Cancel when Alteryx asks you to customize the new configuration
These steps will allow you to continue working with the new file using your old configuration.
In the case that you’re working with different types of layouts, or if you're working with these types of files often, you have the ability to create your own configuration file which will contain all fields with their respective data types, field names, and length.
Try This:
- Use Notepad++
- Take the Field Information from the layout. (be sure the file format is .flat.) Since there are only metrics in the layout and it doesn’t contain all information for the configuration file, you’ll need to write code for each field and then paste the metrics from layout.
-Or-
- Paste the formula in an excel file
- Create rows for each field
- Paste it in Notepad++.
The entire process of creating a configuration file requires just about the same amount of time as “separation by ruler.” The difference, however, lies in the way you can use this file in the future. You can use this file for identical fixed field files or you can use this as a template. The next time you need to change names, lengths, or types of fields according to a new layout….this process will be faster than creating a configuration file from the beginning. This will also be faster than “separation by ruler.”
Below are a few screenshots to help visualize the process described above:
The first screenshot is illustrating how you can use the old configuration. In this screenshot you can see the input tool configuration. Under the title “Connect a File or Database” you can see a space – here’s where you’ll paste a link on your new file.
After that, click on a free space in the workflow. The Input Tool will ask you to customize the new configuration. You’ll click “Cancel”. This step is illustrated on the second screenshot.
Screenshot #1
Screenshot #2
Open the excel table with the layout. Against the first field description, write the formula on screenshot #3. Click on the string that contains the formula. In the right corner you’ll see a small “+”. Click on it and drop it down until all of strings will be filled as on the screenshot 4.
You’ll then select the strings and copy and paste to the Notepad++. Add tags in the beginning and end of document (you can see these tags on the screenshot 5). Between these tags, you’ll place the string from the excel file. Your final version of flat file will look like example on the 5th slide.
Screenshot #3
Screenshot #4
Screenshot #5
