data written to excel string is text rather than double when opened in excel

[mittleiderj]

I have code that combines a time string, numerous double arays and a couple of individual doubles. All seems to work well except when I open the created data file in excel, the first cell after the time string is always a string and not numeric. This is the first value in a double aray and all the other elements of the array are correctly converted to numbers. Only the first element is being interpreted incorrectly. This is messing up an other wise nice spreadsheet.

Any thoughts?

[Dennis_Knutson]

You aren't creating an Excel file at all. It's a simple text file with tabs between the values.

[mittleiderj]

Yes, that's right. I wasn't clear. I'm actually creating a text file with commas delimiting the data. When opened by excel the first cell of data following the time string is also a text string. All of the other data is numeric as it should be. The data in the subject cell is the first element of a 32 element array. I don't understand why this one element is being misinterpreted by excel.

[P@Anand]

The way you have built the data is little confusing. You are just adding all the data together in a single column/row and on top of it you are adding the timestamp, is that the same you are trying to do? Why I am asking is I see you have 2 1D data and individual DBL data which you are just concatenating to the array. Please try to attach the output file (the text/excel file) which you have the problem.

[Dennis_Knutson]

I don't know how you are importing it into Excel but perhaps you should be asking Microsoft.

[natasftw]

What do you mean when you say it enters a string into excel?  If it's a number, Excel should be able to handle it easily as a number.

What "string" is it entering?  Is it a string of the value?  Is a string of the resources name?  What is the string?  Why not attach a test file with the associated Excel sheet?

[crossrulz · Accepted solution]

You already have the strings in a spreadsheet format.  Use Cancatinate Strings instead of Build Array and then use the Write Text File function.  That could be causing all kinds of confusion for Excel.

[mittleiderj]

Here is a data file sample. Check the first column of data following the time stamp.

Crossrulz, good point. I will try concatenating the strings on Monday.

Thanks all for your responses.

[RavensFan]

The problem is Excel.  When Excel gets a text file, it isn't actually opening the file, it is importing it.  It has default settings it uses for how to parse the data.  It seems to have no problem recognizing a tab as a cell separator, but it doesn't recognize a comma has a cell separator.  In older Excel, you could do a File/Import, and you'd have to intentionally check the checkbox for a comma to be a delimiter.

I really think Microsoft was pretty stupid on this point.  Excel recognizes CSV as a file type to import.  CSV stands for Comma Separated Values.  Yet Excel doesn't treat the comma as a separator.

Do you have to use a comma as a separator, or can you use a tab?

Your code is kind of convoluted (as pointed out by others).  Your combining string manipulations with array to spreadsheet string making a comma has a delimiter, then building an array, then writing that 2 column array to a file using Write to Spreadsheet file where now the tab is your delimiter.  That is why the first column sort of works.  Excel understands the tab.

That whole section of code really needs to be rewritten and use a consistent format.

[mittleiderj]

OK, here is how I changed it and it seems to work on my simulated data. My customer has requested the data be in a CSV format thus the commas rather than tabs. Is there a more efficient way to write this?

Thanks for the help.