Extract from Excel Cells?

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

XtremeMaC
Posts: 2
Joined: Wed Aug 13, 2014 1:27 am

Extract from Excel Cells?

Postby XtremeMaC » Wed Aug 13, 2014 1:50 am

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?

Thanks!

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

Re: Extract from Excel Cells?

Postby DataMystic Support » Wed Aug 13, 2014 8:50 am

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

XtremeMaC
Posts: 2
Joined: Wed Aug 13, 2014 1:27 am

Re: Extract from Excel Cells?

Postby XtremeMaC » Thu Aug 14, 2014 12:38 am

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....
Attachments
test.rar
Test File
(6.09 KiB) Downloaded 531 times

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

Re: Extract from Excel Cells?

Postby DataMystic Support » Thu Aug 14, 2014 9:28 am

This is a bug. We've just fixed it, and will be releasing TextPipe 9.7.1 shortly.
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: No registered users and 1 guest