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.
