Importing PDF - column value floating

leandrorezende

Forums

Hi everyone from the forum.
Since I started work with IDEA I faced some situations that seams common on day a day work. One of then occurs importing pdf files, where one of the columns is filled with text values, that sometimes fill more than one line and do not apresent a pattern.
In this case, it's very hard o match each line with the correct field, that in the most of the cases is divided in more than one row, only by using layers.
The case that I am talking about is exemplified at the attached file, where the "comments" collumn is the one with the feature I mentioned.
If someone here have already faced this problem, maybe already defined a good approach that can help me in this case.

Brian Element Thu, 06/01/2017 - 18:45

Hi Leandro,

I think what you need to do is use the multiline option in the print report reader.  I have recreated your example to walk you through it.

Open your file in the print report reader.  Select the first line that contains some information to use as the base layer.

Select the standard layer option.

You will now have to select a trap to show the report reader which lines to bring into IDEA.  I used the fiscal number as it appears on every line and it is numeric so in this case it is a good trap.

The lines have been selected but you can see that multilines have not been selected.

For the comments field highlight the information making sure that it is long enough to take in any additional fields.

In the field details enter the name, Comments, and under the attributes select the Multi-line option.

You will then have several options for ending the field on, namely a blank line, a defined field or a specified line lengh, such as three lines.  In this case select the defined field as the comments end when a new record is hit.

All the comments are now highlighted.

There is one final option to select, at the bottom of the attributes you can decide how you want to handle extra spaces.  The options are space to one in which if there are multiple space the report reader will delete them only leaving one space, the alternative is to leave all the spaces in, in this example select the space to one.

Save the layer and if we select the preview option you can see how all the lines are now being imported.

Finally import the file into IDEA.

leandrorezende Fri, 06/02/2017 - 13:14

Hi Brian, thank you once again!
 
I really didn't see these Attibutes option, can you believe! 
 
And your hint worked perfectly for me. 
 
Enjoying the topic, can you show one example of how to use the "Filter" optin? In this case I don't need, but I will probably use in the future. I tried to use the "Exclude" option, but it seems it not working. 

Brian Element Sun, 06/04/2017 - 16:55

Hi Leandro,

Sure, here is some help for the Filter option.  I will use the Travel_Report.prn to illustrate their use.  The Travel report file can be found in the Tutorial folder.  I have brought the file in and created the layer.

The Filters can be found as the last item in the Field Details.

As you can see there are two options, the first allows you to include or exclude certain items and the other one allows you to select a range.

I will use the town field to illustrate how to use the include/exclude filter.  Here is an example of some of the cities that are available in the town field.

Now suppose we are only really interested in Dallas and New York for our import.  I would make sure that the Airlines Field Details is selected and under Filters click the ellipsis (…).

This will bring up a dialog to either include records with the following data or exclude records.  In this case I want to include Dallas and New York so next to the Add button I would first add DALLAS (it must be an exact match).

Then I click on OK, you would do the same thing with New York and click Add.

Once I have listed all the items that I am interested in I would click OK.  Now I would only see items for New York and Dallas.

To make any corrections I just load the dialog for that field and select the item I want to remove and click on Delete or Add additional items. 

Now using the same example how about I exclude Miami, so first I will remove Dallas and New York using the Delete button.  I then click on the Exclude record with the following data item and enter MIAMI.

Once done I can click on Add and then OK and now your list will have all items except for Miami.

Next, I can add on a range filter, the range filter only works for amount fields.  So above we have an amount field, maybe I only want to look at items between $400 and $1,000.  To do this make sure the Amount field is selected and then click on the Range ellipsis.

This brings up a dialog where you can enter a from / to range.  The drop down next to the From and To hold either a >, >= for the From an <, <= for the To.  For this example we can enter 400 for the From and 1,000 for the To.  I have also selected the >= and the <= so the range will include 400 and 1000.

I then select OK and now the items selected only include amounts between 400 and 1000.

To clear the range just open the Range Filter dialog and select the Delete button.

 

leandrorezende Mon, 06/05/2017 - 15:55

That's nice Brian, thanks for the explanation!
It was a very useful information and will help a lot in my future works.