Hi,
I want to read a CSV file "loadedSheet.csv" using ES3Spreadsheet.GetCell, do some processing, and then save data to a different CSV file "savedSheet.csv" using ES3Spreadsheet.SetCell.
Subsequently, I import the "savedSheet.csv" into an inventory plugin ("Ultimate Inventory System") using its Importer.
The problem is that the Importer of UIS doesn't expect to find any entries in the CSV with quotation marks "", so the import fails.
By examining the files, I found out that even though the file being read "loadedSheet.csv" doesn't contain any entries with quotation marks "", ES3Spreadsheet.GetCell adds quotation marks "" when it reads entries that contain a space. Furthermore, ES3Spreadsheet.SetCell saves entries with quotation marks "" if they contain a space, even though they are new entries that were the outcome of processing.
Since CSV files have their own delimiters, e.g. commas, I believe that it's not necessary to add quotation marks "" to entries that contain a space by ES3Spreadsheet.GetCell and ES3Spreadsheet.SetCell.
Is there something I can do to fix this?
Thank you in advance!
ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Hi there,
All the best,
Joel
Please could you create a script which I can drop into a new scene in a new project which replicates this issue? I don't appear to be able to replicate this at my end.ES3Spreadsheet.GetCell adds quotation marks "" when it reads entries that contain a space.
Wrapping text values in quotation marks is part of the CSV specification, so this would be a limitation at their end rather than ours.The problem is that the Importer of UIS doesn't expect to find any entries in the CSV with quotation marks "", so the import fails.
All the best,
Joel
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Hi,
With further testing, it seems that the problem is not in reading the file but in saving it.
I have sent you a script and steps to reproduce the problem in your email.
With further testing, it seems that the problem is not in reading the file but in saving it.
I have sent you a script and steps to reproduce the problem in your email.
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Thanks for sending that over.
The file you have sent me is a valid CSV and the behaviour you're describing is adhering to the CSV specification.
If you go to any CSV validator (such as https://toolkitbay.com/tkb/tool/csv-validator) and put the file into the validator, you will see that the file created by Easy Save validates, and if you try to import it into any spreadsheet software then it will import correctly without error. We put any strings containing whitespace in quotation marks, because any leading or trailing whitespace not within quotation marks will be ignored (see this summary of the CSV specification). Specifically:
All the best,
Joel
The file you have sent me is a valid CSV and the behaviour you're describing is adhering to the CSV specification.
If you go to any CSV validator (such as https://toolkitbay.com/tkb/tool/csv-validator) and put the file into the validator, you will see that the file created by Easy Save validates, and if you try to import it into any spreadsheet software then it will import correctly without error. We put any strings containing whitespace in quotation marks, because any leading or trailing whitespace not within quotation marks will be ignored (see this summary of the CSV specification). Specifically:
If an asset is unable to read strings enclosed in double quotes then it indicates that their asset isn't adhering to the CSV specification, so this isn't something we would be able to account for at our end.Cells may be enclosed in double quotes; quoted values may contain linebreaks (or any other character); a double quote character within a quoted value is represented by two adjacent double quotes.
All the best,
Joel
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Thank you for your response.
I understand what you're saying, and why you're saying it.
However, I also tested, with the validator that you referenced in your response, the files without the quotes, i.e. the files as exported by the UIS asset, and also as saved by my custom CSV parser, which imports correctly in the UIS asset. In all cases, the result is "File is Valid".
If the results were INVALID, I'd say that the problem lies 100% with the UIS asset developer. But this is not the case.
I'm not sure what good it would do, to notify the UIS developer about the validity of the CSV file. I mean the CSV format they use is also valid.
I'm not at all proficient with the CSV specification, so I don't know how relevant to the issue this is, but I must say that I have noted that in your quoted text, it says: "Cells may be enclosed in double quotes;". My interpretation of "MAY be enclosed" is that it's not compulsory. If it were compulsory, it would say: "MUST be enclosed".
In conclusion, I cannot see why I should notify the UIS asset developer, especially since I solved the problem with my custom parser. However, I cannot see this issue in any other way than an ES3 limitation, i.e. ES3Spreadsheet seems to function correctly only with spreadsheets since the CSV files it outputs may not always function correctly with other CSV Readers/Writers. To put it simply, just because ES3 loads and saves CSV files, it doesn't mean that it will work as expected with non-spreadsheet CSV Readers/Writers.
EDIT:
You said: "We put any strings containing whitespace in quotation marks because any leading or trailing whitespace not within quotation marks will be ignored".
I made some simple tests to see how this works: I created an Excel sheet and entered a few values. Some of these values contained spaces between words, and some also contained leading or trailing whitespace. I saved these as a CSV file, which I then opened in a text editor (Notepad & Notepad++). All the values were delimited by commas and there were no quotation marks, with leading or trailing whitespace showing up properly between the commas.
So, this is what I expected to see when ES3 it saves a CSV file.
I understand what you're saying, and why you're saying it.
However, I also tested, with the validator that you referenced in your response, the files without the quotes, i.e. the files as exported by the UIS asset, and also as saved by my custom CSV parser, which imports correctly in the UIS asset. In all cases, the result is "File is Valid".
If the results were INVALID, I'd say that the problem lies 100% with the UIS asset developer. But this is not the case.
I'm not sure what good it would do, to notify the UIS developer about the validity of the CSV file. I mean the CSV format they use is also valid.
I'm not at all proficient with the CSV specification, so I don't know how relevant to the issue this is, but I must say that I have noted that in your quoted text, it says: "Cells may be enclosed in double quotes;". My interpretation of "MAY be enclosed" is that it's not compulsory. If it were compulsory, it would say: "MUST be enclosed".
In conclusion, I cannot see why I should notify the UIS asset developer, especially since I solved the problem with my custom parser. However, I cannot see this issue in any other way than an ES3 limitation, i.e. ES3Spreadsheet seems to function correctly only with spreadsheets since the CSV files it outputs may not always function correctly with other CSV Readers/Writers. To put it simply, just because ES3 loads and saves CSV files, it doesn't mean that it will work as expected with non-spreadsheet CSV Readers/Writers.
EDIT:
You said: "We put any strings containing whitespace in quotation marks because any leading or trailing whitespace not within quotation marks will be ignored".
I made some simple tests to see how this works: I created an Excel sheet and entered a few values. Some of these values contained spaces between words, and some also contained leading or trailing whitespace. I saved these as a CSV file, which I then opened in a text editor (Notepad & Notepad++). All the values were delimited by commas and there were no quotation marks, with leading or trailing whitespace showing up properly between the commas.
So, this is what I expected to see when ES3 it saves a CSV file.
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Hi there,
Quotation marks are also necessary in situations where strings contain commas, newlines or quotation marks, otherwise the CSV parser will read these as a piece of syntax. For example let's say that we had a CSV which contained items in a chat log, and one of those items in the chat log is:
If you would like I can make a specific version for you which only uses quotation marks if there is leading or trailing whitespace, but I would not be willing to put this in the public version of Easy Save as we do not wish to unnecessarily impact performance for 99% of users because an unrelated asset fails to parse necessary syntax as per the CSV specification.
Now if you put a comma, newline or quotation marks into a value and export it as CSV using Excel or Google Sheets, what happens?
Hope this helps explain the CSV specification and why we use quotation marks in the situations that we do.
All the best,
Joel
If their asset is unable to read a valid CSV file then it indicates an issue at their end. The CSV specification is the CSV specification: if they choose not to support quotation marks (which are a necessary part of the CSV specification to preserve whitespace and support specific characters within strings), then this means they do not adhere to the CSV specifications and their asset can't read CSV files; it can only read *some* CSV files.If the results were INVALID, I'd say that the problem lies 100% with the UIS asset developer. But this is not the case.
Just because the quotation marks aren't required all of the time, this doesn't mean that a CSV importer should assume that there will never be quotation marks. The CSV specification states that it should expect quotation marks:In conclusion, I cannot see why I should notify the UIS asset developer, especially since I solved the problem with my custom parser. However, I cannot see this issue in any other way than an ES3 limitation, i.e. ES3Spreadsheet seems to function correctly only with spreadsheets since the CSV files it outputs may not always function correctly with other CSV Readers/Writers. To put it simply, just because ES3 loads and saves CSV files, it doesn't mean that it will work as expected with non-spreadsheet CSV Readers/Writers.
And quotation marks are required in situations where it's necessary to preserve whitespace, for example in the same CSV specification document:Cells may be enclosed in double quotes; quoted values may contain linebreaks (or any other character); a double quote character within a quoted value is represented by two adjacent double quotes.
If the quotation marks weren't included, the whitespace would be lost. For this reason we include quotation marks if there is any whitespace for performance reasons (as it's more performance-affine to check for any whitespace, and we're a runtime serializer where performance is important). This adheres to the CSV specification which states that quotation marks can be used how the writer of CSVs intends, and that parsers should always expect data to be with or without quotation marks.Whitespace (space or tab) adjacent to a comma is ignored.
Quotation marks are also necessary in situations where strings contain commas, newlines or quotation marks, otherwise the CSV parser will read these as a piece of syntax. For example let's say that we had a CSV which contained items in a chat log, and one of those items in the chat log is:
What would you expect to happen here if this wasn't surrounded by quotation marks? The comma would be read as syntax and this would be considered two separate cells, which would break the entire CSV file. Or how about if it had a newline? A CSV parser must support quotation marks, and the asset you're speaking of doesn't.Hello, how are you?
If you would like I can make a specific version for you which only uses quotation marks if there is leading or trailing whitespace, but I would not be willing to put this in the public version of Easy Save as we do not wish to unnecessarily impact performance for 99% of users because an unrelated asset fails to parse necessary syntax as per the CSV specification.
Consumer spreadsheet software is generally relaxed when it comes to the whitespace rule because spreadsheet software has an unspoken agreement that they will parse all whitespace. However, Easy Save has to adhere strictly to the specification because our data is not just used by consumer spreadsheet software. For example if you were to put our data into something like SPSS or R (which is a common use case for our spreadsheet functionality), the whitespace would be lost as SPSS and R strictly adhere to the CSV specification.All the values were delimited by commas and there were no quotation marks, with leading or trailing whitespace showing up properly between the commas.
Now if you put a comma, newline or quotation marks into a value and export it as CSV using Excel or Google Sheets, what happens?
It surrounds them with quotation marks. Quotation marks are a necessary part of the CSV specification, so failing to support reading them is failing to support the CSV specification. Spreadsheet software can be more selective about what it chooses to wrap in quotation marks because performance isn't an issue as it's not collecting realtime data. However, Easy Save is a runtime serializer which could be collecting millions of rows of data per second (we have numerous customers doing this). Performance is important so we're less selective about what we surround in quotation marks, but we're still adhering to the CSV specification as it's flexible in this regard for this very reason."String with, comma","String with ""quotation marks""",String
Hope this helps explain the CSV specification and why we use quotation marks in the situations that we do.
All the best,
Joel
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Hi Joel,
Thank you for your very detailed response!
Admittedly, I was ready to fire more arguments back at you, up until you made the following points:
- we're a runtime serializer where performance is important, and we do not wish to unnecessarily impact performance for 99% of users
- Consumer spreadsheet software is generally relaxed when it comes to the whitespace rule
All my arguments would have been based on tests I made using Excel, which showed me what you said, i.e. that it is quite relaxed to the whitespace rule.
Also, my custom parser would take about 30 seconds to save 200 rows, whereas ES3 saves them instantaneously.
The main function of the "Ultimate Inventory System" is, of course, Inventory, and IMHO it's the best asset in the Asset Store for Inventory functionality. I mean, I rolled up my own Inventory system before purchasing it, so I know what it takes. It has been developed by one of the most respected and reliable companies in the Asset Store. The Import/Export functionality was added to UIS after a customer asked for it, and I believe that adhering to the CSV specification out of the box wasn't a priority.
I will contact their Customer Support, and give them a link to this discussion, which provides a lot of information regarding the CSV specification, in case they want to add more compatibility with it.
Thank you for your offer to make a specific version just for me, but there's no need. I'm covered with the custom parser I created. I had wrongly assumed that since the ES3Spreadsheet Class handles CSV files, it could also be used to feed data to the UIS Importer.
I also want to thank you for your time and effort in handling my support request!
Best Regards
Panos
Thank you for your very detailed response!
Admittedly, I was ready to fire more arguments back at you, up until you made the following points:
- we're a runtime serializer where performance is important, and we do not wish to unnecessarily impact performance for 99% of users
- Consumer spreadsheet software is generally relaxed when it comes to the whitespace rule
All my arguments would have been based on tests I made using Excel, which showed me what you said, i.e. that it is quite relaxed to the whitespace rule.
Also, my custom parser would take about 30 seconds to save 200 rows, whereas ES3 saves them instantaneously.
The main function of the "Ultimate Inventory System" is, of course, Inventory, and IMHO it's the best asset in the Asset Store for Inventory functionality. I mean, I rolled up my own Inventory system before purchasing it, so I know what it takes. It has been developed by one of the most respected and reliable companies in the Asset Store. The Import/Export functionality was added to UIS after a customer asked for it, and I believe that adhering to the CSV specification out of the box wasn't a priority.
I will contact their Customer Support, and give them a link to this discussion, which provides a lot of information regarding the CSV specification, in case they want to add more compatibility with it.
Thank you for your offer to make a specific version just for me, but there's no need. I'm covered with the custom parser I created. I had wrongly assumed that since the ES3Spreadsheet Class handles CSV files, it could also be used to feed data to the UIS Importer.
I also want to thank you for your time and effort in handling my support request!
Best Regards
Panos
Re: ES3Spreadsheet GetCell and SetCell Add "" to Data Containing Spaces
Glad to be of assistance Panos, let me know if there's anything else I can help you with 
All the best,
Joel

All the best,
Joel