10-17-2014 10:25 PM
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?
Solved! Go to Solution.
10-17-2014 11:01 PM
10-17-2014 11:11 PM
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.
10-18-2014 12:21 AM
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.
10-18-2014 12:54 AM
10-18-2014 09:11 AM
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?
10-18-2014 09:45 AM
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.
10-18-2014 11:58 AM
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.
10-18-2014 12:22 PM
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.
10-18-2014 12:44 PM
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.