I create csv files in my app with list-to-csv. When I open it in a text-editor all the columns correctly have double quotes, however when I open it with Excel, Excel shows it as text, not columns, and the first item of each row has no double quotes. I think that's why Excel doesn't recognize it and doesn't create columns correctly.
The Excel/CSV reader of my phone does understand it and shows the columns correctly. I can import the data in a different way in Excel (data/from text/import) and then it's ok, but I want it to be ok when opening the csv file with Excel.
Someone else had this problem?
I don't use Excel, but it sounds like there is a csv format issue. Show an example of your csv data, and how it is built (relevant blocks)
You appear to have two different lists in your csv ?
no, these are just some parts of the code. On initialize I create the headers in a global.
Later on when getting the data I include the headers and the data to 'csv_data' and that goes to my save file function.
What happens if you leave out rows 2,3,4 & 5 ?
Actually, should only be rows 1 & 2 (1st list), or rows 4 then 6 onwards (2nd list)
I will try tomorrow. I'm too tired, spent all day trying to make file save work under Android 11. No success yet...
I didn't exactly understand which rows you meant, but I tried without the headers, only with the rows with the data in the first column.
Same issue, in texteditor it's okay, in Excel it's not, dates (in what should be the first column in the excel file) don't have double quotes.
Try removing all the quotes from your csv lists, you can do this using the replace block. See if that makes any difference:
1st List
Merk,Type,Kenteken,Periode
VW,Up,2-ABC-99,
AU,Dn,1-DEF-00,4
2nd List
Datum,Naam,Beginstand,...
06-01-2021,Paul Wolbers,,....
13-01-2021,Paul Wolbers,,...
Do not put these two lists in the same file, it will probably break the csv formatting.
I do not have Excel, but this splits to columns nicely in LibreCalc and Google Sheets
I already do the replace.
I have to put it in one file because that is the format the tax authorities want it.
Could the problem be that the headers have a different amount of columns? I will add empty values and try.
UPDATE:
I tried with empty values to also have 10 columns in the header, doesn't help.
Leaving out the empty rows also doesn' t help.
Then for some reason Excel is being dumb. Does it have to be Excel ?
You could always use a second sheet to format the data for the tax authorities, and the first sheet to just import the data.