How To Stop Completely Blanks Records Generating Pages Without Variable Data

OL Connect Professional DataMapper
1

Hi,

Allot of MS Excel users incorrectly delete records by pressing the delete key on their keyboard instead of deleting the row via the context menu. This causes blank records in the data. (Blank rows of data) When converting to CSV and mapping the data in the data mapper and creating a template, when the template generates a PDF in Workflow it generates ‘blank’ pages containing no variable and only the images in the template. How do I tell PPConnect to ignore records that are empty?

As usual, thanks for the assist.

Regards,

S

1 Like
2

Perhaps an easier way to approach this would be to have a process for data validation. For instance, the process can use a VBScript which loops thorugh each line of the CSV file and remove empty lines.

3

If your lines are really empty, you can use Advanced Search and Replace plugin, with “Search whole file” selected, "^
" (no quotes) as “String to search” and “Treat as regular expressions” ticked. This will remove all empty lines.

You can adopt this approach to remove lines which consist only of empty fields (eg “”,“”,“”) but you’ll have to change the regex.

4

I suppose that could work. I just thought that there could be a better way. In FormScape there is a option called “Remove Completely Blank Pages” and what that did is if a sheet contained no variable data it was ignored. Feature request?

Thanks for the assist.

Regards,

S

5

I will try it out. Thanks.

6

I imagine that if you are working with a csv file, lines with empty records will not be completely blank(empty) unless the delimiter is a tab or a white space of some sort. At least those empty records will contain only the delimiter such as commas, semi-colons or pipes…etc

I have just put together a VBScript for you. Please modify the pattern with the delimiter in your csv file. In the below example, the delimiter is comma:

dim objFSO, objInputFile, objOutputFile, objRegEx
dim myCSV, strNewLineContent, strLine

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
set objRegEx = new RegExp
objRegEx.pattern =“[^,]{1,}”

myCSV = Watch.GetJobFileName
set objInputFile = objFSO.OpenTextFile(myCSV, 1, true, 0)
strNewLineContent = “”

    Do Until objInputFile.AtEndOfStream

            strLine = objInputFile.Readline
            strLine = Trim(strLine)
            If objRegEx.test(strLine)Then

                    strNewLineContent = strNewLineContent & strLine & vbCrLf
            End If

    Loop

objInputFile.Close
set objOutputFile = objFSO.OpenTextFile(myCSV, 2, true, 0)

objOutputFile.Write strNewLineContent
objOutputFile.Close

In debug mode, just before hitting the script, if I click on View as Text, you can see that my data file does contain some empty records

Once I get passed the above script, the lines containing the empty records are removed:

You can send the use the Send To Folder to save the file with %O.csv.

7

Works like a charm. Thanks Rod.