Batch for extracting lines into new files in CALC

classic Classic list List threaded Threaded
12 messages Options
tk5ep tk5ep
Reply | Threaded
Open this post in threaded view
|

Batch for extracting lines into new files in CALC

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,
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

Hi,

On 01/17/2012 11:23 AM, 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,
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.
>
> --
> 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.
>


--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

tk5ep tk5ep
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

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,
Calvin Kim Calvin Kim
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

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

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

In reply to this post by tk5ep
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


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

In reply to this post by Calvin Kim
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"= :p
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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

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"= :p

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.

> Sub Import2xls
> Const cDataSource = "Chargen"
> Const cTableName = "MAT"
> Const cColumnName ="ID"
> Const cStartID = 405
> Const cEndID = 407
> Const cPath = "/tmp/"
>
> Dim addr as new com.sun.star.table.CellRangeAddress
> sQuery = "SELECT * FROM """& cTableName &"""WHERE """& cColumnName &"""="
> ' print squery
> Dim xlsprop as new com.sun.star.beans.PropertyValue
> xlsprop.Name = "FilterName"
> xlsprop.Value = "MS Excel 97"
>
> for i = cStartID to cEndID
> doc = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, Array() )
> dbx = doc.DatabaseRanges
> dbx.addNewByName("Import2xls", addr)
> dbr = dbx.getByName("Import2xls")
> dsc() = dbr.getImportDescriptor()
> for each prop in dsc()
> if prop.Name = "SourceType" then prop.Value = com.sun.star.sheet.DataImportMode.SQL
> if prop.Name = "SourceObject" then prop.Value = sQuery & i
> if prop.Name = "DatabaseName" then prop.Value = cDataSource
> next
> dbr.ReferredCells.doImport(dsc())
> doc.storeAsURL(convertToURL(cPath & format(i, String(len(cEndID),"0")) &".xls"), Array(xlsprop))
> doc.close(True)
> next
> End Sub



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Calvin Kim Calvin Kim
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC


On 01/18/2012 03:08 PM, Andreas Säger wrote:

> 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"= :p
>
> 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.
>
>> Sub Import2xls
>> Const cDataSource = "Chargen"
>> Const cTableName = "MAT"
>> Const cColumnName ="ID"
>> Const cStartID = 405
>> Const cEndID = 407
>> Const cPath = "/tmp/"
>>
>> Dim addr as new com.sun.star.table.CellRangeAddress
>> sQuery = "SELECT * FROM """& cTableName &"""WHERE """& cColumnName
>> &"""="
>> ' print squery
>> Dim xlsprop as new com.sun.star.beans.PropertyValue
>> xlsprop.Name = "FilterName"
>> xlsprop.Value = "MS Excel 97"
>>
>> for i = cStartID to cEndID
>>     doc =
>> StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0,
>> Array() )
>>     dbx = doc.DatabaseRanges
>>     dbx.addNewByName("Import2xls", addr)
>>     dbr = dbx.getByName("Import2xls")
>>     dsc() = dbr.getImportDescriptor()
>>     for each prop in dsc()
>>         if prop.Name = "SourceType" then prop.Value =
>> com.sun.star.sheet.DataImportMode.SQL
>>         if prop.Name = "SourceObject" then prop.Value = sQuery & i
>>         if prop.Name = "DatabaseName" then prop.Value = cDataSource
>>     next
>>     dbr.ReferredCells.doImport(dsc())
>>     doc.storeAsURL(convertToURL(cPath & format(i,
>> String(len(cEndID),"0")) &".xls"), Array(xlsprop))
>>     doc.close(True)
>> next
>> End Sub
>
>
>
Nice. Thank you for the tip. I shall remember it for next round.
Keep the good work.

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

tk5ep tk5ep
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

In reply to this post by tk5ep
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
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

Patrick,

On 01/19/2012 06:32 AM, tk5ep wrote:

> 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.
>
> Thanks anyway,
> Patrick
>
> --
> View this message in context: http://nabble.documentfoundation.org/Batch-for-extracting-lines-into-new-files-in-CALC-tp3666623p3672075.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

SARoets SARoets
Reply | Threaded
Open this post in threaded view
|

Re: Re: [libreoffice-users] Re: Batch for extracting lines into new files in CALC

Unsubscribe me please!

The unsubscribe procedure does not work!

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

eskroni eskroni
Reply | Threaded
Open this post in threaded view
|

Re: Batch for extracting lines into new files in CALC

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.


On Thu, 19 Jan 2012 19:43:44 +0000
[hidden email] wrote:

> 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 [hidden email] 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

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted