Not tested on Windows (no machine handly), here’s the code:
Set the Prefix_Path and Target_Prefix variables, as appropriate. Be sure there is no space in neither path and file names. I was fortunate ’cause by pure hazard, there were none in my test case.
Else, you have to quote the whole (path + file name).
Works fine on MacBook Pro m1 (02-2021), Monterey 12.5 and was created and running with Xojo 2021r2.1.
Have fun with it.
//
// How to use the shell to export as csv the contens of a sqlite file
//
// Tested with Xojo 2021r2.1 on a M1 MacBookPro / Monterey 12.5
//
// 2022-07-30, Emile Schwarz
//
Var DB_Dlg As New OpenFileDialog
Var DB_FI As FolderItem
// ********** ********** ********** ********** ********** ********** **********
//
// WARNING: the selected sqlite file MUST HAVE a TABLE called “Customers”
//
// ********** ********** ********** ********** ********** ********** **********
// Fills some ShowModal Properties
DB_Dlg.ActionButtonCaption = "Select"
DB_Dlg.Filter = FTG_DB.All // ".sqlite;.db"
DB_Dlg.Title = "Choose a sqlite file"
// Issue the Dialog
DB_FI = DB_Dlg.ShowModal
If DB_FI = Nil Then
// User cancelled
Return
End If
Var Shell_Cmd As String
Var s As New Shell
// Mandatory: Synchronous
s.ExecuteMode = Shell.ExecuteModes.Synchronous
// Build the file paths
Var Prefix_Path As String = "./Users/emile_schwarz/Downloads/Emile/"
Var Target_Prefix As String = DB_FI.Parent.NativePath
// Build the Shell_Cmd Command
Shell_Cmd = "sqlite3 -header -csv ./" + DB_FI.NativePath + " ""select * from Customers;"" > " + Target_Prefix + "/Customers.csv"
// Exceute the Shell_Cmd Command
s.Execute(Shell_Cmd)
// Report an eventual error
If s.ExitCode > 0 Then
MessageBox("Error code: " + s.ErrorCode.ToString)
End If
Here’s the resulting csv file as displayed by the Finder:
All Text Fields (Non ID / Integer) are quoted.