Help with multiple line items

[lg777]

I need to convert an invoice that looks something like this:

Payment 1
Invoice 1,$2000
Invoice 2,$3000
Invoice 3,$4000
Invoice 4,$5000
Total,$14000

Payment 2
Invoice 5,$100
Total,$100

Payment 3
Invoice 6,$100
Invoice 7,$100
Invoice 8,$100
Total,$300

Output:
Payment1, Invoice 1 - $2000 Invoice 2 - $3000 Invoice 3 - $4000 Invoice 4 - $5000,Total,$14000
Payment2, Invoice 5 - $100,Total,$100
Payment3, Invoice 6 - $100 Invoice 7 - $100 Invoice 8 - $100,Total,$300

Is this possible with multiple or variable line items?

[DFH]

The attached ZIP file contains a TP filter which may be 99% towards a solution.

It's mostly a search replace list using Perl expressions. Plus some tidy up afterwards.

The trial area was used to demonstrate your input and the output matches your requirements.

David

[lg777]

Yikes, I can't read this as I'm using an older version, 8.1.1.

Is it possible to send me the text of the script?

And many thanks for helping.

Got another question about my example though:

In Payment 1, I can use a Exact search/replace by using [capture(csvfield)],[capture(csvfield)] to capture the first two fields but how do I match the rest of the line if I don't need it?

I will replace it with $1,$2

Payment 1
Invoice 1,$2000,,,,,,
Invoice 2,$300,,
Invoice 3,$4000,,,
Invoice 4,$5000
Total,$14000,

[DataMystic Support]

For the second part of the question, just use

Code: Select all

[capture(csvfield), ',', capture(csvfield), longest 0+ not cr or lf]

[lg777]

Sorry to bring this up but is it possible to get a text output of that script contained in the zip file. I have to use an older version so I just need to get an idea of how the perl expressions were used.

Thanks in advance.

[DataMystic Support]

Sure:

Code: Select all

// TextPipe Pro 9.2 Single User Edition
// Purchased by: 1
// 1

// This JScript code was generated by TextPipe -
// the fastest search and replace tool on the planet
//   Download from: http://www.DataMystic.com


//The global TextPipe Application object
var TextPipeApp;
//The global TextPipe Filter Window
var TPWindow;


//connect to TextPipe if we haven't already
function js_connectTextPipe() {

  try {
    if (TextPipeApp == null) {
      TextPipeApp = new ActiveXObject("TextPipe.Application");
    }
  }
  catch(e) {
    MsgBox( "TextPipe is not installed.\n\n" +
      "Please download and install it from\n" +
      "http://www.DataMystic.com",
      48 + 0,
      "TextPipe needs to be installed" );
  }
  TPWindow = TextPipeApp.newWindow();

}


//disconnect from TextPipe
function js_disconnectTextPipe() {

  TPWindow.closeWithoutSave();
  TPWindow = null;

}


// 1) Use this code to simply load the saved filter:

function js_load_filter() {

  js_connectTextPipe();
  TPWindow.openFilter( "C:\\Users\\Simon Carter\\AppData\\Local\\Temp\\Special filter for lg777 on TextPipe Us" +
  "er Forum.fll" );
  if (TPWindow.execute() == false) MsgBox( 'ERROR \r\n' + TPWindow.errorText() );
  js_disconnectTextPipe();

}   


// 2) Use this code to build the filter from scratch, from a web page,
//    ASP script, .vbs file etc.

function js_build_filter( TPWindow ) {

var f1,f2,f3,f4,f5,f6;

TPWindow.startFilters();
TPWindow.clearAllFilters();
TPWindow.logEnabled = true;
TPWindow.logFilename = "textpipe.log";
TPWindow.logAppend = true;
TPWindow.logThreshold = 500;
TPWindow.inputMode = 1;
TPWindow.inputBinaryFiles = 0;
TPWindow.inputBinarySampleSize = 100;
TPWindow.inputPromptOnEach = false;
TPWindow.inputPromptOnReadOnly = false;
TPWindow.inputDeleteFiles = false;
TPWindow.inputInsideCompressed = false;
f1 = TPWindow.addCommentFilter( "Special filter for lg777 on TextPipe User Forum" );
TPWindow.startSubfilters();
f2 = TPWindow.addReplaceFilter( "(Payment) (\\d+)", "$1$$2,", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );TPWindow.addReplaceFilter( "(Total)", ",$1", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );TPWindow.addReplaceFilter( "(Invoice )(\\d+),\\$", "$1$$2 - $$", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );TPWindow.addReplaceFilter( "\\r\\n", " ", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );TPWindow.addReplaceFilter( "(Payment)", "\\r\\n$1", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );TPWindow.addReplaceFilter( " ,", ",", 4, true, false, false, false, false, false, false, 0 );
f1 = TPWindow.setPerl( 4096, false, false, false, true );
f2 = TPWindow.addSimpleFilter( 11, false );
f2 = TPWindow.addSimpleFilter( 10, false );
f2 = TPWindow.addStringFilter( 20, "\r\n", false );
TPWindow.endSubfilters();

TPWindow.outputMode = 1;
TPWindow.outputRetainDate = false;
TPWindow.outputTestMode = 0;
TPWindow.outputAppend = false;
TPWindow.outputOnlyOutputChangedFiles = 1;
TPWindow.outputOpenOutputOnCompletion = false;
TPWindow.outputExtension = ".short.txt";
TPWindow.outputFolder = "";
TPWindow.outputRemoveEmpty = false;
TPWindow.endFilters();


//File List:
TPWindow.clearAllFiles();
TPWindow.addFile( "", 0, 1 );

} 


function js_generate_filter() {

  js_connectTextPipe();
  js_build_filter( TPWindow );
  if (TPWindow.execute() == false) MsgBox( 'ERROR \r\n' + TPWindow.errorText() );
  js_disconnectTextPipe();

}


// 3) Use this code to build the filter from scratch and process a form field

function js_filter_string() {

  js_connectTextPipe();
  js_build_filter( TPWindow );
  form1.output.value = TPWindow.processString( form1.input.value );
  js_disconnectTextPipe();

} 


//Uncomment the following line to load the filter
//  js_load_filter()
//Uncomment the following line to generate the filter
//  js_generate_filter()


[lg777]

Thanks! I'm not sure I understand the script yet but I'll dig a bit deeper.

In the same vein, I have something like this that I need to figure out and not sure if the script will help as well but here it goes:

From:
Colour, Red
A,1
A,2
A,3
A,4
Colour, Blue
A,1
A,2
A,3
Colour, Green
A,1
A,2
A,3
A,4
A,5
A,6
A,7

To:

Colour, Red
A,1, Red
A,2, Red
A,3, Red
A,4, Red
Colour, Blue
A,1, Blue
A,2, Blue
A,3, Blue
Colour, Green
A,1, Green
A,2, Green
A,3, Green
A,4, Green
A,5, Green
A,6, Green
A,7, Green

Is this simple to do in TP or would I need to add some scripts as it has variable line items?

TIA.

[DataMystic Support]

Fairly easy to to with a capture. Happy to help - but let me know your name or purchase information thanks.

[DataMystic Support]

Hi George, here you go.

[lg777]

Thanks! Unfortunately won't load in my latest version 8.6.1.

I got some of these working and would like feedback if it is either an okay way to do it or not.

For the invoice one I started, I simply match a cr and Invoice and replace with just Invoice to merge all the invoice lines together. Seems simple enough.

For the Colour one, I embed html tags between the group of colours and use the restrict between tags to populate the colours for each line by search/replace. It works but seems kind of a workaround.

Now I'm trying to search a value in current file and trying to look it up in another file and copy over the line that it matches over. I read the sticky but still kind of stumped on how to do something like this.

[DataMystic Support]

Hi George,

In the TP install, in database\perform database lookup using vbscript.fll, there is an example of pulling a value from a database. You could use this approach on your file, perhaps by creating an System DSN (data source name) for your lookup file, and then connect to it like a database.

Another approach would be to read the entire file into memory at the start of the script.