OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc, LotusScript, CSV Import on Linux AND Windows

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
anonymous2009
Newbie
Newbie


Joined: 26 Dec 2009
Posts: 4

PostPosted: Sun Dec 27, 2009 5:12 am    Post subject: Calc, LotusScript, CSV Import on Linux AND Windows Reply with quote

Folks, total newb to this Forum ... Holiday greetings.

I have been googling around, here and elsewhere and would like some help (or being pointed in the right direction) on this one. Thanks in advance for any information.

o How can I leverage Calc from LotusScript (on Linux or Windows) in order to import CSV files into Notes?
o I would like to be able to do it from Linux or Windows, Linux being more important
o I would like to be able to do it from the Domino Server or the Notes Client, Domino Server being more important
o The files may be CSV or TSV and I need to determine which they are for a proper import.
o What version of Notes/Domino is a minimum to accomplish the integration?

I would greatly appreciate ANY suggestions, or being pointed in the right direction (link, book, etc.).


PS, I really have been looking, here are just some of the links I have stumbled on, but I haven't been able to stitch it all together yet, forgive my moment of density:

o How to use OO API from Lotusscript (Notes Domino plateform) - Note that this was unanswered and the code he provided was an Export he found

o Using Open Office in lotusscript

o Lotus Notes Lotusscript and OpenOffice... (OLE Stuff)

o CSV Import with a Twist (Solved)
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Dec 27, 2009 9:36 am    Post subject: Reply with quote

IBM offers Lotus Notes as well as Symphony which is a ODF capable office suite. Reportedly, there is some integration between both packages.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anonymous2009
Newbie
Newbie


Joined: 26 Dec 2009
Posts: 4

PostPosted: Sun Dec 27, 2009 9:46 am    Post subject: Reply with quote

Villeroy wrote:
IBM offers Lotus Notes as well as Symphony which is a ODF capable office suite. Reportedly, there is some integration between both packages.


Villeroy, thank you for the response. As crazy as it may sound, I am looking for the integration via an Open Source office suite. As far as I know, Symphony/SmartSuite/whatever it may be called now, is not Open Source, right?

Also, ODF is not really important for my purpose as I simply want to use the outstanding CSV/TSV parsing capability that these spreadsheet programs have in order to get the info into Notes/Domino.

I contemplated using Perl to get the CSV into Notes, but it seems that the two choices that way are via OLE and ODBC (my knowledge may be a bit dated on this) ... which makes the Linux support a bit dicy. (I like Perl because it is fast, cross-platform, and has extensive libraries available).

Calc (and most any spreadsheet) have been doing this CSV stuff ... forever ... and seem to get the import correct 99% of the time; that is how I have arrived at trying to do it this way.

I would appreciate any additional thoughts/suggestions. Thank you
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Dec 27, 2009 10:18 am    Post subject: Reply with quote

Symphony is based on old OOo 1. It produces ODF documents which can be used with any other ODF capable application.
Instead of the Base component it comes with some IBM-specific integration tools.
Csv is for database exchange when there is no other way to access the database directly, right?
Where does the csv come from? Lotus Notes, right?
You want to import Notes data into Calc via csv, right?
Do you need to write back data into the other direction?

Are you aware about the Base module? It is a mere frontend tool. It's very first purpose is to "serve" row sets coming from various sources to office documents.
Lotus Notes can be accessed via ODBC, so you may be able to live without csv.
[Notes]-->[ODBC]-->[Base]-->[Office docs, Calc, Charts, Writer, Labels, PDF...] and possibly input forms to write back if the database driver allows to do so.
Additionally, Base provides configuration settings to read various flavours of text tables.
[CSV directory]-->[Base config]-->[Office docs, Calc, Charts, Writer, Labels, PDF...] but read-only in one direction.

Quote:
Also, ODF is not really important for my purpose as I simply want to use the outstanding CSV/TSV parsing capability that these spreadsheet programs have in order to get the info into Notes/Domino.

Calc has no outstanding parsing options. You need to carefully specify a lot of options for every text file you open, otherwise you get simply wrong values. Then all numeric values are imported into a plain, unformatted spreadsheet, the values are displayed according the current locale defaults of that vanilla file.
You can write scripts in various languages to automate the csv import into Calc, but why? Base has far better import options for most text tables. Without a single line of code it allows to import pre-parsed row sets from text tables into preformatted spreadsheets with formulas, scenarios, charts, cell styles and everything.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anonymous2009
Newbie
Newbie


Joined: 26 Dec 2009
Posts: 4

PostPosted: Sun Dec 27, 2009 4:20 pm    Post subject: Reply with quote

Whoa! Villeroy thanks so much for the quick replies! Some interesting info here. I think I may not have been clear enough.

1) I receive numerous CSV + TSV files via Email
2) I want to just get their data to populate a Notes database
3) From Notes I plan to do other stuff with it (i.e., distribute to other (web) environments, etc.)
4) So I don't really need/want it in a spreasheet, but just wanted to utilize their CSV/TSV parsing engines without having to stitch one together via Perl (or Notes)
5) I want it to be fast, cross-platform, reliable so that I can completely automate this process ... and trust it
6) I do know, based on source (i.e., email address), exactly what format (CSV, TSV, Quoted/Unquoted) the various datasets are in so that I can I tell whatever engine I use how to handle it.

Why I am bothering with all of this?

Well, obviously the data is important, but also been pulling my hair out because of various nuances ... i.e.,
1) Files emanate from Linux OS so they have \r but no \n -- important if I run the process on Windows
2) Occasional character set "issues"
3) I want it to just work ... no matter which of the OS's I put it on (I have some old LotusScript code that I can use for dealing with the file naming differences between Linux & Windows)
4) I have been using Calc to manually parse/preview the imports and it has been spot-on every time -- much better than anything else programmatically that I have tried so far
5) I would most want Domino to control it ... but ultimately some form of LAMP may eventually become the controller ... so that it would be a completely Open Source solution
6) I want a "cheap" solution (as Open Source as possible)
7) PS, records are in the 10's of thousands per day

Summarizing:

numerous CSV/TSV in various formats --> (Some Parser) --> Notes/Domino
No real need to go the other direction

Does this still make sense? Or am I in the wrong forest? (Let alone barking up the wrong tree?)

Villeroy wrote:
Symphony is based on old OOo 1. It produces ODF documents which can be used with any other ODF capable application.
Instead of the Base component it comes with some IBM-specific integration tools.
Csv is for database exchange when there is no other way to access the database directly, right?
Where does the csv come from? Lotus Notes, right?
You want to import Notes data into Calc via csv, right?
Do you need to write back data into the other direction?

Are you aware about the Base module? It is a mere frontend tool. It's very first purpose is to "serve" row sets coming from various sources to office documents.
Lotus Notes can be accessed via ODBC, so you may be able to live without csv.
[Notes]-->[ODBC]-->[Base]-->[Office docs, Calc, Charts, Writer, Labels, PDF...] and possibly input forms to write back if the database driver allows to do so.
Additionally, Base provides configuration settings to read various flavours of text tables.
[CSV directory]-->[Base config]-->[Office docs, Calc, Charts, Writer, Labels, PDF...] but read-only in one direction.

Quote:
Also, ODF is not really important for my purpose as I simply want to use the outstanding CSV/TSV parsing capability that these spreadsheet programs have in order to get the info into Notes/Domino.

Calc has no outstanding parsing options. You need to carefully specify a lot of options for every text file you open, otherwise you get simply wrong values. Then all numeric values are imported into a plain, unformatted spreadsheet, the values are displayed according the current locale defaults of that vanilla file.
You can write scripts in various languages to automate the csv import into Calc, but why? Base has far better import options for most text tables. Without a single line of code it allows to import pre-parsed row sets from text tables into preformatted spreadsheets with formulas, scenarios, charts, cell styles and everything.
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Dec 27, 2009 4:53 pm    Post subject: Reply with quote

OOo is an office suite. It can not do anything you want it to do.
It can not detect nor parse different flavours of csv automatically. All internal csv-parsers (text import, database driver) work with parameters provided by the user (user may be a macro script).
IMHO, it makes no sense to install a 350MB GUI application to parse text files to be used in a completely different software.
Parsing csv, nothing comes close to a true programming language like Perl.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anonymous2009
Newbie
Newbie


Joined: 26 Dec 2009
Posts: 4

PostPosted: Sun Dec 27, 2009 5:02 pm    Post subject: Reply with quote

Villeroy wrote:
OOo is an office suite. It can not do anything you want it to do.
It can not detect nor parse different flavours of csv automatically. All internal csv-parsers (text import, database driver) work with parameters provided by the user (user may be a macro script).
IMHO, it makes no sense to install a 350MB GUI application to parse text files to be used in a completely different software.
Parsing csv, nothing comes close to a true programming language like Perl.


As I mentioned, Perl is my first choice, but the two pathways (that I know of) for Perl --> Notes is OLE and ODBC; both a bit sketchy on Linux, wouldn't you agree?
Is there another method you may be aware of?
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group