Removing Excess Data From a .csv File using Textscan

Good afternoon, all. I'm hoping someone with a keen eye can help. I have a .csv file I'm reading into MATLAB using textscan. Here's an example of my code:

fid = fopen('file.csv');
fstring = '%f %*14s %f %f/%f %f:%f:%f %f/%f %f:%f:%f %*1s %*s %*4s %f/%f %f:%f:%f %f %f %*s %*s %*s %*s %f %f %f %f %f %f %f %f %f %f %*s %*s %*s %*s %*s %*s %*s %*s %*s %*s';
fstring = strcat(fstring, '%*[^\n]'); 
line = fgetl(fid);
data_matrix = [];
while (line ~= -1)
    data = textscan(fid,fstring,'MultipleDelimsAsOne',0, 'Delimiter',',');
    B = cell2mat(data);
    data_matrix = [data_matrix;B];
    line = fgetl(fid);
end
fclose(fid);

The goal is to get rid of the extraneous columns so I have a m-by-n matrix of numerical data. 99% of the time, this technique works flawlessly for what I need it to do, but this time, there's an issue I can't put my finger on. What I want to do is delete the last 10 columns, which correspond to the last 10 %*s entries in 'fstring'; however this isn't happening. Everywhere else there's a %*s, the data gets deleted like I want except for the columns in question. Of those 10 columns, the first 2 contain data and the rest are blank. Neither columns 1/10 or 2/10 contain data of fixed length, so I can't use %*'number_of_characters's as I did at the beginning of fstring.

As an alternative, I substituted %*[\n] for the repeated %*s entries per MathWorks' textscan help page, but I get the same error. For what it's worth, if I manually delete the last 10 columns in the source file (and subsequently don't include the multiple %*s entries in fstring), everything works just fine.

If I left anything unclear, don't hesitate to call me on it and I'll do my best to clarify.

[Walter Roberson]

Could you show a couple of sample lines of input?

[Jeremy]

Sure thing. Apologies for not doing this in the first place.

[Walter Roberson]

What you have named Example.csv is actually a .xlsx file and not a .csv file at all.

Answer 1

[Walter Roberson]

t = readtable('Example.xlsx');
t(:,end-9:end) = [];  %get rid of last 10 columns.

[Jeremy]

I see where you're going with this and I like it, but invoking the 'readtable' function seems to throw everything out of whack due to the fact that I get an error with the 'fgetl' function. I tried a few modifications to include converting the table --> array and table --> cell --> array (wing and a prayer, I know, but worth a try), then moving the deleting line inside the while loop.

Question: would you recommend using the method you provided to eliminate all unnecessary columns, then take the resulting table and convert to a matrix?

[Walter Roberson]

You would not get an error with the fgetl() if you were not using fgetl()... readtable() replaces all of the fopen/fgetl/textscan.

filename = 'Example.xlsx';
opt = detectImportOptions(filename, 'Range', 'A:Y');
opt = setvartype(opt, {'D', 'E', 'I'}, 'datetime');
opt = setvaropts(opt, {'D', 'E', 'I'}, 'InputFormat', 'DDD/uuuu HH:mm:ss', 'DatetimeFormat', 'yyyy-MM-dd HH:mm:ss');

t = readtable(filename, opt);

This already has the last 10 columns removed.

You cannot convert this directly to array form because it contains a mix of data types. But you can do things like

t{:,end-9:end}

[Jeremy]

You are a scholar and a gentleman. This makes perfect sense now, as do the errors I kept getting. Thank you very much for your time and help!

Answer 2

[per isakson]

As Walter Roberson noted, the file Example.csv, which you attached, is an Excel-file. textscan cannot read Excel-files.

I made a little test:

• opened the file Example.csv with Excel and saved the content in a csv-file, CsvExample.csv.
• imported CsvExample.csv with your code.

I got the expected result. No problems.

In response to comment

Output from running your code.

>> data = cssm
data =
   1.0e+06 *
  Columns 1 through 15
    9.2271    0.0000    0.0001    0.0020    0.0000    0.0000    0.0000    0.0001    0.0020    0.0000    0.0000    0.0000    0.0001    0.0020    0.0000
    9.2297    0.0000    0.0001    0.0020    0.0000    0.0000    0.0001    0.0001    0.0020    0.0000    0.0000    0.0000    0.0001    0.0020    0.0000
  Columns 16 through 29
    0.0000    0.0000    0.0011    0.0000    0.0000    0.0002    0.0012    0.0000    1.5571    0.1049    9.2271    0.0000    0.0000    0.0000
    0.0000    0.0000    0.0011    0.0000    0.0000    0.0005    0.0014    0.0000    1.5570    0.1041    9.2297    0.0000    0.0000   -0.0000
>> data(:,27:29)
ans =
    1.0000    0.0319    0.0000
    1.0000    0.1440   -0.0067

where

function    data_matrix = cssm
    fid = fopen('CsvExample.csv');
    fstring = '%f%*14s%f%f/%f%f:%f:%f%f/%f%f:%f:%f%*1s%*s%*4s%f/%f%f:%f:%f%f%f%*s%*s%*s%*s%f%f%f%f%f%f%f%f%f%f%*s%*s%*s%*s%*s%*s%*s%*s%*s%*s';
    fstring = strcat(fstring, '%*[^\n]');
    line = fgetl(fid);
    data_matrix = [];
    while (line ~= -1)
        data = textscan(fid,fstring,'MultipleDelimsAsOne',false, 'Delimiter',',');
        B = cell2mat(data);
        data_matrix = [data_matrix;B];
        line = fgetl(fid);
    end
    fclose(fid);
end

and where CsvExample.csv is a text file, which contains

A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI
9227077,Associated-INT,2,120/2018 17:46:46,120/2018 17:48:23,U,,TRUE,120/2018 18:01:39,1114,5,,,,,9.756394,197.121261,1212.91292,0.207306,1557117,104934,9227077,1,0.031927,0.000001,1114 ADS_TheSpacePlace_05,2f36bbaa-83e7-a4b2-a827-ce108a04421d,,,,,,,,
9229705,Associated-INT,2,120/2018 17:14:53,120/2018 17:17:19,U,,TRUE,120/2018 17:32:22,1117,5,,,,,10.552097,470.651237,1448.617523,4.710569,1557048,104053,9229705,1,0.143954,-0.006698,1117 ADS_TheSpacePlace_08,ea02655b-040e-a6fd-d7b7-d61148cabad3,,,,,,,,

"[...] the result was only 25 columns wide versus 35?"

No, I get 29 columns. The specifier %f appears 29 times in the format string, fstring.

>> fstring = '%f%*14s%f%f/%f%f:%f:%f%f/%f%f:%f:%f%*1s%*s%*4s%f/%f%f:%f:%f%f%f%*s%*s%*s%*s%f%f%f%f%f%f%f%f%f%f%*s%*s%*s%*s%*s%*s%*s%*s%*s%*s';
>> pos = strfind( fstring, '%f' );
>> length( pos )
ans =
    29
>>

"the website wouldn't let me upload the .xlsx version"

That's to prevent the user to open the downloaded file with a click. However, changing the extension doesn't change the format of the file. Example.csv is a binary file with a special format that is used by Excel.

[Jeremy]

Interesting. This is why computers kill me; the website wouldn't let me upload the .xlsx version so I thought it switched to .csv with no issues.

At any rate, I'll load the file again to see if I can make it work after correcting the file extension. So I'm clear, you were able to read the .csv file into MATLAB such that the result was only 25 columns wide versus 35?

Thanks for looking into this!

[Walter Roberson]

You can zip xlsx files and attach the zip.

But it isn't worth uploading now; people can rename it to xlsx extension if they still want to look at it.

[per isakson]

@Jeremy, I added a response to the my answer.

[Jeremy]

I sincerely appreciate the help, my friend. For whatever reason, I can't make it work, though, so I'm about resigned to just delete the data manually from the spreadsheet when it becomes necessary.

At this point, I'm not even sure what's making things go wrong. To test things a bit, I deleted the data from the spreadsheet, removed the last 10 %*s characters from 'fstring', and ran it in MATLAB. Everything worked just fine. That itself is no great surprise, but it does point to the fact that I probably have to do something I'm simply not seeing with respect to the format of the %*s characters.

Thanks a lot for your time and patience!

[Walter Roberson]

? Are you working with a .xlsx file or are you working with a csv file? The file you had attached earlier was xlsx and the readtable() based code I attached works on it. If you are working with csv file then I will need a sample of that.

[per isakson]

@Walter, I'm reading a proper csv-file. I opened the attached file with Excel, which required some "persuasion", and SaveAs a csv-file.

[Walter Roberson]

I am concerned, though, that if I save as csv that the result might not be the same as the original csv, so if the original source is csv then it would be better to post some of it.

[per isakson]

Obviously, something differs between my, CsvExample.csv, and the csv-file that @Jeremy tries to read. I attach mine here. That is a sample with two rows. Maybe the real file contains thousand rows and the error occurs while reading the fifth.

@Jeremy, Add 'ReturnOnError',false to the textscan statement. That should give some information on why textscan fails.

[Jeremy]

Funny story...the thing finally worked as advertised. I am at a complete loss to explain how/why, though. The system that gives me raw data is down so I'm stuck with what I currently have (which doesn't do much for troubleshooting), but I added the 'ReturnOnError' string to 'textscan' and will see what happens with a fresh data set. Either way, you both have given me some outstanding help and I'm extremely grateful!

Answer 3

[Sarah Palfreyman]

You can also use Text Analytics Toolbox for this workflow.