Excel export files require repair

[kerchner]

Opening every Excel export from SFM on my Mac consistently results in

The file can be repaired and seems to behave well after that, but we are probably not creating it correctly.

[kerchner]

@adityadharne you noted a problem with JSON exports, can you describe it here?

[adityadharne]

This the error I get:

[kerchner]

@adityadharne Which application are you using to open to JSON document?

[adityadharne]

@kerchner using Firefox with JSONView

[justinlittman]

That's because it is a line-oriented JSON file, which isn't valid JSON. Each of the lines is valid JSON.

[kerchner]

👍

[Tanych]

@kerchner could you send me the file that can't open?

[kerchner]

sent via email

[Tanych]

thanks!

[Tanych]

The detail of the issue for openpyxl is here , currently, there is no any better way to handle such edge cases. A simple solution for this is to prefix the value with a single quote. But, there might another edge case such as -test,'1/0' etc.

[Tanych]

For the openpyxl, if we set to the cell to TYPE_STRING, it also will check whether it stats with '=', if so, mark the cell as formula.

elif isinstance(value, STRING_TYPES):
            value = self.check_string(value)
            self.data_type = self.TYPE_STRING
            if len(value) > 1 and value.startswith("="):
                self.data_type = self.TYPE_FORMULA
            elif value in self.ERROR_CODES:
                self.data_type = self.TYPE_ERROR
            elif self.guess_types:
                value = self._infer_value(value)

Whether we should consider using xlsxwriter to write xlsx, it supports to disable convert string to formula.

I think the big difference between openpyxl and xlsxwriter is that openpyxl focuses on deal with xlsx files including loading, modify while xlsxwirter pays attention to write file as xlsx format.

https://xlsxwriter.readthedocs.io/workbook.html

[Tanych]

For this issue, adding a single quote before and after the text field. The excel can consider the text as a regular string not the formula. It might modify the data, but can avoid the open failure.

[kerchner]

Test after PR for #588 is merged

[Tanych]

@kerchner you can retest the formula issue for twitter export when PR gwu-libraries/sfm-twitter-harvester#30 and gwu-libraries/sfm-utils#27 are merged.
It can guarantee no problem when exporting to xlsx but not csv.

[justinlittman]

I tested and this works for xlsx, but not csv.