Exports data in Exel or ods

[Sigfried SkerrA]

Hi

A script starts every day and exports data from a H2 database (CSV).

CALL CSVWRITE('Y:\JVerein\Auswertung\Adressenexport4.csv', 'SELECT ANREDE,
TITEL, NAME, VORNAME, STRASSE, PLZ, ORT FROM PUBLIC.MITGLIED');

But I want to create an Excel file or ODS.
For many employees it is too difficult to open a CSV.

Can someone help me pleace

Siegfried

[t603]

1) Try to set up CSV OPTIONS (lineSeparator, fieldSeparator, fieldDelimiter are among the most important for this task) in CSVWRITE, so then You can doubleclick on CSV and it opens without any additional steps directly in MS Office. Works for me great! Can be used for just only one sheet and no formatting. 

2) When You need multiple sheets, formatting, even very complex one, You need to do some automatic post-processing. Try SpreadsheetML (Microsoft easy readable and writeable XML format, not the current XLSX which looks very similar to (X)HTML). The XSLT 3.0 and XPath 3.1 using Saxon 10 HE is my choice. But You have to be familiar with XSLT a XPath. 

Regards, Stepan 

[bocher]

Hi Siegfried,

You can try this library : https://github.com/miachm/SODS to write a custom function to export table in ODS file.

It works well.

Regards

Erwan

[Sigfried SkerrA]

Hi Stepan,
i am not an expert in java.
1) What does the new code look like in Java?
I can't find any help on the internet.
Attached my csv.
thanks in advance
Siegfried

[t603]

Siegried, 

for task 1 (exporting data from H2 into CSV into just one sheet with no formatting ready to open inside Excel by just doubleclick) You do not need any Java knowledge, just only knowledge of H2's SQL "csvOptions". 

You can see documentation of these options on https://www.h2database.com/html/grammar.html#csv_options 

I guess, it is an easy task, You have to modify (to add few options as the third string parameter of csvwrite) Your code to 

CALL CSVWRITE('Y:\JVerein\Auswertung\Adressenexport4.csv', 'SELECT ANREDE,TITEL, NAME, VORNAME, STRASSE, PLZ, ORT FROM PUBLIC.MITGLIED', 'charset=windows-1250 escape=" fieldDelimiter=" fieldSeparator=; writeColumnHeader=true');

This settings works for me (MS Excel in Czech settings). You can change these options according Your German Excel settings directly in Your SQL code. 

That is all! Stepan 

If You want to learn all the features of csvOptions, see below. Some of used options are ignored for writing or reading, see the link to the documentation above or just play with the code to see the result. I just kept them for uniformity of the code. 

--CREATE AND USE YOUR OWN SANDBOX SCHEMA NAMED E. G. T603
--drop schema if exists "T603";
create schema if not exists "T603";
set schema "T603";


--ISOLATED EXAMPLE OF READING OF CSV FILE WITH EXPLICIT DEFINED CSV OPTIONS
--LET PLAY WITH CSV OPTIONS USED IN CSV READING
--see "https://www.h2database.com/html/grammar.html#csv_options" for explanation of each option
set @caseSensitiveColumnNames = 'true';
set @charset = 'UTF-8';
set @escape = '';
set @fieldDelimiter = '';
set @fieldSeparator = ',';
set @lineComment = '--';
set @lineSeparator = char(13) || char(10);
set @nullValue = 'NULL';
set @preserveWhiteSpace = 'true';
set @writeColumnHeader = 'true';
set @csvOptions = concat_ws(' ', 'caseSensitiveColumnNames=' || @caseSensitiveColumnNames, 'charset=' || @charset, 'escape=' || @escape, 'fieldDelimiter=' || @fieldDelimiter, 'fieldSeparator=' || @fieldSeparator, 'lineComment=' || @lineComment, 'lineSeparator=' || @lineSeparator, 'null=' || @nullValue, 'preserveWhiteSpace=' || @preserveWhiteSpace, 'writeColumnHeader=' || @writeColumnHeader);
--select @csvOptions;
select * from csvread('C:\temp\temp\KC-Mitgliederadressen_INPUT.csv', null, @csvOptions);
--/ISOLATED EXAMPLE OF READING OF CSV FILE WITH EXPLICIT DEFINED CSV OPTIONS


--ISOLATED EXAMPLE OF READING AND WRITING OF CSV FILE WITH EXPLICIT DEFINED CSV OPTIONS DIFFERENT FOR READING AND WRITING
--LET PLAY WITH CSV OPTIONS USED IN CSV READING AGAIN
--see "https://www.h2database.com/html/grammar.html#csv_options" for explanation of each option
set @caseSensitiveColumnNames = 'true';
set @charset = 'UTF-8';
set @escape = '';
set @fieldDelimiter = '';
set @fieldSeparator = ',';
set @lineComment = '--';
set @lineSeparator = char(13) || char(10);
set @nullValue = 'NULL';
set @preserveWhiteSpace = 'true';
set @writeColumnHeader = 'true';
set @csvOptions = concat_ws(' ', 'caseSensitiveColumnNames=' || @caseSensitiveColumnNames, 'charset=' || @charset, 'escape=' || @escape, 'fieldDelimiter=' || @fieldDelimiter, 'fieldSeparator=' || @fieldSeparator, 'lineComment=' || @lineComment, 'lineSeparator=' || @lineSeparator, 'null=' || @nullValue, 'preserveWhiteSpace=' || @preserveWhiteSpace, 'writeColumnHeader=' || @writeColumnHeader);
--select @csvOptions;
--create table filled with data from Your CSV file
drop table if exists "T603"."MITGLIED";
create table if not exists "T603"."MITGLIED" as select * from csvread('C:\temp\temp\KC-Mitgliederadressen_INPUT.csv', null, @csvOptions);
select * from "T603"."MITGLIED";
--
--LET PLAY WITH CSV OPTIONS USED IN CSV WRITING
--csv options used for writing can be totally different from csv options used for csv reading
--see "https://www.h2database.com/html/grammar.html#csv_options" for explanation of each option
set @caseSensitiveColumnNames = 'true';
set @charset = 'windows-1250';
set @escape = '"';
set @fieldDelimiter = '"';
set @fieldSeparator = ';';
set @lineComment = '--';
set @lineSeparator = char(13) || char(10);
set @nullValue = 'NULL';
set @preserveWhiteSpace = 'true';
set @writeColumnHeader = 'true';
set @csvOptions = concat_ws(' ', 'caseSensitiveColumnNames=' || @caseSensitiveColumnNames, 'charset=' || @charset, 'escape=' || @escape, 'fieldDelimiter=' || @fieldDelimiter, 'fieldSeparator=' || @fieldSeparator, 'lineComment=' || @lineComment, 'lineSeparator=' || @lineSeparator, 'null=' || @nullValue, 'preserveWhiteSpace=' || @preserveWhiteSpace, 'writeColumnHeader=' || @writeColumnHeader);
--select @csvOptions;
call csvwrite('C:\temp\temp\KC-Mitgliederadressen_OUTPUT.csv', 'select * from "T603"."MITGLIED"', @csvOptions);
--/LET PLAY WITH CSV OPTIONS USED IN CSV WRITING
--/ISOLATED EXAMPLE OF READING AND WRITING OF CSV FILE WITH EXPLICIT DEFINED CSV OPTIONS DIFFERENT FOR READING AND WRITING

[t603]

Ooops, formatting of the source code cut my example. One can see attached file "sigi02.sql" with the long example.