Formating multiple identical files

Discuss the spreadsheet application
Post Reply
exfrog
Posts: 2
Joined: Tue Mar 22, 2011 6:20 pm

Formating multiple identical files

Post by exfrog »

I have a report generated as a .csv for every day for the last two years.
Every report has the same columns, and the first 15 rows are identical
I need to remove the same columns and rows from every .csv file.
What's the best way to do this? I could write an autohotkey script to do it, but it seems like too big of a hammer, and i'd think open office would have some ability to do this.
ideally, i'd like to keep the files seperate by day, so importing all the files into sheets on one document wouldnt work unless there is a subsequent save all sheets to individual files.

thanks in advance for any advice
OpenOffice 3.1 on Windows xp
andyroberts1234
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Formating multiple identical files

Post by andyroberts1234 »

You could easily use OO macros. A Basic macro for use on multiple files is http://user.services.openoffice.org/en/ ... 20&t=33247. A quick google/forum search should tell you how to delete columns using Basic (StarBasic code not Visual Basic).

To change from csv to ods use this solution http://user.services.openoffice.org/en/ ... 20&t=38587

HTH
Andy

OOO 3.3.0 / Windows 7 64-bit
exfrog
Posts: 2
Joined: Tue Mar 22, 2011 6:20 pm

Re: Formating multiple identical files

Post by exfrog »

I know html, css, a little javascript, but nothing about basic or starbasic

I wish i had a few hours to learn how to understand the file-selection macro you linked me, but it's way over my head.
And a quick search for deleting columns starbasic didnt return anything immediately useful either.

If anyone's got any additional help or links, it'd be appreciated.
OpenOffice 3.1 on Windows xp
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formating multiple identical files

Post by Villeroy »

csv is a database exchange format. Simply define the import settings in Base and use the resulting data in pre-formatted spreadsheets, Writer documents or database reports.
http://user.services.openoffice.org/en/ ... 19#p107619
This way you can link formatted ranges to text files, charts and dynamic formula fields to the formatted ranges.
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
andyroberts1234
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Formating multiple identical files

Post by andyroberts1234 »

I agree about using databases- is a steep learning curve though (I haven't yet used them much).

Quick google search for "delete column openoffice macro" found this code to remove/delete sheets.

Code: Select all

Sub delete2DE 
Dim oSheet as Object 
oSheet = ThisComponent.Sheets.getByIndex ( 1 ) 
oSheet.Columns.removeByIndex ( 3, 2 ) 
End Sub
Andy

OOO 3.3.0 / Windows 7 64-bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formating multiple identical files

Post by Villeroy »

andyroberts1234 wrote:I agree about using databases- is a steep learning curve though (I haven't yet used them much)
When you connect a Base document to a directory of similar csv files you do not use any database. The text files remain text files and the "database document" is just a configuration file to store the specific import settings so you can link or copy the tables in the most convenient way to Writer and Calc documents.
Additionally you may use queries (text allows only for very simple queries) in order to select rows and column in a defined order and/or filtered.
This is the most superficial way to use Base in order to feed data dynamically into your serial letters, your calculation models and charts.
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
andyroberts1234
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Formating multiple identical files

Post by andyroberts1234 »

True, connecting a Base document to a directory is in fact a very simple process (see description from Villeroy in the 11.49am post at the 2nd URL posted above) .

Whether you truly need a spreadsheet application or database all depends on whether you just need simple queries or more advanced calculations.
Andy

OOO 3.3.0 / Windows 7 64-bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formating multiple identical files

Post by Villeroy »

andyroberts1234 wrote:True, connecting a Base document to a directory is in fact a very simple process (see description from Villeroy in the 11.49am post at the 2nd URL posted above) .

Whether you truly need a spreadsheet application or database all depends on whether you just need simple queries or more advanced calculations.
You can combine both feature sets seamlessly. A simple query can import selected fields and selected records in a distinct order of rows and columns where you attach dynamically adjusting formula fields and charts to the import range. In a spreadsheet alone this would take a lot of macro code.
Disadvantages with Base's text driver: It can not import arbitrary flavours of csv as Calc 3.3 can do. The assumed number format locale should match the global application locale. If not, we get text at least. Since "nobody" uses Base for text import, the developers don't feel too much pressure to improve the text driver.
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
Post Reply