macro to export data in multiple txt files

Creating a macro - Writing a Script - Using the API

macro to export data in multiple txt files

Postby croko » Tue Mar 12, 2019 10:22 pm

I have an .xls file with lots of columns (A to DM) and rows (9000+) that I can open in OpenOffice.

I'm looking for a macro to run in OpenOffice in order to export some data from this file in 8 separate tab delimited .txt files, like this:

In txt file T.txt should be exported all rows (except row 1) with greater than 0 values in column T and all the data (tab delimited) from this colums: columns from B to S, column T, column AB.

In txt file U.txt should be exported all rows (except row 1) with greater than 0 values in column U and all the data (tab delimited) from this colums: columns from B to S, column U, column AB.
In txt file V.txt should be exported all rows (except row 1) with greater than 0 values in column V and all the data (tab delimited) from this colums: columns from B to S, column V, column AB.
In txt file W.txt should be exported all rows (except row 1) with greater than 0 values in column W and all the data (tab delimited) from this colums: columns from B to S, column W, column AB.
In txt file X.txt should be exported all rows (except row 1) with greater than 0 values in column X and all the data (tab delimited) from this colums: columns from B to S, column X, column AB.
In txt file Y.txt should be exported all rows (except row 1) with greater than 0 values in column Y and all the data (tab delimited) from this colums: columns from B to S, column Y, column AB.
In txt file Z.txt should be exported all rows (except row 1) with greater than 0 values in column Z and all the data (tab delimited) from this colums: columns from B to S, column Z, column AB.
In txt file AA.txt should be exported all rows (except row 1) with greater than 0 values in column AA and all the data (tab delimited) from this colums: columns from B to S, column AA, column AB.

Please anyone can help? Many thanks in advance!

PS: I dont use Excel, the macro should be able to run in OpenOffice Calc 4.1.4 on a Windows 10 machine. Thanks!
Last edited by robleyd on Wed Mar 13, 2019 1:24 am, edited 1 time in total.
Reason: Remove Known Issues icon
OpenOffice 4.1.4 on Windows 10
croko
 
Posts: 3
Joined: Tue Mar 12, 2019 10:09 pm

Re: macro to export data in multiple txt files

Postby JeJe » Wed Mar 13, 2019 12:05 am

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 507
Joined: Wed Mar 09, 2016 2:40 pm

Re: macro to export data in multiple txt files

Postby MrProgrammer » Wed Mar 13, 2019 12:46 am

Hi, and welcome to the forum.

croko wrote:I'm looking for a macro to run in OpenOffice in order to export some data from this file in 8 separate tab delimited .txt files, like this: …
Export the spreadsheet to a CSV file, then use Perl to create the TXT files. If you say "I don't know how to use Perl", well, you apparently don't know how to do it in OpenOffice either and this will be much easier in Perl than using an OpenOffice macro. Perl runs on all operating systems, has libraries to read CSV, and TXT files are simple to create. Perl can create multiple TXT files during program execution. If you structure the program properly, you can write a subroutine and call it eight times.

Or see: [Solved] Can Files Be Generated From Calc List?

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Wed Mar 13, 2019 2:12 am, edited 1 time in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3705
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: macro to export data in multiple txt files

Postby Sébastien C » Wed Mar 13, 2019 1:52 am

Hello every ones.

Indeed I say ++++++1000000 to MrProgrammer.

BUT, if I may, Perl is one of many others.

For example, I do not know Perl and I am just a (very, unfortunately) beginner with Python (which could be even more suitable than Perl, since it is included in the office suite).

But I know PHP.

  • And PHP CLI works with M$Win$10.
  • And PHP manage very well the strings and the text files too.
  • And you can call a script with the shell instruction of the Basic just at rthe right moment you want.
So, as says MrProgrammer, what it is complicate for the Basic can be delegate ton an appropriate language. Perl is one of them.
:D
LibreOffice 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: macro to export data in multiple txt files

Postby Villeroy » Wed Mar 13, 2019 10:09 am

You have a spreadsheet at hand. Use it. You can filter to other sheets, (re)move columns, use references. Writing the program takes more time than doing it manually on sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: macro to export data in multiple txt files

Postby Villeroy » Wed Mar 13, 2019 3:17 pm

OK, more explicitly:

Insert 8 new sheets

Select B1:ABxxxx (columns B through AB until last used row)
menu:Data>Define...
Give a name to the range.
Check option "range contains column labels"
Buttons [Add], [OK]

Click any cell in the defined database range
menu:Data>Filter>Standard...
< name of column T> greater than 0
[More Options], Copy Output to: SheetX.A1
[OK]
Remove unwanted columns
menu:File>SaveAs...
File type: Text (*.csv)
[X] Edit Filter Settings
Specify the details such as Tab separator, encoding, line breaks
This file type stores the current sheet only.

Repeat the above block for columns U,V,W etc. and the next empty sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests