Batch for extracting lines into new files in CALC

users
#1

Hi All,

I have a 130000 lines CALC file.
I need to :
- filter the lines according a criterion
- export these lines to a new XLS file named after this criterion
- email the file

this has to be done about 500 times (criterions)

Has anybody a solution ??

Thanks for any help,

#2

Hi,

Hi All,

I have a 130000 lines CALC file.
I need to :
- filter the lines according a criterion
- export these lines to a new XLS file named after this criterion
- email the file

this has to be done about 500 times (criterions)

Has anybody a solution ??

Thanks for any help,

How complex are the criteria? Is the data on one or several sheets? Do you need to keep formulas in the final spreadsheet or just need to display the data nicely?

I might convert the spreadsheet to a database, with each sheet being a table then query the database using SQL. I can write a more complex query than is possible in Calc covering multiple tables (sheets) at once.

#3

Hi Jay,

The Calc file has only one sheet, no formulas, only raw datas.

I need to extract all lines having the same value (numerical) from one
column and create an XLS sheet with these lines. This XLS file must be named
with the value used to filter.

For example, filter all lines who have 001 in a specific column, extract the
lines and create a file named 001.XLS.

This has to be done about 124 times, for each and every different value in
the given column (only one column)

Is it more understandable ?

Thanks,

#4

I usually convert it to .csv and extract data with PHP, but I love to see how to do it with Calc.
cK

#5

Am 17.01.2012 17:23, tk5ep wrote:

Hi All,

I have a 130000 lines CALC file.
I need to :
- filter the lines according a criterion
- export these lines to a new XLS file named after this criterion
- email the file

this has to be done about 500 times (criterions)

Has anybody a solution ??

Thanks for any help,

--
View this message in context: http://nabble.documentfoundation.org/Batch-for-extracting-lines-into-new-files-in-CALC-tp3666623p3666623.html
Sent from the Users mailing list archive at Nabble.com.

Leave behind all spreadsheets. There is no technical reason nor convenience factor in favour of using spreadsheets with a database dump of raw data.
The dBase driver of the Base component supports indexing for fast lookups within millions of rows.
Save as dBase in a *dedicated directory*. dBase is a database in a directory.
Connect a Base document to the *directory*.
Open the table for editing and add some indices on relevant fields.
Create a set of simple queries. Parameter queries are supported.
Video tutorial on something similar with a small amount of data in a sheet: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=40403&p=186845&hilit=+parameter#p186158

#6

Am 18.01.2012 14:56, Calvin Kim wrote:

I usually convert it to .csv and extract data with PHP, but I love to
see how to do it with Calc.
cK

Manually without any coding:
Connect a registered Base document to the spreadsheet. A connection to an indexed dBase copy might be more performant.
Create a parameter query: SELECT * FROM "Table" WHERE "Column"= :stuck_out_tongue:
Create a Calc template with an import range bound to that param query.
Bind some shorcut to command "refresh data range", say Ctrl+R
Declare the template as default template.
Switch to xls as default file format.

Ctrl+N gets a new document.
Ctrl+R prompts for parameter :p. Enter 1 and wait for the import to complete.
Ctrl+W closes this document prompting for the xls file name. Enter 001.

Repeat these 3 steps until you are finished.

#7

Am 18.01.2012 18:13, Andreas Säger wrote:

Connect a registered Base document to the spreadsheet. A connection to
an indexed dBase copy might be more performant.
Create a parameter query: SELECT * FROM "Table" WHERE "Column"= :stuck_out_tongue:

Use the parameter query for testing if you get the wanted records for a given number :p.
Store the following Basic code somewhere in the global library container (aka "My Macros").
Modify the 6 constants on top of the code. Registered name of the database, table name, column name, first ID to substitute, last ID to substitute and the target path.
The code works without further modifications but with any type of database such as address books, csv, spreadsheets, dBase, MySQL, HSQLDB, MS Access, MS servers, Oracle servers, anything connectable with a Base document.

#8

Nice. Thank you for the tip. I shall remember it for next round.
Keep the good work.

#9

Hi All,

Thanks for the different answers, but even if there are good ideas, they are
all more demanding than doing all the work by hand in Calc.

I could filter, copy , paste the result in a new sheet and save it... 4 or 5
clicks to be repeated 124 times.

I was looking for a way to do that with a batch or an automated process. If
i have to do a more complex process 124 times, there is no gain....

Thanks anyway,
Patrick

#10

Patrick,

Hi All,

Thanks for the different answers, but even if there are good ideas, they are
all more demanding than doing all the work by hand in Calc.

I could filter, copy , paste the result in a new sheet and save it... 4 or 5
clicks to be repeated 124 times.

I was looking for a way to do that with a batch or an automated process. If
i have to do a more complex process 124 times, there is no gain....

One other option is to use a macro to do this.

#11

Unsubscribe me please!

The unsubscribe procedure does not work!

#12

Hi,

did you actually read the instructions? I'm talking about this ones
here:
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/

Btw, this link is added to each email, so you should have seen it.

If you did read the instructions, then why didn't you write to the
postmaster, as suggested there? No one of us users can unsubscribe you.

Unsubscribe me please!

The unsubscribe procedure does not work!

If all your attempts to unsubscribe just don't work, then - as a last
resort - please send a message to postmaster@documentfoundation.org and
include the following information: The lists you want to be
unsubscribed from, the address you're subscribed with, and detailed
information about the problems you encountered. Please include/forward
the replies you received from the mailing list system, if you received
any.

So, please write to the postmaster and include the necessary
informations.

Sigrid