Importing text files into Calc using macros

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
perseus
Posts: 2
Joined: Tue Oct 04, 2022 10:43 am

Importing text files into Calc using macros

Post by perseus »

I know there are several other threads similar to this, but although I've used calc spreadsheets before I've never written a macro before so I'm not sure I understand all the commands, and I'm not sure if calc is able to do this specific task anyway.

I have a large number of files in directory let's call it numbers
lets say the file names are file1, file2, file3........... up to a hundred different files
The text data in each file is in the following comma format

date1, number1, number2, number3, number4, number5
date2, number6, number7, number8, number9, number10
date3,......

and so on up to around 2000 lines but the number of lines in each file is variable

I wish to import the list of numbers into a spreadsheet, let's call it spreadsheet1 sheet1 starting in cell A3, so this would typically fill A3 to F2002 with the text.

The spreadsheet would then use these numbers to calculate an output in Sheet2. The filename of file1 is entered in sheet2 cell cell a1, and cells b1 to g1 are filled with the output calculations from the spreadsheet.

Once the calculations for the first file have been completed and entered in sheet2, the macro should move onto importing file2, starting in the same place as the file1 data, that is spreadsheet 1 sheet 1 cell A3 as before. It may be necessary to delete all the previous numbers from file 1 first since the input files have may different lengths, so I don't want part of the previous file left in!

The output this time is moved down one row filling a2 to g2 in sheet2

the process is repeated for each file, so for 100 files it would finish filling cells a101 to g101 in sheet with the output.

Note the spreadsheet is quite data intensive, it takes several minutes to load it on my new desktop. I only have calc v4 without any extras attached.
So far I've been doing this by hand!
open office version 4.1.12
windows 11
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Importing text files into Calc using macros

Post by JeJe »

As a start, have you tried the macro recorder for any of this?

If you break things down into little steps... which ones can be done with the recorder... and then for the others... do a search for how to do those here... and if you get stuck... ask for help.

Your post comes across as asking someone to do it all for you. [Edit: sometimes people do but its a teach people how to fish forum... not a fish on your plate one...]
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing text files into Calc using macros

Post by Villeroy »

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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Importing text files into Calc using macros

Post by Zizi64 »

I've never written a macro before so I'm not sure I understand all the commands, and I'm not sure if calc is able to do this specific task anyway.
Why you want to achieve this task by macros - when you not never written any macros? Start with a simplier task to study of the macro writing... Start with Andrew pitonyak' free macro books. https://www.pitonyak.org/oo.php

But YOU must learn about the thousands of the API functions, and the really simple StarBasic commands. (API: application Programming Interface)
Install one of the excellent Object Inspection Tools: XrayTool or MRI. Then you will able to list the properties and methods of the programming object of the API.
https://berma.pagesperso-orange.fr/index2.html
https://extensions.libreoffice.org/en/e ... ction-tool


You can be sure: the Calc will able to do this if YOU can write the appropriate macros.

Note: the Macro Recorder has a strongly limited capability. And it works in the Calc and Writer applications only. You must WRITE your macros (instead of recording them) if you want to work with macros efficiently.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing text files into Calc using macros

Post by Villeroy »

perseus wrote: Tue Oct 04, 2022 12:27 pm Once the calculations for the first file have been completed and entered in sheet2, the macro should move onto importing file2, starting in the same place as the file1 data, that is spreadsheet 1 sheet 1 cell A3 as before. It may be necessary to delete all the previous numbers from file 1 first since the input files have may different lengths, so I don't want part of the previous file left in!
My little macro suite keeps the information about the source file in the first column. It dumps everything into one list and then you may use any of the numerous aggregation features to perform your calculations for each individual source file. For instance you can add a pivot table to calculate sums, averages, etc. for each source file and/or any other category that can be derived from your data.
Old data will not be overwritten so they can be compared with later data.
New data are dumped into newly inserted rows. This way your calculation models, charts and any other references will include newly inserted rows.
There is also an option to move the processed files into a separate directory so they are separated from newly incoming files.
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
perseus
Posts: 2
Joined: Tue Oct 04, 2022 10:43 am

Re: Importing text files into Calc using macros

Post by perseus »

By being specific, I may have made the task sound more complex than it is. Essentially it is very simple. Read (Input) text files, one at a time into an already written spreadsheet, so it can perform a calculation on each files data. That's it! I don't need an output file, the answer will be contained in each line of the spreadsheet IF the 'output' moves down one line for each file so it doesn't rub out the previous data. That's what I tried to describe above.

What' I'm doing at present, is opening a csv text file, which OpenOffice recognises and opens as a spreadsheet, I then copy and paste around 1000-2000 lines into the main spreadsheet which carries out the calculations. This spreadsheet then takes a few minutes to calculate about 7 numbers. I then need to repeat the whole process for each file, but save the spreadsheet with a different name for each input file. Because of the number of files, and the time the spreadsheet takes to undertake the calculation, it's very time consuming and monotonous.

Unfortunately, I don't find the dialects of Visual Basic easy, & don't really want to go on a course for this one task. That said, by examining the code I could probably reverse engineer it and adapt it for a range of other purposes and get to understand the language more easily that way.

I really assumed this was so standard there must be something already written which does (almost) exactly what I asked for. I'll have a look through the one Villeroy posted, so thanks for that.
open office version 4.1.12
windows 11
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Importing text files into Calc using macros

Post by Zizi64 »

Unfortunately, I don't find the dialects of Visual Basic easy, & don't really want to go on a course for this one task.
The Visual basic (VB), the Visual Basic for Applications (VBA, what the Excel uses), and the StarBasic + API (of the Open/LibreOffice) are three basicly different things.

You can call of the API functions of the AOO/LO from every supported programming languages: python, Java, StarBasic and more...)

You must rewrite the VBA macros based on the API functions - if you want to work with the AOO/LO efficiently. Some VBA macros can run in the LibreOffice (without rewriting them) if you use the compatibility option.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Bidouille
Volunteer
Posts: 574
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: Importing text files into Calc using macros

Post by Bidouille »

perseus wrote: Wed Oct 05, 2022 9:06 pmthen copy and paste around 1000-2000 lines into the main spreadsheet
Maybe Base app will be more appropriate
Post Reply