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

Question

Zee 2022-6-6

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1734995-import-and-export-excel-data-having-combined-number-and-text-in-a-single-cell

回答： Peter Perkins 2022-6-13

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.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Zee 2022-6-7

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 2022-6-7

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 2022-6-13

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1734995-import-and-export-excel-data-having-combined-number-and-text-in-a-single-cell#answer_984890

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.