Calc macro to open tab delimited data file?

users
#1

Hi all,

First of all, thanks to all for such a great product!

Second, I would like to write, record, or copy a macro for calc that opens a
tab delimited file and sets all the imported columns to be text format. I want
it to give me a file selection dialogue, but otherwise do everything else
automatically.

I know how to do this manually, and it isn't tricky, it is just that we have
LOTS of tab delimited text data files here at work, and it would awesome to be
able to push a button, choose a file, and voila.

Since I have already chosen not to have LO prompt if saving in non-ODT, this
would work seamlessly, saving me endless hours of fury at Excel for their stupid
"you will lose formatting" blather.

I can provide any more info if anyone would like.

P.S. -- I don't really want to get good at macro writing, so I am hoping for the
very quickest quick hack, rather than the 500 page introduction to LO Basic. No
judgements implied, just expediency.

Tx!

#2

Am 07.03.2012 00:35, fork wrote:

I can provide any more info if anyone would like.

A couple of lines of actual data copied from a text editor tell more than thousand words.

P.S. -- I don't really want to get good at macro writing, so I am hoping for the
very quickest quick hack, rather than the 500 page introduction to LO Basic. No
judgements implied, just expediency.

So you need someone who does the entire work for you which is impossible without seeing any data.

I use to suggest a configured solution rather than a programmed one.
Collect all your similar csv in one dedicated directory. The similar files should share the same file name suffix, delimiters and encoding.
Now you can read this directory of text files as if it were a collection of database tables. Database tables can be used seamlessly in Writer and Calc.

Database...

[X] Connect to existing database of type "Text"
[X] Register the new database
Save the new database which is just a configuration file
Close the new database and forget about the database for now.

In Calc:
1) Create a new file.
2) Hit F4 to get the data source window, select one of the tables _icons_ on the left and drag it into a cell.
3) Apply all the (conditional) cell styles, additional formulas, charts all the things why you want to use a calculator with text data.
4) menu:Data>Define... and pick the database "Import1" which represents the currently imported range of data, click "More Options" and check the 2nd and the 3rd option.
5) Save the file as a spreadsheet template.

Using the newly configured spreadsheet template:
1) File>New>Template...
2) Hit F4
3) Drag the table over the existing import range and confirm to replace the old data with the new data.

Alternatively, you can store a fixed document with an import range bound to one particular text file, say "CurrentData.csv". Check the additional 4th option for the import range so the data won't be stored with the document.
Usage: Replace the file CurrentData.csv with a new version, open the document, confirm to refresh the unsaved import range.

#3

Andreas Säger <villeroy <at> t-online.de> writes:

A couple of lines of actual data copied from a text editor tell more than
thousand words.

I don't really have any lines to show -- do you want screenshots or something?
I am hoping to develop a macro that opens files a certain way. There isn't
anything to show in a document.

> P.S. -- I don't really want to get good at macro writing, so I am hoping for
> the very quickest quick hack, rather than the 500 page introduction to LO
> Basic. No judgements implied, just expediency.

So you need someone who does the entire work for you which is impossible
without seeing any data.

You make me sound so lazy! (One of the three basic virtues of a programmer... :wink:
) Again, I don't know what data you might want to see.

I will do my best to hack a macro together and then post it and ask questions
around that -- specificity is always easier.

I use to suggest a configured solution rather than a programmed one. Collect
all your similar csv in one dedicated directory.

<SNIP>

This is a geat idea, but because of workflow here it won't work, though there
might be tidbits that help show the way to the macro I will write. I really
want a button to open text files in arbitrary places in the file system put
there by other people. Plus I think it would be generally useful.

Thanks for the thoughtful reply, though!

#4

All that "Inelligent Technology" is about data processing. Input > Processing -> Output. Without knowing anything about the input there is no way to write any program to process data.
Programmers are lazy because they know how to write routines to solve the same problem once for all times. You are not a programmer. You want others to write some program for you without the faintest specification.

