Using xlsread with multiple delimiters

조회 수: 6 (최근 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
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일
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
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.
Tatjana Mü
Tatjana Mü 2022년 3월 31일
THANK YOU!

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by