Catalog Confusion

[Tanjamuse]

Here's another version with the comments last.

[BetterRed]

I suspect the problems you perceive are caused by multi-line comments, here is the text for the test.csv shown in Notepad++ (I used a small font to eliminate word wrap)



And here is the same CSV in Excel 365 (2016) - with Autofit Row Height and Column Width set - which looks okay to me.



Having the Comments at the end made easier to 'see' what was happening.

The LF"LF at the end of the lines(records) is making all the cell heights taller. If you replaced them with "LF it may look better, because only true multl-line comments would increase cell height.

BR

[Tanjamuse]

Then why is it so much different when I open it? I still get one field with all those commas, and when there's multi-line comments they create multiple rows in my csv.

[BetterRed]

Which version of Excel?

[Tanjamuse]

I think it's 16, I can't afford the newer versions.

[BetterRed]

Version 16 is the latest Office

Office 2003 - version 11
Office 2007 - version 12
Office 2010 - version 14
Office 2013 - version 15
Office 2016 - version 16
Office 2019 - version 17 (will be)

I think I read MS made some improvements to Excel's CSV handling in Office 16. IIRC there are ways to force older versions of Excel to process LF terminators 'properly', but I can't recall details. Another avenue might be to load the CSV into Libre Office Calc and Save it as XLSX, and open that in Excel whatever.

BR

[Tanjamuse]

Ok, then. Why do I get the fields with all the commas? And come can I get rid of all the problems I have?

[BetterRed]

The attached ZIP has the CSV from post #16, with a corresponding XLSX.

If the XLSX is OK in your version of Excel then there's a good chance that reading the CSV's into Libre Calc and saving as XLSX should be OK too.

BR

[Tanjamuse]

I can open the .XLSX just fine in my excel.

[BetterRed]

Originally Posted by Tanjamuse

Ok, then. Why do I get the fields with all the commas? And come can I get rid of all the problems I have?

I think its because the Comments cells have commas and line-feed characters within the quotes. The line-feeds terminate the line and the commas are no longer seen as being within quotes and they somehow end up as column delimiters.

If you replaced all the ',' characters within Comments with for example the '#' character the problems would probably go away - but you probably wouldn't want to do that

BR

[BetterRed]

Originally Posted by Tanjamuse

I can open the .XLSX just fine in my excel.

Good.

Download and install Libre Office 6.0.6 from Download LibreOffice

Then open your CSV in its Calc program and Save it as an XLSX. Then load the XLSX into your Excel.

BR

[Tanjamuse]

That did the trick. I normally use csved to open csv-files. I really like all the options available.

[BetterRed]

Originally Posted by Tanjamuse

That did the trick.

Good.

Originally Posted by Tanjamuse

I normally use csved to open csv-files. I really like all the options available.

I don't know csved. I normally open csv files with csvfileview. But, because of the line-feeds and commas in comments, it was also 'confused' when it opened the csv's you posted.

You could try this: after opening the XLSX in Excel try saving it as a fresh CSV, and then try opening that CSV in csved.

BR

[Katja_hbg]

Perhaps this will help you as well:
I have to open csv files in my company in excel and get very strange views, while my colleagues get a proper structure. As we cannot find a better answer and we are working global, the assumption is that the different result is due to local settings. We did not find which but I found a workaround.

I use the same workaround for my calibre catalog to get rid of the extra rows in comments.

Here are the steps:
- open csv with notepad+, mark all and copy ctrl A/C
- open excel, paste ctrl V
- mark first column, data, text to columns, separate, by comma, finish (my excel is german, so I tried to find translation) = bad result is ok
- open new tab in same excel, paste again = oh wonder, perfect view

This works not after closing excel.

[Tanjamuse]

I'm going with the LibraOffice solution.

I've also considered creating a db-file like sqlite but I don't know how to

Another query which hasn't got anything to do with the catalog.

How do I add a custom column called lists to the following icon rule, so that it's both the lists and cloud column?

program:
    uc = user_categories();
    icon = test (field ('#cloud'), '', 'list_uninteresting.png');
    str_in_list(uc,',','Uninteresting',icon,'')