Later edit: I manage to write this macro after digging this forum, found some code and learn how to modify it to make what I need. Thanks guys!
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!
[Solved] macro to export data in multiple txt files
[Solved] macro to export data in multiple txt files
Last edited by croko on Tue Jun 18, 2019 12:51 pm, edited 2 times in total.
LibreOffice 6.x on Windows 10
Re: macro to export data in multiple txt files
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: macro to export data in multiple txt files
Hi, and welcome to the forum.
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.
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.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: …
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.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
- Sébastien C
- Posts: 111
- Joined: Mon Jan 04, 2010 5:06 pm
- Location: Meymac, France
Re: macro to export data in multiple txt files
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.
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.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 .
Re: macro to export data in multiple txt files
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: macro to export data in multiple txt files
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice