csv filter and FilterOptions

Java, C++, C#, Delphi, ??? - Using the UNO bridges

csv filter and FilterOptions

Postby kovi » Fri Jan 09, 2009 5:14 pm

Hi!

I'm importing a CSV file into Calc using Java and it goes ok.
The problem I have is that I cannot find any good document on how to specify correct FilterOptions for "ext - txt - csv (StarCalc)" FilterName.
Now I have:
59,34,0,1,1/1/2/3/3/1 which means:
59 = columns delimiter by #
34 = text surrounded by double quotes
0 = System character set
1 = number of first line
1/1/2/3/3/1 = first column of type Standard, second column of type date (DD.MM.YY), third column of type Standard

I have come up with this format looking at http://api.openoffice.org/docs/Develope ... 1_Overview and chapter "Filter Options for the CSV Filter"m but there are only 10 formats described. Where can I see the whole thing? For example, how to define that a file is in UTF-8 character set, how to specify that a column has format Date (DD.MM.YYYY), ....
kovi
 
Posts: 6
Joined: Fri Jan 09, 2009 2:26 pm

Re: csv filter and FilterOptions

Postby Villeroy » Fri Jan 09, 2009 8:04 pm

DD.MM.YY falls into category DMY (3 groups of digits, separated by any non-digit).
Open the csv manually, specify every detail.
Then run the following macro. It shows the used filter options in an input box, so you can copy the string and use it in your own code.
Code: Select all   Expand viewCollapse view
Sub showFilterOptions
Dim args(),i%
   args() = thisComponent.getArgs
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
   next
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: csv filter and FilterOptions

Postby kovi » Mon Jan 12, 2009 12:21 pm

The problem I have is that the CVS files that I need to open do not have the same format.
I'm getting these CSV files as a result of an SQL query.
And because of that I have to specify FilterOptions dynamically.
So:
- if a column is of type Date then format it as DD.MM.YYYY
- if a column is of type DECIMAL then format it as #.##0,00 (Number)
- if a column is of type DECIMAL and negative then format it as 0,00;[RED]-0,00
- and so on

This is why I need to know how to specify different formats. In GUI that is opened when I open a CSV file in OpenOffice.org does not have that capability. Or does it ? :)
OOo 3.0.X on Ubuntu 8.x
kovi
 
Posts: 6
Joined: Fri Jan 09, 2009 2:26 pm

Re: csv filter and FilterOptions

Postby Villeroy » Mon Jan 12, 2009 1:17 pm

No, it's a spreadsheet application with a customizable csv filter. There is no option to store named import formats for this particular file format.
You can store FilterOptions with linked sheets. See [Tutorial] External Links In Calc "Chapter 2. Linked Sheet From File"
You can write a "csv-suite for Calc" which organizes several settings in a configuration tree or something.
You try using databases linked to csv-directories where all files in the same directory share the same import settings. See chapter 5 of the linked tutorial "Import From Registered Datasources".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: csv filter and FilterOptions

Postby kovi » Mon Jan 12, 2009 2:41 pm

I thiink I didn't explain correctly.
I have a server/client type of an application. both are writter in Java and they exchange data via RMI.
Client says to server "Display result of this SQL query in OpenOffice.org Calc". Server runs that SQL query against a relational database (DB2 in my case) and the result of this is a CSV file that is returned to the client. The client then imports this CSV file into Calc, again via Java.
I just want to display content of CSV file in Calc so that dates are dates, numbers are numbers, ....
OOo 3.0.X on Ubuntu 8.x
kovi
 
Posts: 6
Joined: Fri Jan 09, 2009 2:26 pm

Re: csv filter and FilterOptions

Postby Villeroy » Mon Jan 12, 2009 3:16 pm

Yes, you can exchange the dumped csv with another one and refresh the linked in menu:Edit>Links...[Update].
You can also exchange the csv in a text-database directory and refresh Calc's import ranges.
But why using csv anyway if OOo has a database component powerful enough to access data by means of ODBJ or JDBC? You could use those data seemlessly in any Wrriter or Calc document.

menu:File>New>Database...
[X] Connect to existing database
Type: ODBC
specify ODBC datasource name.
[X] Register database (so you can use it in arbitrary OOo documents)
Store the database which contains nothing but connection settings so far.
Add (parameter-) queries, forms and reports if you like.

[Tutorial] Using registered data sources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: csv filter and FilterOptions

Postby kovi » Mon Jan 12, 2009 3:47 pm

Usually I cannot access database on the server side directly from a client because network ports between client and server are closed.
OOo 3.0.X on Ubuntu 8.x
kovi
 
Posts: 6
Joined: Fri Jan 09, 2009 2:26 pm

Re: csv filter and FilterOptions

Postby Villeroy » Mon Jan 12, 2009 4:24 pm

So change the server policy or define you own standards for csv.
Hint: comma or tab as column separator, dot as decimal separator, ISO dates YYYY-MM-DD and all text (particulary numeric text) in double-quotes.

This should work with the "Standard" column type of any language unless your OOo application locale is set to a language like German or French where comma is the decimal separator (menu:Tools>Options...LangSettings>Languages: Locale).

Adjust the csv decimal separator and column separator to the OOo locale or vice versa.

Nother exception to the rule: "HH:MM:SS" seems to work as universal time format except for Italian OOo-locale where times are written like "HH.MM.SS".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest