LibreOffice macro to export excel data to notepad

[Dreamer_14581]

Hi,

I am a newbie in macro / programming & need help to sort a task.

Following is the problem :-

let suppose i have multiple files in a directory with extension :- .xls, .ods, .xlsm, .xlsx
Each file contains data from column A2:B* ( Data varies from 10 to 20000 rows).

I have to open each file, do some formatting work in column A:B (i.e. replace, substitute, remove, add, subtract, divide) & finally concatenate values of both columns with a PIPE separator in column "D". After that, copy the result values from column "D" to notepad & save.

Want to automate the process of opening the file with above extension, do the calculation, copy the result data & save in notepad file with name of source file.

Need help in sorting this out with LibreOffice basics.

Thanks
Dreamer

[Villeroy]

Python comes with a csv module which can write DataArrays from sheet cell ranges. This way you could use a regular programming language with a bare minimum of macro programming.

[Dreamer_14581]

Hi,
Thanks for your reply!

As I mentioned, I am new in macro or programming.
Can you please guide me in detail.

Thanks
Dreamer

[Villeroy]

Learn programming.

[Dreamer_14581]

Thanks for your guidance.

Surely, will solve this.

[Villeroy]

We all solved it the hard way. You've got to learn first things first.

[JeJe]

Looks doable. A "notepad file" is just a text file.

If you break it down and do a search in this forum for the individual bits you want to do you may be able to get there.

macros to open the file, get the data, manipulate it, then create a text file and put the data in that.

There may be a thread or several on doing each of those...

[JohnSUN-Pensioner]

OK, I hope that this example help you to start

[Dreamer_14581]

Thanks JeJe & JohnSu for your reply.

Just needed some guidance & a little help to get started.

Thanks again and really appreciate your help !!! : )

Will get back to you if needed any help on this.

Thanks
Dreamer

[Dreamer_14581]

Cheers!

[Zizi64]

Code: Select all

Dispatcher.executeDispatch(Document, ".uno:SaveAs", "", 0, args1())
oDoc.storeToURL((sSaveToURL), Array(args1 (0),args1(1)))

Why you use the Dispatcher, if you know how to use the 'StoreTo...'; 'StoreAs...' direct API functions?


Try to RECORD a 'save as procedure'. Examine the recorded code. Then you will see the parameters that the Dispatcher requires.

[Dreamer_14581]

Hi ZiZi64,

Tried with the recorder also, but problem is still the same.
It seems something is missing, because first file is converted and saved the directory, but same error occur for the second file.

need your guidance & help.

Thanks
Dreamer

[Dreamer_14581]

@ZiZi64,

When i removed following line from the code, same worked for 2 files, and error occur for the third file.

'dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())

Please guide.

Thanks
Dreamer

[RoryOF]

You may need to work out if you giving distinct filenames to each document. Ask yourself how are the documents being named, and how is the name being changed for each new document,

You want the macro - you must do some work.

[Dreamer_14581]

@ZiZi64 &RoryOF

Thanks for your help and guidance, problem solved.
One step closer. Cheers !!!

Thanks
Dreamer

[Villeroy]

1) open the attached text document.
2) edit the yellow lines on top starting with g_ Strings can be wrapped in single quotes or double-quotes.

Code: Select all

g_Path = '/tmp/test/'

On Windows you set this variable to something like C:\path\with\back\slashes\

Code: Select all

g_Patterns = ('*.xls', '*.xls?', '*.ods')

is a list of shell patterns matching all *.xls files, files ending with .xls and some more letter and *.ods files.

Code: Select all

g_Log = 'faillog.txt'

is the name of a file which logs failed conversions

Code: Select all

g_ColumnSep = '|'

is the column separator for the csv files

Code: Select all

g_SheetIndex = 0

sheet index of the sheet to be converted. 0 refers to the first sheet.

3) push the [Install Script...] button which saves the text body to a special folder.

4) call Tools>Macros>Organize>Python... navigate to [My Macros]>[pyCalc]>[Sheets2CSV]>[export_csv] and hit the [Run] button.
alternatively: Tools>Macros>Run... [pyCalc]>[Sheets2CSV]>[export_csv] and hit the [Run] button.

The macro will convert a file named "myfile.ods" to "myfile.ods.Sheet1.csv" where "Sheet1" is the name of the converted sheet.

The macro makes use of Python's csv library and its Excel dialect. This csv dialect uses Windows line feeds, double-quotes as text separators and commas as column separator. The column separator is replaced by the one specified by variable g_ColumnSep

Any names of files that can not be converted, are written into the log file specified by variable g_Log.
Possible reasons:
-- Sheet does not exist if you specified a sheet index higher than 0
-- The file is corrupted
-- Calc can not open the file type
-- The file is password protected
-- Restrictions on file system level