Help with multiple line items
Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators
Help with multiple line items
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?
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?
-
- Posts: 987
- Joined: Sun Dec 09, 2007 2:49 am
- Location: UK
Re: Help with multiple line items
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
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 722 times
David
Re: Help with multiple line items
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,
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
- Site Admin
- Posts: 2227
- Joined: Mon Jun 30, 2003 12:32 pm
- Location: Melbourne, Australia
- Contact:
Re: Help with multiple line items
For the second part of the question, just use
Code: Select all
[capture(csvfield), ',', capture(csvfield), longest 0+ not cr or lf]
Re: Help with multiple line items
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.
Thanks in advance.
- DataMystic Support
- Site Admin
- Posts: 2227
- Joined: Mon Jun 30, 2003 12:32 pm
- Location: Melbourne, Australia
- Contact:
Re: Help with multiple line items
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()
Re: Help with multiple line items
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.
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
- Site Admin
- Posts: 2227
- Joined: Mon Jun 30, 2003 12:32 pm
- Location: Melbourne, Australia
- Contact:
Re: Help with multiple line items
Fairly easy to to with a capture. Happy to help - but let me know your name or purchase information thanks.
- DataMystic Support
- Site Admin
- Posts: 2227
- Joined: Mon Jun 30, 2003 12:32 pm
- Location: Melbourne, Australia
- Contact:
Re: Help with multiple line items
Hi George, here you go.
- Attachments
-
- lg777.zip
- (829 Bytes) Downloaded 706 times
Re: Help with multiple line items
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.
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
- Site Admin
- Posts: 2227
- Joined: Mon Jun 30, 2003 12:32 pm
- Location: Melbourne, Australia
- Contact:
Re: Help with multiple line items
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.
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.