csv excel wrong decimal value

Question

Hi there,

I have a thermochrone datalogger that exports his data into a .csv file that looks like this,

Date/Time,Unit,Value
5/08/16 9:01:01,C,29,665
5/08/16 9:11:01,C,12,143
5/08/16 9:21:01,C,5,109
5/08/16 9:31:01,C,3,6
5/08/16 9:41:01,C,3,097

When I import the file into excel, it separates my value (which  is my temperature) like this

Date/Time	Unit	Value
5/08/2016 9:01	C	29	665
5/08/2016 9:11	C	12	143
5/08/2016 9:21	C	5	109
5/08/2016 9:31	C	3	6
5/08/2016 9:41	C	3	97

Is it possible to import this .csv file and separate everything, except my temperature value?

Thank you very much

Answer 1

Dear tom.db,

Use the below formula to extract the value of temperature.

Data has been assumed to be in Column A, beginning from cell A2.

=VALUE(SUBSTITUTE(MID(A2,FIND("C,",A2,1)+2,10),CHAR(44),".",1))

FIND will find the location of the text "C,"

MID will extract the required text, 2 places after the text "C,"

SUBSTITUTE and CHAR(44) will replace the comma with a decimal point

VALUE will convert the text result to numeric value.

You can either use Data - Text to Columns for the rest of the data, or else we can alter the above formula to suit the rest of the data.

Generally, I use IFERROR(AboveFormula,0) or IFERROR(AboveFormula,"-") , to get a zero or "-" as the result in case of an error.

Do let me know if this was what you wanted to do.

Vijaykumar Shetye, Goa, India

Answer 2

Hi,

There is something not logic in the export, the list separator and the decimal separator are both comma. Are you sure that this is the format? What do you get when you open the file with a text editor?

What are your settings in control panel?

Answer 3

Hi,

The first option won't work either.

My data lines are not perfect vertically aligned.

5/08/16 9:31:01,C,3,6
5/08/16 9:41:01,C,3,097
5/08/16 9:51:01,C,2,593
5/08/16 10:01:01,C,3,097
5/08/16 10:11:01,C,3,097
5/08/16 10:21:01,C,3,097

The total file contains about a 2000 rows, 4 days of temperature logging.

An uniform method would be helpful.

Thanks

Answer 4

Hi,

The second option will not work because Excel removes leading zeros.

Answer 5

Re:  importing .csv file to excel

If your entire data set lines up as per your example (all commas in line)...

then use the "fixed width" setting in the Data Import Wizard (step 1).

In step 2, you specify where you want the data separated.

Another option is to simply join the last two columns into one column...

In column E use:   =C2&D2   and fill down.

Copy and paste values and then delete columns C & D.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)