Use matlab to search and replace in an excel sheet

Hello,

I load an excel file with matlab R2015b using "xlsread". I would like to use matlab to search through the excel sheet for points and replace it with commas. Any ideas how do do that?

Thanks

[Stephen23]

Is it required to use MATLAB? Why not just VBA, or find-and-replace inside Excel?

[mael thevenot]

Currently I'm opening each excel file, click on find&replace, replace dot to commas then save.

Then I'm using a mat file to work on the data in the excel sheet.

But I will have to do that for thousand of files, so I'm searching a way to automatise this ^^"

[Caglar]

Both excel and Matlab has a setting for the decimal separator. Both of them can be set to work with comma or dot. Are you sure you cannot solve your problem that way?

If you really want to change dots to commas, you can load the file with xlsread, use strrep function with cellfun to change whatever you want and then save it back to excel with xlswrite. You may need to convert numerics from Excel to strings for this to work properly.

Note that, even in R2018a (I dont know about R2018b) xlswrite cannot write strings. You will need to convert back to chars or numerics after you make your string operation.

[mael thevenot]

MMh how could I do to tell matlab to use the dot as a decimal separator when I import excel files? I searched it and did not find any way to do it.

[madhan ravi]

https://www.mathworks.com/help/matlab/ref/dlmread.html#bul7e92-1

[Stephen23]

"how could I do to tell matlab to use the dot as a decimal separator when I import excel files? I searched it and did not find any way to do it."

MATLAB only recognizes the decimal point (i.e. period/dot) as the decimal separator. It does not recognize any other character (i.e. decimal commas are not recognized). This means there is no setting that you can/need to change.

[mael thevenot]

Matlab use the decimal point for everything I do, but for my xlsread command it do not work with dots.. If I use xlsread and have dots in my file, I obtain nonsense values. If I replace all dots to commas in my excel, I obtain my matrix of data with xlsread, with all commas being replaced by dots automotically by matlab.

[Walter Roberson]

Can you attach a sample?

[mael thevenot]

Ok, and I use

xlsread(filename, 'Feuil1', range);

range being all the data except the 1st column.

[Guillaume]

@Calgar,

"Both excel and Matlab has a setting for the decimal separator. Both of them can be set to work with comma or dot"

As far as I know this is not true for either. Excel uses Windows regional settings, it doesn't have its own. Matlab has very little support for , as a decimal separator. The only place it is aware of it is when exporting to the clipboard. It does not support it for import.

"xlswrite cannot write strings"

xlswrite can write strings just fine.

@mael thevenot,

As I said above, Excel uses Windows regional settings. So you can always change your regional settings to use a . instead of , as a decimal separator. Note that it will affect other programs. On my machine (Win10 + Excel 365), as soon as I change the decimal symbol in the regional settings, Excel automatically reflects the change.

However, it is really not clear what problem you are having. If the number are really stored as numbers in Excel, then whichever separator Excel uses does not matter for matlab. Matlab, using xlsread or readtable (or even through actxserver) will read the numbers with a . separator. That's because matlab doesn't read the textual representation of the numbers but their binary representation.

Of course, if the numbers are stored as text, then matlab will read the text as it is. But then they're not numbers in excel as well.

So, the whole "search through the excel sheet for points and replace it with commas" seems pointless. If it's for matlab, it's a non-issue and if it's for excel, you can't do that without changing the numbers to text and preventing you use them in calculation.

[Walter Roberson]

It is an xlsx file. The numbers are probably stored as text, because xlsx is a compressed folder of XML files.

[mael thevenot]

Well the problem I'm having is xlsread does not work with my excel files if I keep the dot as decimal separator in it. Cells do not have a particular format (standard format in my excel2016 version).

If I manually replace dots to commas, xlsread work and in Matlab I have my data, with dot as decimal separator. I've attached one of my excel file above, idk if you will have the same result than I do?

