Using xlsread with multiple delimiters

8 次查看（过去 30 天）
Tatjana Mü
Tatjana Mü 2022-3-31
评论： Tatjana Mü 2022-3-31
Hi,
I want to write a script, which can import xlsx, txt and csv files. Therefore I use xlsread. I know it's not recommded, but I have no headers, so readtable is no option. But I struggle to add multiple delimiters into the code of xlsread. I tried this code:
directory_name=uigetdir('','Waehl den Ordner mit den Messungen');
[nur_file_name,pfad]=uigetfile({'*.csv;*.txt;*.xlsx','csv&xlsx&txt-files (*.csv,*.txt,*xlsx)';'*.*','all Files'},...
'Die Intensitäts-Files der Proben oeffnen (probe_001.txt=',...
[directory_name '/'], 'Multiselect', 'on');
[num,txt,raw] = xlsread(filename{xy}, [',' ';' '\t'], 'B1:KR1');
But it is sadly not working in the last line. The delimiters are not accepted in this form. I am grateful for every hint.
  9 个评论
显示 7更早的评论
Stephen23
Stephen23 2022-3-31
Tatjana Mü's incorrectly posted "Answer" moved here:
I realised I did a huge mistake. xlsread is really the wrong option - I am sorry. So most important is to read in the file "SMP_Std.csv". Most of my files will be like this.
The probleme is the seperation with a ','.
I just added a picture, how excel is opening the file. Everything is in one cell.
So I want to read in this file.
[num,txt,raw] = xlsread(filename{xy}, 'B1:KR1');
element_cim=string(txt);
element_cim(:,[5 7:29 31:32)=[];
element_cim=regexprep(element_cim,'\[','');
element_cim=regexprep(element_cim,'\]','');
element_cim=regexprep(element_cim,'\''','');
element_cim = convertStringsToChars(element_cim);
element_cim=char(element_cim);
end
xlsread is not working. I want to read in the range from 'B1:KR1'. Then I delete some columns, delete some columns and parts of the element name and want to receive a char like this:
val =
'23Na+ '
'24Mg+ '
'25Mg+ '
'26Mg+ '
'27Al+ '
'39K+ '
'40Ca+ '
'41K+ '
'42Ca+ '
'43Ca+ '
'87Sr++ '
'88Sr++ '
'44Ca+ '
'45Sc+ '
'46Ca+ '
'48Ca+ '
'50Cr+ '
'50V+ '
'51V+ '
'52Cr+ '
'53Cr+ '
Do you know how I receive this?
Stephen23
Stephen23 2022-3-31
编辑：Stephen23 2022-3-31
"The probleme is the seperation with a ','.... I just added a picture, how excel is opening the file. Everything is in one cell."
Sure. Those a problems that Excel has due to your OS's delimiter settings (i.e. locale settings).
But that has nothing to do with MATLAB.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Stephen23
Stephen23 2022-3-31
编辑：Stephen23 2022-3-31
Ran in:
Without a sample XLSX file I had to create my own (attached).
It is easy to read the first line using READCELL, it will correctly indentify the delimiter character:
firstrow('SMP_3.8.1.csv')
ans = 277×7 char array
'23Na+ ' '24Mg+ ' '25Mg+ ' '26Mg+ ' '27Al+ ' '39K+ ' '40Ca+ ' '41K+ ' 'ArH+ ' '42Ca+ ' 'ArH2+ ' '43Ca+ ' '87Sr++ ' '88Sr++ ' '44Ca+ ' 'CO2+ ' '45Sc+ ' 'CHO2+ ' '46Ti+ ' '46Ca+ ' 'NO2+ ' '47Ti+ ' '48Ti+ ' '48Ca+ ' '49Ti+ ' '50Ti+ ' '50Cr+ ' '50V+ ' '51V+ ' 'ClO+ ' '52Cr+ ' '36ArO+ ' '53Cr+ ' '37ClO+ ' '54Cr+ ' '54Fe+ ' 'ArN+ ' '55Mn+ ' '56Fe+ ' 'ArO+ ' '57Fe+ ' 'ArOH+ ' '58Fe+ ' '58Ni+ ' '59Co+ ' '60Ni+ ' '61Ni+ ' '62Ni+ ' '63Cu+ ' '64Ni+ ' '64Zn+ ' '65Cu+ ' '66Zn+ ' '67Zn+ ' '68Zn+ ' '137Ba++' '69Ga+ ' '138Ba++' '70Ge+ ' '70Zn+ ' '71Ga+ ' '72Ge+ ' '73Ge+ ' '74Ge+ ' '74Se+ ' '75As+ ' 'ArCl+ ' '76Se+ ' '76Ge+ ' 'Ar36Ar+' '77Se+ ' 'Ar37Cl+' '78Se+ ' '78Kr+ ' 'Ar38Ar+' '79Br+ ' '80Kr+ ' '80Se+ ' 'Ar2+ ' '81Br+ ' '82Kr+ ' '82Se+ ' '83Kr+ ' '84Kr+ ' '84Sr+ ' '85Rb+ ' '86Sr+ ' '86Kr+ ' '87Sr+ ' '87Rb+ ' '88Sr+ ' '89Y+ ' '90Zr+ ' '91Zr+ ' '92Zr+ ' '92Mo+ ' '93Nb+ ' '94Mo+ ' '94Zr+ ' '95Mo+ ' '96Mo+ ' '96Ru+ ' '96Zr+ ' '97Mo+ ' '98Ru+ ' '98Mo+ ' '99Ru+ ' '100Ru+ ' '100Mo+ ' '101Ru+ ' '102Ru+ ' '102Pd+ ' '103Rh+ ' '104Pd+ ' '104Ru+ ' '105Pd+ ' '106Pd+ ' '106Cd+ ' '107Ag+ ' '108Pd+ ' '108Cd+ ' '109Ag+ ' '110Cd+ ' '110Pd+ ' '111Cd+ ' '112Cd+ ' '112Sn+ ' '113In+ ' '113Cd+ ' '114Sn+ ' '114Cd+ ' '115Sn+ ' '115In+ ' '116Sn+ ' '116Cd+ ' '117Sn+ ' '118Sn+ ' '119Sn+ ' '102Sn+ ' '120Te+ ' '121Sb+ ' '122Te+ ' '122Sn+ ' '123Sb+ ' '123Te+ ' '124Te+ ' '124Sn+ ' '124Xe+ ' '125Te+ ' '126Te+ ' '126Xe+ ' '127I+ ' '128Xe+ ' '128Te+ ' '129Xe+ ' '130Xe+ ' '130Te+ ' '130Ba+ ' '131Xe+ ' '132Xe+ ' '132Ba+ ' '133Cs+ ' '134Ba+ ' '134Xe+ ' '135Ba+ ' '136Ba+ ' '136Ce+ ' '136Xe+ ' '137Ba+ ' '138Ba+ ' '138Ce+ ' '138La+ ' '139La+ ' '140Ce+ ' '141Pr+ ' '142Nd+ ' '142Ce+ ' '143Nd+ ' '144Nd+ ' '144Sm+ ' '145Nd+ ' '146Nd+ ' '147Sm+ ' '148Sm+ ' '148Nd+ ' '149Sm+ ' '150Sm+ ' '150Nd+ ' '151Eu+ ' '152Sm+ ' '152Gd+ ' '153Eu+ ' '154Gd+ ' '154Sm+ ' '155Gd+ ' 'CeO+ ' '156Gd+ ' '156Dy+ ' '157Gd+ ' '158Gd+ ' '158Dy+ ' '159Tb+ ' '160Dy+ ' '160Gd+ ' '161Dy+ ' '162Dy+ ' '162Er+ ' '163Dy+ ' '164Dy+ ' '164Er+ ' '165Ho+ ' '166Er+ ' '167Er+ ' '168Er+ ' '168Yb+ ' '169Tm+ ' '170Yb+ ' '170Er+ ' '171Yb+ ' '172Yb+ ' '173Yb+ ' '174Yb+ ' '174Hf+ ' '175Lu+ ' '176Hf+ ' '176Yb+ ' '176Lu+ ' '177Hf+ ' '178Hf+ ' '179Hf+ ' '180Hf+ ' '180W+ ' '180Ta+ ' '181Ta+ ' '182W+ ' '183W+ ' '184W+ ' '184Os+ ' '185Re+ ' '186Os+ ' '186W+ ' '187Os+ ' '187Re+ ' '188Os+ ' '189Os+ ' '190Os+ ' '190Pt+ ' '191Ir+ ' '192Pt+ ' '192Os+ ' '193Ir+ ' '194Pt+ ' '195Pt+ ' '196Pt+ ' '196Hg+ ' '197Au+ ' '198Hg+ ' '198Pt+ ' '199Hg+ ' '200Hg+ ' '201Hg+ ' '202Hg+ ' '203Tl+ ' '204Pb+ ' '204Hg+ ' '205Tl+ ' '206Pb+ ' '207Pb+ ' '208Pb+ ' '209Bi+ ' '220Bkg+' '232Th+ ' '234U+ ' '235U+ ' '238U+ ' 'ThO+ ' 'UO+ '
firstrow('SMP_Std.csv')
ans = 277×7 char array
'23Na+ ' '24Mg+ ' '25Mg+ ' '26Mg+ ' '27Al+ ' '39K+ ' '40Ca+ ' '41K+ ' 'ArH+ ' '42Ca+ ' 'ArH2+ ' '43Ca+ ' '87Sr++ ' '88Sr++ ' '44Ca+ ' 'CO2+ ' '45Sc+ ' 'CHO2+ ' '46Ti+ ' '46Ca+ ' 'NO2+ ' '47Ti+ ' '48Ti+ ' '48Ca+ ' '49Ti+ ' '50Ti+ ' '50Cr+ ' '50V+ ' '51V+ ' 'ClO+ ' '52Cr+ ' '36ArO+ ' '53Cr+ ' '37ClO+ ' '54Cr+ ' '54Fe+ ' 'ArN+ ' '55Mn+ ' '56Fe+ ' 'ArO+ ' '57Fe+ ' 'ArOH+ ' '58Fe+ ' '58Ni+ ' '59Co+ ' '60Ni+ ' '61Ni+ ' '62Ni+ ' '63Cu+ ' '64Ni+ ' '64Zn+ ' '65Cu+ ' '66Zn+ ' '67Zn+ ' '68Zn+ ' '137Ba++' '69Ga+ ' '138Ba++' '70Ge+ ' '70Zn+ ' '71Ga+ ' '72Ge+ ' '73Ge+ ' '74Ge+ ' '74Se+ ' '75As+ ' 'ArCl+ ' '76Se+ ' '76Ge+ ' 'Ar36Ar+' '77Se+ ' 'Ar37Cl+' '78Se+ ' '78Kr+ ' 'Ar38Ar+' '79Br+ ' '80Kr+ ' '80Se+ ' 'Ar2+ ' '81Br+ ' '82Kr+ ' '82Se+ ' '83Kr+ ' '84Kr+ ' '84Sr+ ' '85Rb+ ' '86Sr+ ' '86Kr+ ' '87Sr+ ' '87Rb+ ' '88Sr+ ' '89Y+ ' '90Zr+ ' '91Zr+ ' '92Zr+ ' '92Mo+ ' '93Nb+ ' '94Mo+ ' '94Zr+ ' '95Mo+ ' '96Mo+ ' '96Ru+ ' '96Zr+ ' '97Mo+ ' '98Ru+ ' '98Mo+ ' '99Ru+ ' '100Ru+ ' '100Mo+ ' '101Ru+ ' '102Ru+ ' '102Pd+ ' '103Rh+ ' '104Pd+ ' '104Ru+ ' '105Pd+ ' '106Pd+ ' '106Cd+ ' '107Ag+ ' '108Pd+ ' '108Cd+ ' '109Ag+ ' '110Cd+ ' '110Pd+ ' '111Cd+ ' '112Cd+ ' '112Sn+ ' '113In+ ' '113Cd+ ' '114Sn+ ' '114Cd+ ' '115Sn+ ' '115In+ ' '116Sn+ ' '116Cd+ ' '117Sn+ ' '118Sn+ ' '119Sn+ ' '102Sn+ ' '120Te+ ' '121Sb+ ' '122Te+ ' '122Sn+ ' '123Sb+ ' '123Te+ ' '124Te+ ' '124Sn+ ' '124Xe+ ' '125Te+ ' '126Te+ ' '126Xe+ ' '127I+ ' '128Xe+ ' '128Te+ ' '129Xe+ ' '130Xe+ ' '130Te+ ' '130Ba+ ' '131Xe+ ' '132Xe+ ' '132Ba+ ' '133Cs+ ' '134Ba+ ' '134Xe+ ' '135Ba+ ' '136Ba+ ' '136Ce+ ' '136Xe+ ' '137Ba+ ' '138Ba+ ' '138Ce+ ' '138La+ ' '139La+ ' '140Ce+ ' '141Pr+ ' '142Nd+ ' '142Ce+ ' '143Nd+ ' '144Nd+ ' '144Sm+ ' '145Nd+ ' '146Nd+ ' '147Sm+ ' '148Sm+ ' '148Nd+ ' '149Sm+ ' '150Sm+ ' '150Nd+ ' '151Eu+ ' '152Sm+ ' '152Gd+ ' '153Eu+ ' '154Gd+ ' '154Sm+ ' '155Gd+ ' 'CeO+ ' '156Gd+ ' '156Dy+ ' '157Gd+ ' '158Gd+ ' '158Dy+ ' '159Tb+ ' '160Dy+ ' '160Gd+ ' '161Dy+ ' '162Dy+ ' '162Er+ ' '163Dy+ ' '164Dy+ ' '164Er+ ' '165Ho+ ' '166Er+ ' '167Er+ ' '168Er+ ' '168Yb+ ' '169Tm+ ' '170Yb+ ' '170Er+ ' '171Yb+ ' '172Yb+ ' '173Yb+ ' '174Yb+ ' '174Hf+ ' '175Lu+ ' '176Hf+ ' '176Yb+ ' '176Lu+ ' '177Hf+ ' '178Hf+ ' '179Hf+ ' '180Hf+ ' '180W+ ' '180Ta+ ' '181Ta+ ' '182W+ ' '183W+ ' '184W+ ' '184Os+ ' '185Re+ ' '186Os+ ' '186W+ ' '187Os+ ' '187Re+ ' '188Os+ ' '189Os+ ' '190Os+ ' '190Pt+ ' '191Ir+ ' '192Pt+ ' '192Os+ ' '193Ir+ ' '194Pt+ ' '195Pt+ ' '196Pt+ ' '196Hg+ ' '197Au+ ' '198Hg+ ' '198Pt+ ' '199Hg+ ' '200Hg+ ' '201Hg+ ' '202Hg+ ' '203Tl+ ' '204Pb+ ' '204Hg+ ' '205Tl+ ' '206Pb+ ' '207Pb+ ' '208Pb+ ' '209Bi+ ' '220Bkg+' '232Th+ ' '234U+ ' '235U+ ' '238U+ ' 'ThO+ ' 'UO+ '
firstrow('SMP_Std.xlsx')
ans = 277×7 char array
'23Na+ ' '24Mg+ ' '25Mg+ ' '26Mg+ ' '27Al+ ' '39K+ ' '40Ca+ ' '41K+ ' 'ArH+ ' '42Ca+ ' 'ArH2+ ' '43Ca+ ' '87Sr++ ' '88Sr++ ' '44Ca+ ' 'CO2+ ' '45Sc+ ' 'CHO2+ ' '46Ti+ ' '46Ca+ ' 'NO2+ ' '47Ti+ ' '48Ti+ ' '48Ca+ ' '49Ti+ ' '50Ti+ ' '50Cr+ ' '50V+ ' '51V+ ' 'ClO+ ' '52Cr+ ' '36ArO+ ' '53Cr+ ' '37ClO+ ' '54Cr+ ' '54Fe+ ' 'ArN+ ' '55Mn+ ' '56Fe+ ' 'ArO+ ' '57Fe+ ' 'ArOH+ ' '58Fe+ ' '58Ni+ ' '59Co+ ' '60Ni+ ' '61Ni+ ' '62Ni+ ' '63Cu+ ' '64Ni+ ' '64Zn+ ' '65Cu+ ' '66Zn+ ' '67Zn+ ' '68Zn+ ' '137Ba++' '69Ga+ ' '138Ba++' '70Ge+ ' '70Zn+ ' '71Ga+ ' '72Ge+ ' '73Ge+ ' '74Ge+ ' '74Se+ ' '75As+ ' 'ArCl+ ' '76Se+ ' '76Ge+ ' 'Ar36Ar+' '77Se+ ' 'Ar37Cl+' '78Se+ ' '78Kr+ ' 'Ar38Ar+' '79Br+ ' '80Kr+ ' '80Se+ ' 'Ar2+ ' '81Br+ ' '82Kr+ ' '82Se+ ' '83Kr+ ' '84Kr+ ' '84Sr+ ' '85Rb+ ' '86Sr+ ' '86Kr+ ' '87Sr+ ' '87Rb+ ' '88Sr+ ' '89Y+ ' '90Zr+ ' '91Zr+ ' '92Zr+ ' '92Mo+ ' '93Nb+ ' '94Mo+ ' '94Zr+ ' '95Mo+ ' '96Mo+ ' '96Ru+ ' '96Zr+ ' '97Mo+ ' '98Ru+ ' '98Mo+ ' '99Ru+ ' '100Ru+ ' '100Mo+ ' '101Ru+ ' '102Ru+ ' '102Pd+ ' '103Rh+ ' '104Pd+ ' '104Ru+ ' '105Pd+ ' '106Pd+ ' '106Cd+ ' '107Ag+ ' '108Pd+ ' '108Cd+ ' '109Ag+ ' '110Cd+ ' '110Pd+ ' '111Cd+ ' '112Cd+ ' '112Sn+ ' '113In+ ' '113Cd+ ' '114Sn+ ' '114Cd+ ' '115Sn+ ' '115In+ ' '116Sn+ ' '116Cd+ ' '117Sn+ ' '118Sn+ ' '119Sn+ ' '102Sn+ ' '120Te+ ' '121Sb+ ' '122Te+ ' '122Sn+ ' '123Sb+ ' '123Te+ ' '124Te+ ' '124Sn+ ' '124Xe+ ' '125Te+ ' '126Te+ ' '126Xe+ ' '127I+ ' '128Xe+ ' '128Te+ ' '129Xe+ ' '130Xe+ ' '130Te+ ' '130Ba+ ' '131Xe+ ' '132Xe+ ' '132Ba+ ' '133Cs+ ' '134Ba+ ' '134Xe+ ' '135Ba+ ' '136Ba+ ' '136Ce+ ' '136Xe+ ' '137Ba+ ' '138Ba+ ' '138Ce+ ' '138La+ ' '139La+ ' '140Ce+ ' '141Pr+ ' '142Nd+ ' '142Ce+ ' '143Nd+ ' '144Nd+ ' '144Sm+ ' '145Nd+ ' '146Nd+ ' '147Sm+ ' '148Sm+ ' '148Nd+ ' '149Sm+ ' '150Sm+ ' '150Nd+ ' '151Eu+ ' '152Sm+ ' '152Gd+ ' '153Eu+ ' '154Gd+ ' '154Sm+ ' '155Gd+ ' 'CeO+ ' '156Gd+ ' '156Dy+ ' '157Gd+ ' '158Gd+ ' '158Dy+ ' '159Tb+ ' '160Dy+ ' '160Gd+ ' '161Dy+ ' '162Dy+ ' '162Er+ ' '163Dy+ ' '164Dy+ ' '164Er+ ' '165Ho+ ' '166Er+ ' '167Er+ ' '168Er+ ' '168Yb+ ' '169Tm+ ' '170Yb+ ' '170Er+ ' '171Yb+ ' '172Yb+ ' '173Yb+ ' '174Yb+ ' '174Hf+ ' '175Lu+ ' '176Hf+ ' '176Yb+ ' '176Lu+ ' '177Hf+ ' '178Hf+ ' '179Hf+ ' '180Hf+ ' '180W+ ' '180Ta+ ' '181Ta+ ' '182W+ ' '183W+ ' '184W+ ' '184Os+ ' '185Re+ ' '186Os+ ' '186W+ ' '187Os+ ' '187Re+ ' '188Os+ ' '189Os+ ' '190Os+ ' '190Pt+ ' '191Ir+ ' '192Pt+ ' '192Os+ ' '193Ir+ ' '194Pt+ ' '195Pt+ ' '196Pt+ ' '196Hg+ ' '197Au+ ' '198Hg+ ' '198Pt+ ' '199Hg+ ' '200Hg+ ' '201Hg+ ' '202Hg+ ' '203Tl+ ' '204Pb+ ' '204Hg+ ' '205Tl+ ' '206Pb+ ' '207Pb+ ' '208Pb+ ' '209Bi+ ' '220Bkg+' '232Th+ ' '234U+ ' '235U+ ' '238U+ ' 'ThO+ ' 'UO+ '
function tmp = firstrow(fnm)
tmp = readcell(fnm, 'Range','B1:KR1');
tmp([5,7:29,31:32]) = [];
tmp = regexprep(tmp,'\[|\]|''','');
tmp = char(tmp);
end
  3 个评论
显示 1更早的评论
Stephen23
Stephen23 2022-3-31
编辑：Stephen23 2022-3-31
No, do not change the function like that (it is invalid sytnax to put any indexing into the function signature line).
I doubt that you need to change the function much, most likely you can just call it like this:
[fnm,pfad] = uigetfile(.. whatever you want here..);
out = firstrow(fullfile(pfad,fnm))
If you do not use FULLFILE then you will have problems with the file not being found.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by