[h2] Re: Exports data in Exel or ods

[t603]

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

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a964ced7-812e-4258-9797-0fd1e65b1819%40googlegroups.com.

--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