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 -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/137e5678-7d8e-4ed9-892d-3c7f75ec4b8c%40googlegroups.com.
