[CALC] Automatically save columns as text files

[willygroup]

Hi,
there is a way to automatically save the columns of a sheet as different text files?

Willy

Here an example

columns.ods

text_files.png

[mauricio]

When you say “automatic”, what is exactly?

An file with exactly you want, will help to help you.

[willygroup]

With a button or a menu item, something like that.

[mauricio]

With button not is automatic, but, if this it’s ok for you… only I need an example.

[willygroup]

I’ve attached a simple example of file.
Thanks

[mauricio]

I see your file, but, Only in your mind are how to needed export this information. A text file can be many different things.

[willygroup]

Ok, now there is an image (I cannot attach txt files!) with that I would like

[mauricio]

First version in Basic. Caution: If you have a few data, this code work fine, with many (5000+) rows, maybe run slow then we need refactory code.

Sub export_to_txt()
	
	path_target = "/home/mau/"
	
	doc = ThisComponent
	sheet = doc.CurrentController.ActiveSheet
	cell = sheet.getCellRangeByname("A1")
	
	cursor = sheet.createCursorByRange(cell)
	cursor.collapseToCurrentRegion()
	data = cursor.DataArray
		
	For c = 0 To UBound(data(0))
		ff = FreeFile()
		path = path_target & data(0)(c) & ".txt"
		Open path For Output As ff
			Print #ff, data(0)(c)
			For r = 1 To UBound(data)
				Print #ff, data(r)(c)
			Next r
		Close #ff
	Next c
	
End Sub

Version Python, more simple:

import uno


def main():

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cursor = sheet.createCursorByRange(sheet['A1'])
    cursor.collapseToCurrentRegion()
    data = cursor.DataArray

    for c in range(len(data[0])):
        values = [r[c] for r in data]
        path = '/home/mau/{}.txt'.format(values[0])
        with open(path, 'w') as f:
            f.write('\n'.join(values))

    return

Version using my library easymacro

import easymacro as app

def main():

    data = app.get_cell('A1').current_region.data
    for c in range(len(data[0])):
        values = [r[c] for r in data]
        path = '/home/mau/{}.txt'.format(values[0])
        app.save_file(path, data='\n'.join(values))

    return

[willygroup]

Thanks a lot!!

Python & LibreOffice? I’ve discovered a new wonderful world

I’ve used the first python macro, a couple of adjustments and it works like a charm!

I quickly tried the second version, but I couldn’t get it to work (probably I haven’t understood where to put easymacro.py ).

I think there is also an error here in the doc: https://gitlab.com/mauriciobaeza/zaz/-/wikis/easymacro.py

I think a final colon ‘:’ is missing here:

def test_easymacro()

[mauricio]

Thanks, I fixed it.

[sealjosephson]

hey mauricio, i copy-pasted it and it shows an I/O error in line 16. can you please advice?

[GaXve]

After multiple attempts, I noticed I got the same error when the name of the Files created had Characters that were… “not supported by Basic” I suppose? When there were “non-supported characters”, would be another way of saying it. Changing the contents of the first cells of each column to make sure they only contain standard Latin characters solved my issue (my knowledge of Basic, being it an older programming language, leads me to assume that only ASCii Characters are supported. Meaning @, !, etc. characters still work.)

There seems to also be a limitation with the contents of each txt File. When I had recent Unicode characters (⦰) or Emojis (), they would show up as a question mark in the txt Files… Making this tool have a very limited use for me, unfortunately.

[karolus]

@GaXve

this tool have a very limited use for me

from pathlib import Path

def main(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cursor = sheet.createCursorByRange( sheet['A1'])
    cursor.collapseToCurrentRegion()
    data = cursor.DataArray
    for column in zip(*data):
        file_name, *column = column
        file_path = Path.home() / f"{file_name}.txt"
        with file_path.open('w', encoding="utf8") as txt_file:
            txt_file.write('\n'.join(f'{entry}' for entry in column if entry != ''))

[mikekaganski]

@GaXve What was a specific function call that failed to save a Unicode filename?

[Lupp]

I didn’t study the previous solution in detail, but made an example file (based on the very old upload) now containing a little package of Basic routines and a sheet with explanations.
Since the filter options for csv are somehow unhandy, I did the final writing of csv files using hidden helper documents and the dispatcher. Helper documents are cheap.
disask5039014CsvFilePerColumn.ods (16.8 KB)
The solution is efficient even with columns containing lots of data.

.