Skip to main content

Return to Answer

added 480 characters in body
Source Link
Dan
Dan
  • 139
  • 1
  • 4

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.

It also stops double quotes being zapped on read in, so a foolproof way of making text in a csv remain unchanged by excel EVEN IF is some text like "3.1415" is to surround it with double quotes AND prepend the whole string with a space, i.e. (using single quotes to show what you would type) ' "3.1415"'. Then in excel you always have the original string, except it is surrounded by double quotes and prepended by a space so you need to account for those in any formulas etc.

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.

It also stops double quotes being zapped on read in, so a foolproof way of making text in a csv remain unchanged by excel EVEN IF is some text like "3.1415" is to surround it with double quotes AND prepend the whole string with a space, i.e. (using single quotes to show what you would type) ' "3.1415"'. Then in excel you always have the original string, except it is surrounded by double quotes and prepended by a space so you need to account for those in any formulas etc.

Source Link
Dan
Dan
  • 139
  • 1
  • 4

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.