InsightIQ Blog

4.0 Tricks of the Trade, Unica V75 – Solving Problems Loading Variable Length Records from Flat Files

View more Blog Posts

Oct 22 2009

Have you ever seen the error displayed in Figure 1 in Unica Campaign V75? For this blog on Unica tips and tricks we are going to evaluate an issue encountered when loading flat files with variable columns widths into Unica. I will provide a solution so that you do not have to encounter this issue when loading flat files in the future.

Figure 1. Error caused by incorrect parsing of variable length fields in a flat filet1

For larger flat files, Unica's parsing capabilities are limited. So when you are connecting a select process box to a flat file, Unica may parse only a subset of the records in a flat file. For instance, if there is a longer field in a record within your flat file, Unica may not automatically create the appropriate field width for that record.

Figure 2 displays a flat file wherein the field "email" is wider in record #3 than record #2. When Unica parses the flat file, it may default the field width to the smaller field's width.

Figure 2. Flat file with variable column width t3

In Figure 3 you can see the default values that Unica applies to the flat file. These default values may not be set to accommodate wider columns in the subsequent records.

Figure 3. Unica only parses a subset of the records when determining default column widths t2

Consequently, when you attempt to run a report (e.g., cell content report) displaying all of the available columns in the flat file, you may see the message in Figure 1. This error would also occur when attempting to add this column to a mail list process box in the personalization tab. So the problem is that Unica only uses a subset of the records in order to determine the default column width for the columns in the flat file.

How are we going to trick Unica into solving this problem? We could attempt to fix this by trial-and-error, but that would not be my counsel. If you read the error message in Figure 1, the error indicates that the field named "phone" with a length of 12 needs to be enlarged to a width of 13 in order to accommodate record # 65,547. So you could attempt to make that fix and then try to load the flat file again. However, if you look at the flat file in Figure 2 and compare that to the default values Unica has applied in Figure 3, you may quickly identify some other problems with additional columns. For instance, in Figure 3 Unica has determined that the column widthfor the last name column is 3. Since the last name in the first record of the flat file is Gee (see Figure 1 above), Unica applied a width of 3 to the lastname column. If you look at record #3 in Figure 1 above, you can see that the last ("Longestname") has a width wider than 3. So you get my point?

If you were to attempt to fix this issue with trial and error, you may find yourself running multiple iterations in order to fix the issue. Imagine if you have a lot of variable width columns and a lot of records. Trial-and-error is not an option if you need to get the job done efficiently. For this tip and trick, we will need to use an open source tool like Notepad++. The key concept here is that you need to know your data. Since the flat file that I used for this blog has 65,000 rows, I cannot visually scan all the data in order to "know my data". Thus I used a text editor like Notepad++.

I used Notepad++ as follows in order to analyze my flat file and provide the correct column widths for each column.

Step 1. Open flat file in Notepad++. First you will have to use the link provided above and install Notepad++ on your computer.

Step 2. Use Notepad++ to line up your file by the comma separator. Figure 4 depicts the menu option to perform this operation on your flat file.

Figure 4. Line up multiple lines by (,) using Notepad++t4

Step 3. Insert a ruler in Notepad++. Use the TextFX menu and then the TextFX Tools sub-menu in order to select the "Insert Ruler" option.

Figure 5. Depicts the menu selection used in order to insert the rule in Notepad++.t7

Step 4. Highlight each column and read value for column width

The ruler enables you to see the record length in totality and then you can use your mouse to select each column as I did in Figure 6 in order to get the specific column width. Once I highlighted the email column you can see the "Sel: 35" value displayed on the dialog bar which means the column width is 35.

Figure 6. Note the Sel: 35 in the middle of the dialog bar at the bottom of the screen.  

t6

Step 5. Update the select process box to use the column widths provided by Notepad++

Figure 7 shows the updated values entered into the select process box for the flat file with variable column width. Note the difference between these values and the values in Figure 3 above. Also note that I added the value of 1 to each column just for padding. So even though the first column has a width of 35, I went ahead and used a width of 36 just for good measure :) 

Figure 7. Updated select process box in Unicat8

And now you are able to correctly parse any flat file into Unica Campaign correctly the first time you try. This time the trick was not to trick Unica. Unica attempts to determine column widths by parsing a subset of the data file. So the real trick to getting to "know your data" was to understand the need for a tool that can parse data using the entire file (i.e. Notepad++).

You can respond with your feedback by adding a comment to my blog or by dropping me a line at greg_denlea@csgsystems.com.

You can also read my previous tricks of the trade blogs; "Printing a Mail List File Even When There are No Records";  "How do I dynamically add a date stamp to my list files?"; and "Copying and Pasting in Unica Campaign".

This article is filed under:

View more Blog Posts

Leave a Comment

  • Your email won't be published on our site.

All Fields Required