@Walter I tried changing the format of cells to numbers : with commas in my excel it work fine as well, but if I have dots I obtain a NaN matrix

[Guillaume]

"It is an xlsx file. The numbers are probably stored as text, because xlsx is a compressed folder of XML files".

Yes, the numbers are stored as text in the xlsx file (with a dot as decimal separator by the way!). However, that doesn't matter, since xlsread does not read the file itself (unless in basic mode). Instead it talks to Excel through COM and ask for the Value property of the range which is going to return values of type VARIANT|VT_R8 (double) most likely, certainly not text.

[Guillaume]

@mael,

Which OS are you using?

I have changed my regional settings so that , is the decimal separator. Opened your essai3.xlsx in excel and checked that it is indeed using ,. Restarted matlab just to be sure then

v = xlsread('essai3.xlsx')

shows all numbers with a . in matlab.

I'm using a newer version of matlab than yours but that shouldn't matter since xlsread was using the same mechanism in your version as it do now to communicate with excel.

[mael thevenot]

@Guillaume,

I'm using windows 10 professionnel. And if you keep your regional setting that way but replace all commas to dots in the excel file, you should face the same probleme than me with the xlsread command, don't you?

[Guillaume]

What does replace all commas to dot in the excel file actually mean?

If the numbers are stored as numbers in excel, the only way to replace all commas to dot is by changing the regional settings.

As I said, with comma regional settings and the file showing commas in excel, xlsread still return the correct numbers in matlab.

[Stephen23]

"And if you keep your regional setting that way but replace all commas to dots in the excel file, you should face the same probleme than me with the xlsread command, don't you?"

Excel shows values according to the locale settings, but they are always saved in xlsx files with periods. Changing the data in the worksheet without changing the locale (or options) just change the data from numeric to text, or vice versa. Not very useful at all.

I work for a large company where many use decimal commas and many use decimal points, and yet all Excel documents open correctly according my locale/options. I have never needed to change commas/periods like this.

What are you actually trying to do? What is the actual problem?

[Walter Roberson]

I opened the file in Office for Mac, and I read the file using xlsread() on Mac (which does not talk to Excel to read it), and I read the file using readtable(). All of the values appear to be the same; a numeric comparison of the first 100 entries in column 2 is bitwise identical between xlsread() and readtable()

[mael thevenot]

@Stephen my regional settings are already using commas by default. My problem is that I have .tdms files that I save in xlsx format, that are using a dot separator (I just receive those files like that and save them as xlsx). When I use xlsread I have a matrix of NaN, but if I search&replace commas to dots, xlsread give me a correct matrix...

@Walter readtable seems to work fine but I can't manage to delimitate which range of cells I want with this command :/

I just changed my regional settings to dot for decimal separator, start matlab, tried xlsread with an excel file with dot : NaN matrix. And I got correct values if I search&replace dots to commas......

[Stephen23]

"My problem is that I have .tdms files that I save in xlsx format, that are using a dot separator (I just receive those files like that and save them as xlsx)."

It sounds like that conversion is not working correctly. How do you "save in xlsx format"? With what tool? Please upload a sample .tdms file for us.

[Guillaume]

I assume the tdms files are converted to xlsx using National Instrument excel add-in (that is automatically installed with LabView). Note that there is a tdms reader in the file exchange which allows you to read the tdms file directly into matlab.

A sample of a generated xlsx file would be useful.

"start matlab, tried xlsread with an excel file with dot : NaN matrix" Then it doesn't look like your problem has anything to do with dot vs comma.

[mael thevenot]

I said something wrong sorry, the xlsx that are not working are from another soft, where there is a button "copy data" and then I can copy those data in an excel (and it have a dot as decimal separator). The default format of the cell is "standard" in my excel 2016. I tried to change the format of cells (text, number, ...) but xlsread do not work until I search&replace dots to commas.

If there is no way to do this change in matlab, I'll just go on by doing it manually, thanks for your time.