Bug 37512

Summary:	FORMATTING - Importing CSV files appends apostrophe to numbers.
Product:	LibreOffice	Reporter:	Alan C Whiteman <acwhiteman>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED WONTFIX
Severity:	major	CC:	acwhiteman, LibreOffice
Priority:	medium
Version:	3.3.2 release
Hardware:	x86 (IA32)
OS:	Linux (All)
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	CSV file with numerical values

Description Alan C Whiteman 2011-05-23 10:19:31 UTC

When importing numerical data from a CSV text file, with comma separated values, the numbers will have an apostrophe prefixed in the cell.

The value 123 will result as '123 in the imported cell, which will break formulas.

Changing the default localization settings does not work. As such, any numerical values imported from CSV will have to be fixed manually by removing the apostrophe.

Found this solution on the web:

1. Highlight affected column
2. Select 'Data' -> 'Text to Columns'
3. Select Text delimiter as " ' " in the dialog.
4. Click OK.

Comment 1 Petr Mladek 2011-05-23 11:57:49 UTC

Alan, could you please attach a test document?

Comment 2 Alan C Whiteman 2011-05-23 13:28:28 UTC

Created attachment 47071 [details]
CSV file with numerical values

The last two columns contain various numbers that when summed, they total zero. Converting the cells to 'number' and formatted as '123' reveals an apostrophe for every number, therefore the sum equation does not work.

Comment 3 vitriol 2011-05-23 13:39:25 UTC

@Alan C Whiteman
You must remove the option "Quoted field as text" if is checked.

Comment 4 Alan C Whiteman 2011-05-23 16:00:47 UTC

(In reply to comment #3)
> @Alan C Whiteman
> You must remove the option "Quoted field as text" if is checked.

Yes, that worked.

Thank you Vitriol.