This is a follow up question to save a SQLite db contents into .csv file knowing some cells have Returns in the text.
The guy who needed that was not able to get the correct data in Excel.
A row is as follows:
“Text from a Cell” + \Tab + “Text with many lines” and so on.
That .csv file is loaded correctly in LibreOffice.
What can be done ?
In the mean time, I will check if Google Sheet load my .csv correctly.
I dumped MS Office a while ago, so this is from memory. Something arguably dangerous at my age. 
Excel on Windows can open a CSV file in two different ways. One is to simply double-click the file, or right click it and then select open with Excel. That method uses default csv parameters in Excel. It often fails, especially if cells can contain a separator character (by default, the list separator in the user’s regional parameters), for example. I suspect that your user went that route.
The second method is to start Excel, and then import the CSV file. In that process, you can define the separator as “tab”, and string delimiter as “, for example. That most often results in success opening the file. Now, if cells can contain the character “, then you must figure out which other character might be a good string delimiter, and define that when exporting data from SQLite. You, as the originator of the file, must provide the user with correct separator and delimiter specifications.
Hoping this helps.
LD
THANK YOU for your answer Louis.
Unfortunately, this option does not exists (I do not see it, the user do not found it).
For the quote character:
each cell content see all quotes removed (just in case the user type one; it is not required, but I remove them: I know what people can do),
each cell contents is surrounded with one quote
cell are tab separated (I checked yesterday before the tests).
Google Sheet testing:
my .csv generated with Xojo works fine, I exported it to .pdf AND to .xls.
Since the customer goes back to its desk, I sent him the two files.
It is incredible how many time we can waste whan in from of a 30 years of using computers (he looks like a newbie while using a computer, do not know how to use a keyboard / do not know what a PopupMenu - file type - is, “what is UTF-8 ?”)
Excel export replaced é with a , and tab with ;
This is Microsoft way of not following standards.
It certainly does. I used it. Manipulating csv files was quite common in my former life as an SAP consultant. Not sure any more where the import feature is, possibly in the Data section, but it does exist for sure. Oh, by the way, file encoding can also be defined in that import process. You can also set the default encoding to UTF-8 in Excel parameters.
Same sort of job, and I imported into Excel on a daily basis.
Go to the Data tab.
Click From Text/CSV in the “Get & Transform Data” group.
Select the file and click Import.
Review the data preview, then click Load to bring it into Excel or Transform Data to edit
To be honest I found it easier in older Excels, such as 2003 edition.
Here is a page about power query
I do found only Open in the File Menu (Windows as I do not have Excel in my Mac since… 45 years ago)…
Once (only), I saw a file type selection (*.txt I think it was the default) and this ~ßπîÌfißß machine opens the file when I click in its icon (instead of selecting it)…
That dialog never was displayed to me anymore even after I changed the file extension to .csv (it was by error .txt) /n after a reboot.
The customer call me to say he was able to load the .xls I created from google Sheet. So unless further bad news, the case seems closed.
For the future, if the same problem arises again.
Hi Emile!
I’m an ETL expert. Although I use tools for this specialty, Excel always gets in my way.
By default, I always use formats like the one you describe, with a TAB delimiter. It works very well in Excel.
The quotation marks in a file are very important. These quotation marks indicate that the data is a STRING.
To use it in Excel, you always need the tool open and to use FILE-OPEN-BROWSE. This forces Excel to use its “Text Import Wizard.”
Within the Wizard, use the “Delimited” option, look for it under “Choose the file type.”
When you click NEXT, it shows you the screen where you choose the delimiter. If it’s TAB, choose that; if you need a specific delimiter, use the “Other” option.
NEXT
Thats the screen I always used. Not sure about the Power Query malarky, but I didn’t adopt the subscription model of Office. I like my software locally, and paid for one time.
The foolish Windows user have its screen text size probablu at 10 px (Display at 50 % ?): the text are small and… I do not saw the Next button in the Text Import Wizard in the first screen.
Else, I would do like with LibreOffice (Step 2 / " above) and set Tab and Quote: this worked fine with LibreOffice.
I THANK YOU, José, once more for your explanation: VERY GOOD.
PS: I have my glasses, but I forget to take the magnifying glass for these cases.
I’m taking this opportunity to share some additional tools for reading and preparing data in the forum. These are for those who don’t have Excel or who frequently use delimited text.
These tools might be helpful to someone.
How about writing a simple application that converts your user’s strange CSV. File to a tab/ delimited file? Then import it. Solved a lot of issues with CSV import
Reminder:
a. The data comes from a SQLite data base generated by a specialized application created with (REALbasic, Real Studio, then Xojo),
b. I created a simple exportation application that export the Records with quotes set around each field texts, separated using Tab and ended with an EndOfFile, (*)
c. The process worked fine without quotes, excepted some fields have one to many lines (Return separated), so quotes are mandatory…
d. I opened a Google Sheet document and paste the whole file (around 1,000 Records), and exported the document into an .xls file.
Apparently, the user loaded that without troubles.
I was able to load correctly my file into LibreOffice Calc, and Numbers (by Apple).
At last, Excel is not my cup of tea, and this is what the stupid guys "appointed by the French State” required: an Excel file, so they can import that in their wed “application”.
(*) The application allows the export with a custom order (Order to export the Fields: Field 2,Field 1, Field 5, Field 7, etc. instead of the original order). This also allows to exclude fields if needed.
Also, I do not talked about how Excel export the data as this is not needed / to relevant to my question to the user quest.
Thank you for trying to help.
Bonjour,
“. The data comes from a SQLite data base generated by a specialized application created with (REALbasic, Real Studio, then Xojo),”
In that case, may I suggest that you modify the code to export to a TAB separated file, insted of a comma separated file. Excel and libre office can load that perfectly well. The only problem will be with your end-users needing to understand that they need to load from a tab-delimited file. (Or you could export from LibreOffice office to excel format yourself). It is also possible to write an application in Xojo that will export to Excel, but that takes a bit of work.
There’s one older format that’s actually the sweet spot for Xojo:
Excel 2003 XML / SpreadsheetML (.xml or .xls with XML content)
This is a plain single-file XML format that Excel recognises and opens natively as a proper spreadsheet. It predates .xlsxand avoids the ZIP-archive complexity entirely. Xojo can write it with just string/text output — no plugins needed.
A minimal example looks like:
xml
<?xml version="1.0"?>
Save it with a .xls extension and Excel opens it seamlessly — it even detects numeric vs string types if you tag them correctly.
The other older formats aren’t worth pursuing:
.xls (Excel 97–2003 binary) — binary format, very complex to write manually, no real advantage.slk — very old, limited, almost no practical use todayVerdict
SpreadsheetML is probably your best overall option for a pure-Xojo, no-plugin solution. It gives you:
Claude AI gave me a clean SpreadsheetMLExporter class plus a usage example:
Class: SpreadsheetMLExporter
Add a new Class to your project and name it SpreadsheetMLExporter. Add these two methods:
If rs = Nil Then
MessageBox(“SpreadsheetMLExporter: RowSet is Nil.”)
Return
End If
If outputFile = Nil Then
MessageBox(“SpreadsheetMLExporter: No output file specified.”)
Return
End If
If sheetName.Trim = “” Then
sheetName = “Sheet1”
End If
Var ts As TextOutputStream
ts = TextOutputStream.Create(outputFile)
ts.Encoding = Encodings.UTF8
// XML declaration and Workbook open
ts.WriteLine(“<?xml version=""1.0"" encoding=""UTF-8""?>”)
ts.WriteLine(“<?mso-application progid=""Excel.Sheet""?>”)
ts.WriteLine(“<Workbook xmlns=”“urn:schemas-microsoft-com:office:spreadsheet”“”)
ts.WriteLine(" xmlns:ss=““urn:schemas-microsoft-com:office:spreadsheet””>")
// Styles — bold header row
ts.WriteLine(“”)
ts.WriteLine(" <Style ss:ID=““Header””>“)
ts.WriteLine(” <Font ss:Bold=““1"”/>”)
ts.WriteLine(" “)
ts.WriteLine(”")
// Worksheet and Table open
ts.WriteLine(“<Worksheet ss:Name=”“” + XMLEscape(sheetName) + “”“>”)
ts.WriteLine(“
// Header row from column names
Var colCount As Integer = rs.ColumnCount
Var i As Integer
ts.WriteLine(“”)
For i = 0 To colCount - 1
Var colName As String = rs.ColumnAt(i).Name
ts.WriteLine(" <Cell ss:StyleID=““Header””><Data ss:Type=““String””>" + XMLEscape(colName) + “”)
Next
ts.WriteLine(“”)
// Data rows
Do Until rs.AfterLastRow
ts.WriteLine(“”)
For i = 0 To colCount - 1
Var cellVal As String = rs.ColumnAt(i).StringValue
Var cellType As String
Var outputVal As String
If cellVal.Trim <> “” And IsNumeric(cellVal) Then
cellType = “Number”
outputVal = cellVal
Else
cellType = “String”
outputVal = XMLEscape(cellVal)
End If
ts.WriteLine(" <Data ss:Type=“”" + cellType + “”“>” + outputVal + “”)
Next
ts.WriteLine(“”)
rs.MoveToNextRow
Loop
Method: Export Parameters: rs As RowSet, outputFile As FolderItem, sheetName As String
Here’s a clean SpreadsheetMLExporter class plus a usage example:
Class: SpreadsheetMLExporter
Add a new Class to your project and name it SpreadsheetMLExporter. Add these two methods:
Method: Export Parameters: rs As RowSet, outputFile As FolderItem, sheetName As String
xojo
If rs = Nil Then
MessageBox("SpreadsheetMLExporter: RowSet is Nil.")
Return
End If
If outputFile = Nil Then
MessageBox("SpreadsheetMLExporter: No output file specified.")
Return
End If
If sheetName.Trim = "" Then
sheetName = "Sheet1"
End If
Var ts As TextOutputStream
ts = TextOutputStream.Create(outputFile)
ts.Encoding = Encodings.UTF8
// XML declaration and Workbook open
ts.WriteLine("<?xml version=""1.0"" encoding=""UTF-8""?>")
ts.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
ts.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
ts.WriteLine(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
// Styles — bold header row
ts.WriteLine("<Styles>")
ts.WriteLine(" <Style ss:ID=""Header"">")
ts.WriteLine(" <Font ss:Bold=""1""/>")
ts.WriteLine(" </Style>")
ts.WriteLine("</Styles>")
// Worksheet and Table open
ts.WriteLine("<Worksheet ss:Name=""" + XMLEscape(sheetName) + """>")
ts.WriteLine("<Table>")
// Header row from column names
Var colCount As Integer = rs.ColumnCount
Var i As Integer
ts.WriteLine("<Row>")
For i = 0 To colCount - 1
Var colName As String = rs.ColumnAt(i).Name
ts.WriteLine(" <Cell ss:StyleID=""Header""><Data ss:Type=""String"">" + XMLEscape(colName) + "</Data></Cell>")
Next
ts.WriteLine("</Row>")
// Data rows
Do Until rs.AfterLastRow
ts.WriteLine("<Row>")
For i = 0 To colCount - 1
Var cellVal As String = rs.ColumnAt(i).StringValue
Var cellType As String
Var outputVal As String
If cellVal.Trim <> "" And IsNumeric(cellVal) Then
cellType = "Number"
outputVal = cellVal
Else
cellType = "String"
outputVal = XMLEscape(cellVal)
End If
ts.WriteLine(" <Cell><Data ss:Type=""" + cellType + """>" + outputVal + "</Data></Cell>")
Next
ts.WriteLine("</Row>")
rs.MoveToNextRow
Loop
// Close all tags
ts.WriteLine("</Table>")
ts.WriteLine("</Worksheet>")
ts.WriteLine("</Workbook>")
ts.Close
Method: XMLEscape (Private) Parameters: s As String Return type: String
xojo
s = s.ReplaceAll("&", "&")
s = s.ReplaceAll("<", "<")
s = s.ReplaceAll(">", ">")
s = s.ReplaceAll("""", """)
s = s.ReplaceAll("'", "'")
Return s
Usage — e.g. in a button’s Pressed handler
xojo
// Run your SQLite query
Var db As New SQLiteDatabase
db.DatabaseFile = GetDatabaseFolderItem() // however you get your db path
If Not db.Connect Then
MessageBox("Could not open database.")
Return
End If
Var rs As RowSet = db.SelectSQL("SELECT * FROM your_table")
If rs = Nil Then
MessageBox("Query returned no results.")
Return
End If
// Ask user where to save
Var dlg As New SaveDialog
dlg.InitialFileName = "Export.xls"
Var outputFile As FolderItem = dlg.ShowModal
If outputFile = Nil Then
Return // user cancelled
End If
// Export
Var exporter As New SpreadsheetMLExporter
exporter.Export(rs, outputFile, "Data")
rs.Close
db.Close
MessageBox("Export complete.")
---
**Notes:**
* Save the file with a **`.xls` extension** — Excel recognises the XML content and opens it natively with no import dialog
* Numeric detection uses Xojo's built-in `IsNumeric()` — integers and decimals are typed as `Number` in the XML, everything else as `String`
* The header row comes out **bold** automatically via the embedded Style
* You can add a second `Worksheet` block inside the Workbook if you want multiple sheets — just call the relevant query twice
* Dates from SQLite will come through as strings (SQLite stores them as text anyway); if you want Excel date formatting, let me know and I can add a date-detection branch
I haven't tried this, but you might like to explore it
Best regards,
Philsee below:
That is already the case. The fields are enclosed by quotes and separated by a Tab, and, of course the record is closed with an EndOfLine.
LibreOffice read correctly my generated file.
There are no quotes in the SQLite Data Base, but I remove any eventual before writing to disk.
At last, as everyone know, it is especially easy to add quotes and \TAB to the Record Line String before writing it to the file.
Here’s how looks a Record
“Field 1” \TAB “Field 2” \TAB “Field 3” \TAB “Field 4” \Return
until the end of the file.
(Return: EndOfLine in Xojo).
ALSO: I saved the file as .txt (by error), and changed that to .csv …
The Windows 10 of the user do not show the extensions, so it tooks me time to realize what happened.
NB: Excel export using a semi-colon, no quotes ! When I saw that, I do not tried to open that file with Excel. [I DO NOT NEED TO SAVE FROM EXCEL]
Here’s an example file as show by QuickLook:
I will meet the user probably on Sunday: my testing with Google Sheet apparently does the trick (it can export as .xls). The user says that, but tell “I printed it and study that in the week-end, last Friday”… go figure.
I do not have Excel to make tests by myself.
Not sure if the online version will work for you and your tests:
https://excel.cloud.microsoft/
