Export a SQLite Data Base with a Multilines Text Field

Databases
1

I have a SQLite database with around 1,000 entries and the pro-bono user want (have to) export every records to use it with Excel (or a SpreadSheet: LibreOffice ?).

In a typical Record, there are child(s) in an entry: so many lines in a ListBox Cell.

I tried to see how it fits with Copy (from the ListBox) or Export to (.txt, .csv), but I get the original row Contents (Cells) until the Child Cell who appears multi-lines in the file.

I tried to create a multi-lines paragraph (read the code to understand) and import it to LibreOffice: everything goes to its lines: 6 Cells were filled…

LibreOffice, after I opened the .txt file

image
image460×436 13.8 KB

Xojo code to generate the .txt file:

Sub Action() Handles Action
  // ********** ********** ********** **********
  // 
  // Save multi lines text with LF
  //   and ends with an EndOfLine
  // 
  
  // Adapted from the documentation “Close” example:
  Var f As FolderItem
  Var t As TextOutputStream
  f = FolderItem.ShowSaveFileDialog("txt/raw","Essai lignes et paragraphe.txt") // Changed to Save…
  If f <> Nil Then
    t = TextOutputStream.Open(f)
    t.Write "Line 1" + Chr(10)
    t.Write "Line 2" + Chr(10)
    t.Write "Line 3" + Chr(10)
    t.Write "Line 4" + Chr(10)
    t.Write "Line 5" + Chr(10)
    t.Write "End of paragraph." + EndOfLine
    
    t.Close
  End If
End Sub

My question is:

Can I export each record with a cell that have more than one line as a Paragraph in a spreadsheet ?

Is-it clear ?

2

Another attempt. New code in a button:

Var Clip As New Clipboard
Var Paragraph As String

Paragraph = "Line 1" + Chr(10)
Paragraph = Paragraph + "Line 2" + Chr(10)
Paragraph = Paragraph + "Line 3" + Chr(10)
Paragraph = Paragraph + "Line 4" + Chr(10)
Paragraph = Paragraph + "Line 5" + Chr(10)

// Set the Clipboard Text Property
Clip.Text = Paragraph + "End of paragraph." + EndOfLine
Clip.Close
break

Results in the debugger:

image
image1342×612 140 KB

My LF [Chr(10)] is transformed to a CR [Chr(13)]: $0D.

Why ?

3

Apparently, when set to the Clipboard, $0A is changed to $0D…

Weird, even 2021r2.1 show that behavior !

PS: Paragraph holds the value I gave it: $0A (LF).

I replaced Chr(10) with EndOfLine.LF: same misbehavior !

4

use another char like chr(1) or chr(2) for record separator

or use real CVS files, by real I mean csv that can export text between quotes even if they have line ending between the quotes. then of course use a csv import that deals correctly with quotes and endoflines as well.

5

Merci Jean-Yves.

The .csv file contents below seems to work with LibreOffice:

"Line 1
Line 2
Line 3
Line 4
Line 5
End of paragraph.","Cell 2","Cell 3"
"Cell 1","Cell 2","Cell 3"

Now I have to implement an had hoc Export routine.