[Solved] macro to export data in multiple txt files

[croko]

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!

[JeJe]

viewtopic.php?f=9&t=81035

viewtopic.php?f=9&t=82157

viewtopic.php?f=20&t=70528

[MrProgrammer]

Hi, and welcome to the forum.

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.

[Sébastien C]

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.

[Villeroy]

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.

[Villeroy]

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.