Help with multiple line items

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

lg777
Posts: 7
Joined: Thu Dec 01, 2011 12:12 pm

Help with multiple line items

Postby lg777 » Fri Apr 06, 2012 11:48 am

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
Posts: 636
Joined: Sun Dec 09, 2007 2:49 am
Location: UK

Re: Help with multiple line items

Postby DFH » Mon Apr 09, 2012 1:10 am

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
Attachments
Special filter for lg777 on TextPipe User Forum.zip
Special filter for this issue
(877 Bytes) Downloaded 363 times

lg777
Posts: 7
Joined: Thu Dec 01, 2011 12:12 pm

Re: Help with multiple line items

Postby lg777 » Wed Apr 11, 2012 12:28 pm

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,

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Help with multiple line items

Postby DataMystic Support » Thu Apr 12, 2012 9:01 pm

For the second part of the question, just use

Code: Select all

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

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

lg777
Posts: 7
Joined: Thu Dec 01, 2011 12:12 pm

Re: Help with multiple line items

Postby lg777 » Wed Aug 22, 2012 3:07 pm

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.

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Help with multiple line items

Postby DataMystic Support » Wed Aug 22, 2012 11:43 pm

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()
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

lg777
Posts: 7
Joined: Thu Dec 01, 2011 12:12 pm

Re: Help with multiple line items

Postby lg777 » Thu Aug 23, 2012 8:49 am

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.

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Help with multiple line items

Postby DataMystic Support » Thu Aug 23, 2012 3:11 pm

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

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Help with multiple line items

Postby DataMystic Support » Fri Aug 24, 2012 6:58 am

Hi George, here you go.
Attachments
lg777.zip
(829 Bytes) Downloaded 341 times
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

lg777
Posts: 7
Joined: Thu Dec 01, 2011 12:12 pm

Re: Help with multiple line items

Postby lg777 » Fri Aug 24, 2012 7:33 am

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.

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Help with multiple line items

Postby DataMystic Support » Fri Aug 24, 2012 10:50 am

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.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “TextPipe Tips and Tricks, Questions and Support”

Who is online

Users browsing this forum: Baidu [Spider] and 6 guests