Import and export excel data having combined number and text in a single cell.

I have following subset of data of size (3x1) stored in an excel file. I would like to import this data and write into another excel file.

4-sample1-sample2-(23.56,-13.54)

8-sample1-sample2-(5.35, 6.25)

15-sample1-sample2-(23.10, 2101.20)

Since each cell has combination of number, text and character datatype, may I know which function to use for such operation.

[dpb]

<readtable> and/or <readcell> and their compatriots would be first choice. Which, specifically depends on how actually stored in the worksheet -- it it really is one cell each (why would you do that, btw???), then either will result in a one-column set of data as a cell, just whether is in a table or not.

It seems like this Q? or one very much like it just came around last week; I think I recall telling same thing about how to write such data to a file???

[Zee]

Thank you for your comment. I had posted that similar question on how to format in that particular way for which I was suggested to use sprintf function. I am making independent multiple scripts where I have to use output of some of these scripts as input for another script. One such output that I want to read is mentioned above. By each cell I meant that A(1,1) is 4-sample1-sample2-(23.56,-13.54), A(2,1) is 8-sample1-sample2-(5.35, 6.25) and so on.

[Walter Roberson]

A challenge with that format is that there is a risk that excel might see the leading digits and try to convert it to a number or a date, losing some information. The text representation in XML inside .xlsx files is prone to misinterpretation unless the field is clearly written as a character vector. For example "012" might be sent as a character vector but if not carefully encoded in the XML using a literal substitution, would likely be pulled back as 012 numeric and then having the leading 0 dropped to be read as numeric 12.

Because of this, when constructing text to be stored in xlsx files it is best to ensure that the text begins with a non-digit.

Answer 1

[Peter Perkins]

If each cell of your spreadsheet literally contains things like "4-sample1-sample2-(23.56,-13.54)", then you may want to figure out why and fix this at the source. This has to be like the most difficult data format ever.

Just for fun:

>> s = "4-sample1-sample2-(23.56,-13.54)"
s = 
    "4-sample1-sample2-(23.56,-13.54)"
>> s1 = split(s,"(")'
s1 = 
  1×2 string array
    "4-sample1-sample2-"    "23.56,-13.54)"
>> s11 = split(extractBefore(s1(1),strlength(s1(1))),"-")'
s11 = 
  1×3 string array
    "4"    "sample1"    "sample2"
>> s12 = replace(split(s1(2),",")',["(" ")"],["" ""])
s12 = 
  1×2 string array
    "23.56"    "-13.54"
>> s3 = [s11 s12]
s3 = 
  1×5 string array
    "4"    "sample1"    "sample2"    "23.56"    "-13.54"

There may be shorter ways, but that's what I came up with. So, read your spreadsheet into a 3x1 string array using readmatrix (you may need to tell it "string"), do the above on each row, to creat a 3x5 string array, then use writematrix.