I showed you a way how a user can configure this integrated office suite so a most simple drag&drop reads text data into Calc (and Writer btw). For anything else you can hire a lazy person.

#5

May be you did not notice that LibreOffice remembers the import settings for text files.
If you need to open the same type of file repeatedly, you can specify the import options once and next time you simply confirm the dialog.

#6

Andreas Säger <villeroy <at> t-online.de> writes:

All that "Inelligent Technology" is about data processing. Input >
Processing -> Output. Without knowing anything about the input there is
no way to write any program to process data.
Programmers are lazy because they know how to write routines to solve
the same problem once for all times. You are not a programmer. You want
others to write some program for you without the faintest specification.

I showed you a way how a user can configure this integrated office suite
so a most simple drag&drop reads text data into Calc (and Writer btw).
For anything else you can hire a lazy person.

Thanks for your unhelpful and rude response. I thought I spelled out my use
case and specification quite clearly (press button, choose file, automatically
load with all the columns set to text format), and asked what else might be
necessary to clarify. I would very much like to "write a routine to solve a
problem once for all times", but you are unable to help with that; that's ok,
but a little less rudeness would be nice.

#7

Andreas Säger <villeroy <at> t-online.de> writes:

May be you did not notice that LibreOffice remembers the import settings
for text files.
If you need to open the same type of file repeatedly, you can specify
the import options once and next time you simply confirm the dialog.

Yes, and that is great. The only steps I want to streamline are (1) the drop
down to get to "csv/text", and the step where I choose all columns to be text
format. And really, I would like to avoid confirming the dialogue.

It works fine the way it is, it is just that I open hundreds of these things a
day, and getting rid of 4 clicks per document would be a real time saver.

#8

Am 07.03.2012 19:24, fork wrote:

Andreas Säger<villeroy<at> t-online.de> writes:

May be you did not notice that LibreOffice remembers the import settings
for text files.
If you need to open the same type of file repeatedly, you can specify
the import options once and next time you simply confirm the dialog.

Yes, and that is great. The only steps I want to streamline are (1) the drop
down to get to "csv/text", and the step where I choose all columns to be text
format. And really, I would like to avoid confirming the dialogue.

It works fine the way it is, it is just that I open hundreds of these things a
day, and getting rid of 4 clicks per document would be a real time saver.

How many different keystrokes does it take to write a macro compared to the Enter key you are supposed to hit in order to confirm the dialog 10 times a day? 50 times?
Better you hit Enter a thousand times. My contingent of silly Basic code is exhausted for the rest of the week.

#9

Again, it is technically and logically impossible to write a program without knowing anything about the input.
If you can not copy a few lines of text from a text editor, then I will have to explain too many details anyway.
End of discussion.
Bye.

#10

A Tab delimited text file – what more could we want to know?
Some text ↹ More text ↹ More text again ↹ Text again, perhaps ↵
Another row with text, ↹ and ↵ and so on.
The macros could automatically detect how many rows and columns there
are, shouldn't be too difficult. I'd give it a try if I had some time
left. Sorry that I don't at the moment.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#11

Johnny Rosenberg <gurus.knugum <at> gmail.com> writes:

2012/3/7 Andreas Säger <villeroy <at> t-online.de>:
> Again, it is technically and logically impossible to write a program without
> knowing anything about the input.

A Tab delimited text file – what more could we want to know?
Some text ↹ More text ↹ More text again ↹ Text again, perhaps ↵
Another row with text, ↹ and ↵ and so on.

yeah, exactly. Oh, let me add "with a newline after Another row with text".

(Or perhaps: a tab delimited file with text in columns. Or maybe: a text file,
with columns separated by tabs and records separated by newlines. Or: A CSV
file, except with tabs instead of commas... I am a little bit surprised at the
level of drama my original specification elicited ...)

The macros could automatically detect how many rows and columns there
are, shouldn't be too difficult. I'd give it a try if I had some time
left. Sorry that I don't at the moment.

If you have time at some point, could you point me at the functions you would
think of first to do this? I tried to record a macro but all I got was an empty
Main function.

Kind regards

Likewise! (so refreshing...)

#12

Sorry, have you taken a look on internet to find something about.

http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=40585&p=186925&hilit=macro+import+text+file#p186925

Miguel Ángel

#13

MiguelAngel <mariosv <at> miguelangel.mobi> writes:

Sorry, have you taken a look on internet to find something about.

http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=40585&p=186925&hilit=macro+import+text+file#p186925

I saw this, but I don't need to insert part of a file into my document, but
rather open a document slightly differently.

I guess I am hoping for a Basic function that opens a document, except with a
bunch of parameters to customize the process (but I can't find such). Or maybe
invisibly open a document, set some attributes (for each column set format to
text, etc), then make visible.

Thanks for the tip, but I don't think the journey is over yet... :wink:

#14

Am 07.03.2012 22:03, Johnny Rosenberg wrote:

A Tab delimited text file – what more could we want to know?

4 different lines of tab delimited text each with 4 identical values:

2012/03/08 1,213.59 text 7-8
"2012-03-08" "1,213.59" "text" "7-8"
8.März12 01213,59 text 7-8
03/08/2012 $1213.59 "text" "7-8"
[...] to be continued with dozends of variants

The import dialog covers all these situations plus dozends of character encodings.

If you google for macros to load csv you will find dozends of ready
made solutions and all of them use a different set of FilterOptions. The FilterOptions are bundled in a complex string that reflects all the settings of the import dialog.

#15

Andreas Säger <villeroy <at> t-online.de> writes:

Am 07.03.2012 22:03, Johnny Rosenberg wrote:
>
> A Tab delimited text file – what more could we want to know?

4 different lines of tab delimited text each with 4 identical values:

2012/03/08 1,213.59 text 7-8
"2012-03-08" "1,213.59" "text" "7-8"
8.März12 01213,59 text 7-8
03/08/2012 $1213.59 "text" "7-8"
[...] to be continued with dozends of variants

Ahh -- I think the reason behind some of the confusion is becoming clearer... I
want to manipulate *dumb, uninterpreted* text. So "03/08/2012" would not be the
same thing as "2012-03-08", since both cases would be just a bunch of
meaningless characters, but different meaningless characters.

In this use case, I just want to rearrange columns or split or join columns.
For those times when I want to be able to interpret a date as a date and not
just a bunch of text, opening normally would be fine.

This is not a natural way to work for most spreadsheet users, but it is totally
natural for a subset of us data analysts (who might just want a visual
equivalent of the AWK programming language).

If you google for macros to load csv you will find dozends of ready
made solutions and all of them use a different set of FilterOptions. The
FilterOptions are bundled in a complex string that reflects all the
settings of the import dialog.
>

http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/OfficeDev/Handling_Documents#MediaDescriptor

> http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide/StarDesktop
>

http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options

That will be totally helpful, and I would not have found it on my own.

Tx

#16

Am 08.03.2012 01:48, fork wrote:

Andreas Säger<villeroy<at> t-online.de> writes:

Am 07.03.2012 22:03, Johnny Rosenberg wrote:

A Tab delimited text file – what more could we want to know?

4 different lines of tab delimited text each with 4 identical values:

2012/03/08 1,213.59 text 7-8
"2012-03-08" "1,213.59" "text" "7-8"
8.März12 01213,59 text 7-8
03/08/2012 $1213.59 "text" "7-8"
[...] to be continued with dozends of variants

Ahh -- I think the reason behind some of the confusion is becoming clearer... I
want to manipulate *dumb, uninterpreted* text. So "03/08/2012" would not be the
same thing as "2012-03-08", since both cases would be just a bunch of
meaningless characters, but different meaningless characters.

Then you do not want any spreadsheet at all. At least there is no technical reason to use this dinosaur software for plain text. This is covered by a tiny but very powerful text editor for text tables and Windows: http://csved.sjfrancke.nl/

#17

I believe that a sample solution can be found in AndrewBase.odt, search for the macro listing "Prompt for a CSV file, and then display the data."

#18

Am 08.03.2012 06:54, Andrew Douglas Pitonyak wrote:

I believe that a sample solution can be found in AndrewBase.odt, search
for the macro listing "Prompt for a CSV file, and then display the data."

Right, your AndrewBase.odt demonstrates how to load plain text database data into the database component, with or without macros.
In the Base tutorials of http://user.services.openoffice.org/en/forum.php we find tutorials and examples about loading plain text database data into 2 different types of databases without any macro code and this is what I suggested in the first place.
With the help of the built-in HSQLDB we can design your own csv editors for specific types of csv files. Once you have these data in Base, a drag&drop dumps them into any other component.

Your latest draft of your macro document http://www.pitonyak.org/OOME_3_0.odt deals with MediaDescriptor and FilterOptions providing a routine to display all arguments that had been used to load a file.

But the issue here is that "fork" insists in using one particular calculating tool called "spreadsheet" but in fact he hates using any tool at all which is why he nags people to write him a tool on top of the tool.

Today's even more blatant example of not using a software tool: http://www.oooforum.org/forum/viewtopic.phtml?t=145216
Finally he hired a coder because he can not apply simple formulas.

While "fork" is waiting for a lazy person who writes a csv import routine for tab separated files with unknown encoding, unknown text delimiters, suppressing evaluation for an unknown amount of columns, he may try a dedicated csv editor which helped so many spreadsheet non-users: http://csved.sjfrancke.nl/

#19

Andreas Säger <villeroy <at> t-online.de> writes:

But the issue here is that "fork" insists in using one particular
calculating tool called "spreadsheet" but in fact he hates using any
tool at all which is why he nags people to write him a tool on top of
the tool.

Andreas -- could I ask a favor? How about you ignore all my posts from now on?
I have been posting to newsgroups, as both a respondent and a questioner, for
10 years, and I have never encountered so much vitriol as I have from you. Just
pretend I don't exist, ok?

#20

Johnny Rosenberg <gurus.knugum <at> gmail.com> writes:

2012/3/7 Andreas Säger <villeroy <at> t-online.de>:
> Again, it is technically and logically impossible to write a program without
> knowing anything about the input.

A Tab delimited text file – what more could we want to know?
Some text ↹ More text ↹ More text again ↹ Text again, perhaps ↵
Another row with text, ↹ and ↵ and so on.

yeah, exactly.  Oh, let me add "with a newline after Another row with text".

Well, ↵ is a newline… :slight_smile: Well, it's the Enter key, but it produces a new line.

(Or perhaps: a tab delimited file with text in columns.  Or maybe:  a text file,
with columns separated by tabs and records separated by newlines.  Or: A CSV
file, except with tabs instead of commas... I am a little bit surprised at the
level of drama my original specification elicited ...)

The macros could automatically detect how many rows and columns there
are, shouldn't be too difficult. I'd give it a try if I had some time
left. Sorry that I don't at the moment.

If you have time at some point, could you point me at the functions you would
think of first to do this?  I tried to record a macro but all I got was an empty
Main function.

The macro recorder sucks, I never use it. There is a slightly better
one somewhere, but I never use that either.

Well, I am not sure at the moment, I am sure there will be a lot of
searching on the Internet for some suitable methods to use, and I
would probably be using ”xray” very frequently in order to create
something that works. I am not an expert in any way with macro
programming.

I think a macro can do everything that can be done manually and quite
a lot more, so I would probably try to find how to execute the CSV
import thing and let the macro set the values, but right now I don't
know how to do such a thing, but I'm convinced that it's possible (but
not 100% sure, of course).

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