Page 1 of 1

Extract from Excel Cells?

Posted: Wed Aug 13, 2014 1:50 am
by XtremeMaC
Hi there,

I've just come across TextPipe & ExcelPipe and have been fiddling around with it so far pretty useful.
However, I'm stuck at one filtering:
What I would like to do is for either program to search for multiple strings (i.e. apple|banana) and return the contents of the matching cell from multiple excel workbooks & worksheets and make a summary of them. All data is on column A, no cells are merged. Each workbook contains around 30 sheets.
So each match should be on a new line on the text file output.
I'm using "Extract line matching (grep)" option with "Process inside compressed...", but it is returning almost the whole excel file.
With ExcelPipe, I've entered these query strings to be replaced & highlighted and it works, but there is a lot of data to go through, so I'd like to see summary and automate this process.

Example Excel worksheet:
A5: There were 5 apples in the basket
A6: Monkey ate 3 bananas on Aug 12.
A7: No animals ate peaches.
A8: Not matching query.
and so forth.

Can anyone help out?


Re: Extract from Excel Cells?

Posted: Wed Aug 13, 2014 8:50 am
by DataMystic Support
Hi there,

To extract data, you need to use TextPipe's Filters\Convert\Excel spreadsheets to text filter, and then process the resulting text. You can use Filters\Remove\Delimited fields (CSV, Tab, Pipe etc) to remove all columns except for column 1.

Re: Extract from Excel Cells?

Posted: Thu Aug 14, 2014 12:38 am
by XtremeMaC
Thank you for your input.

The excel workbook has 30 sheets. Is "Excel to Text" supposed to process all sheets in the range? As an output I am only getting a blank first sheet.
The only populated column is column 1 so I do not need to remove anything with "Remove\Delimited fields"
Each row has different information in it, I'm trying to extract the ones matching with my search criteria. So if anything I'd like to remove not matching rows/lines.

I've used the Excel to Text convert function, now all I am getting is a blank excel output or text output depending on what I choose in "File Output". I've also clicked on generate output files "Always". Blanks...

If I remove all the filters except "Excel to Text Convert" function, should I be getting an output of the excel in a text file? if so I'm not getting that either..

I'm also trying to from "Add" "insert line(s) of text" just to see if it actually writes. I still get empty results...

Can you please help out?
I'm attaching the sample excel file I'm trying to work with....

Re: Extract from Excel Cells?

Posted: Thu Aug 14, 2014 9:28 am
by DataMystic Support
This is a bug. We've just fixed it, and will be releasing TextPipe 9.7.1 shortly.