Problem with importing a text file

Hello,

I need to import text files to matlab and then import them to excel, but matlab won't import the whole file. My txt files look like this:

Date	25/05/2021	Time	10:42:31	Status	OK
AA-Alg test1
Scan (#)	1	X (#)	1	Y (#)	1	Indentation (#)	1
X-position (um)	2000.000
Y-position (um)	1999.920
Z-position (um)	2291.040
Z surface (um)	2296.040
Piezo position (nm) (Measured)	-31.5

k (N/m)	0.290
Tip radius (um)	24.500
Calibration factor	2.488
SMDuration (s) 4.7


Control mode: Load
Measurement: Indentation
Profile:
D[Z1] (nm)	0.000	t[1] (s)	0.500
D[Z2] (nm)	5.000	t[2] (s)	5.000
D[Z3] (nm)	0.000	t[3] (s)	5.000
D[Z4] (nm)	0.000	t[4] (s)	0.500

Model: Hertz
P[max] (uN)	5.027
D[max] (nm)	0.000
D[final] (nm)	0.000
D[max-final] (nm)	0.000
Slope (N/m)	0.000
E[eff] (Pa)	9890.152
E[v=0.500] (Pa)	7417.614

Time (s)	Load (uN)	Indentation (nm)	Cantilever (nm)	Piezo (nm)	Auxiliary
0.000000	-0.010260	0.000000	-35.378081	20.677123	-0.004944
0.001000	-0.009494	0.000000	-32.737571	20.849585	-0.004863
0.002000	-0.007694	0.000000	-26.531443	21.049583	-0.004944
0.003000	-0.005115	0.000000	-17.636515	21.626005	-0.004863

But when I import them, it only imports the lower part:

 Time (s)  Load (uN)  Indentation (nm)  Cantilever (nm)  Piezo (nm)  Auxiliary 
 
  0  -0.01026  0  -35.378081  20.677123  -0.004944 
 
  0.001  -0.009494  0  -32.737571  20.849585  -0.004863 
 
  0.002  -0.007694  0  -26.531443  21.049583  -0.004944 
 
  0.003  -0.005115  0  -17.636515  21.626005  -0.004863 
 
  0.004  -0.002357  0  -8.126755  22.18949  -0.004783 
 
  0.005  0.000083  0  0.285939  22.358716  -0.004823 

This is the part of my code that doesn't do what I want it to:

            data = readtable(A, 'PreserveVariableNames', true); 
            writetable(data, 'myData.xls','Sheet', i);

I tried using the import tool aswell and it also didn't import the whole file.

I believe the problem is with delimiters but so far I couldn't find the right ones.

Please help, thank you in advance.

Answer 1

[dpb]

"... it only imports the lower part:"

Of course, because the file is a mishmash of stuff and only the last section is a regular array that can be recognized by an automated system without help in being told the structure of the file.

The file format was prepared for human, not machine consumption -- to read it you'll have to use low level i/o functions like textscan and/or fgetl, fscanf with specific formats that can parse the input records in the sequence in which they are in the file. This is doable, but certainly will be a fair amount of tedium in doing so.

You could start by readcell and get it all in memory in a cell array and then start parsing from memory.

[dpb]

Alternatively, does the instrument that creates this file format also have an option to download its data to an external device? Many (most?) do these days -- if so, it will undoubtedly be in a much easier format to parse.

Alternatively #2, contact the machine vendor or the vendor web site for support for such interfacing -- oftentimes vendors will supply interfacing routines for their products.

[Ruth Pikovsky]

Thank you so much for the answer! I didn't expect that the format would be problematic. I have decided instead to use matlab to run an excel macro that imports the files and it works exactly as expected.

[dpb]

What is/was expected?

I'm curious what this magic macro does -- what did you get in the MATLAB workspace?

If it's just a cell array, then readcell would do the same -- I'd be extremely interested in a tool that could magically turn all those values into MATLAB variables.

[Ruth Pikovsky]

All I needed was to import txt files to excel so that each file will be in a separate sheet. I used the VBA: https://www.extendoffice.com/documents/excel/3231-excel-import-multiple-text-files-to-multiple-sheets.html

and it imported the whole file with no issues (with Tab delimiter).

I used actxserver to access excel through matlab and then executed calculations and other excel macros with matlab. Sorry if it doesn't exactly solve the problem but in the end I didn't need the variables in matlab, just in excel.

[dpb]

Oh. That's a wholly different Q? than the one interpreted from the one asked.

That's trivial in ML, too...

d=dir(fullfile(rootdir,'*.txt'));       % use appropriate wild card, directory structure
fn=fullfile(outrootdir,'outfile.xlsx'); % define output file
for i=1:numel(d)
  data=readcell(fullfile(d(i).folder,d(i).name));
  writecell(data,fn,'sheet',"Sheet"+i)
end

You can, of course, create the sheet name to be whatever is wanted with compose or just use the incremental number of the loop counter, i

When MATLAB tries to "importdata" it presumes one intends to use those numeric values in calculations and, therefore, turn them into variables.

[Ruth Pikovsky]

Thank you! Yeah that is what I was looking for, sorry if i explained the question incorrectly. At first I thought it would be better to turn the data to variables and do calculations in matlab, but actually doing it in excel is also a good solution.Again, thank you so much for all your help.