How can I estimate the time required by textscan and the size of the output?

Hello,

I am running Matlab 2013b on Windows 7. I have 8 GB RAM memory and a I set the swap file to 20 GB.

I am trying to read a relatively large txt file that is tab separated. The size of the file on the hard disk is a little over 2 GB. There are 6 columns and approx. 64 million rows in the file. The entries are mixed (strings and numbers with some missing values).

At this point I am using:

textscan(fid,repmat('%s',1,6),'delimiter','\t');

It is running for about 4 hours now using about 6.5 GB RAM.

1. I would like to know how can I estimate the time it takes to read the file and the size of the output.

2. After it is done I would like to extract the numerical values from the resulting cell matrix and save that to a .mat file. Any idea how long that would take?

3. Is there any better way of doing this? If I could extract from the file a matrix with the numerical values only (setting everything else to NaN) it would be great.

Thanks!

[per isakson]

"using about 6.5 GB RAM." &nbsp How did get you get this number? I would have thought close to 8GB.

[Alexandru]

Just from task manager in Windows...

[per isakson]

Strange! Could there be something using memory that the task manager doesn't report on? Is your system 64bit?

Answer 1

[per isakson]

I made an experiment with R2013a, 64bit, Win7, 8GB RAM, 9GB page file size and a mechanical HD

• created a file with "6 columns and approx. 64 million rows"
• read a piece of the file with textscan after restart of Matlab
• Monitored the memory usage with the Windows Task Manager

    Elapsed time is 192.597879 seconds.
    >> cac{1}(1:3)
    ans = 
        'Col1'
        'Col1'
        'Col1'
    >> cac{2}(1:3)
    ans =
         1
         1
         1
    >> cac{6}(1:3)
    ans =
         3
         3
         3
    >> whos cac
      Name      Size                 Bytes  Class    Attributes
      cac       1x6             1920000672  cell

where code is

    fid = fopen('c:\tmp\test.txt');
    M   = cumsum(ones( 3, 64e6 ), 1 );
    fprintf( 'Col1\t%4.1f\tCol2\t%4.1f\tCol3\t%4.1f\n', M )
    fclose( fid );
    tic
    fid = fopen('c:\tmp\test.txt');
    cac = textscan( fid, '%s%f%s%f%s%f', 5e6, 'Delimiter', '\t' );
    fclose( fid );
    toc

1. start of Matlab
2. running of experiment

Results

• Reading and parsing 5 million rows took three minutes and peaked at 4.8GB RAM usage
• 5 million rows produced a 2GB variable, cac, in Matlab.
• An experiment to read the entire file showed that speed decreased drastically when there was no more free physical RAM. (I killed the process.) 8GB RAM would allow effective reading of nearly ten million rows.

[Alexandru]

Thanks, I guess I need to break the file in batches as it's been running for 8 hours now and still it hasn't ended...

How do you tell textscan to read from row 1 to row say 5,000,000 then from 5,000,001 to 10,000,000 etc.

I don't mean the for loop, I mean how do you specify the range, is that in the options for this function and I missed it?

Also how do you determine how many rows there are exactly (for the last step in the loop)?

[per isakson]

See (nothing new of importance compared to R2013a)

• Import Large Text File Data in Blocks
• Moving within a File describes in some detail how to manipulate the file pointer.

The size of the block, which is read, is the smaller of the number specified and the rest of the file.

A loop around &nbsp cac = textscan(fid,format_spec, 5e6, 'Delimiter','\t'); will do it.

[Alexandru]

Thanks!

Since I don't know what characters show up in the text I cannot skip them and I cannot collect the output. However breaking into blocks and reading as strings works great.

I never before realized that the time to read the file increases exponentially. I tested my file and the results were:

1e3 rows - 0.035627 seconds.

1e4 rows - 0.097302 seconds.

1e5 rows - 1.159013 seconds.

1e6 rows - 30.041312 seconds.

[per isakson]

That's surprising results! It doesn't make sense to me. Here is another one that doesn't make sense.

    %s%f%s%f%s%f
    Elapsed time is 1.670506 seconds.
    Elapsed time is 0.001105 seconds.
    Elapsed time is 0.007156 seconds.
    Elapsed time is 0.066814 seconds.
    Elapsed time is 0.747540 seconds.
    Elapsed time is 13.118503 seconds.
    %s%s%s%s%s%s
    Elapsed time is 0.754963 seconds.
    Elapsed time is 0.001459 seconds.
    Elapsed time is 0.009222 seconds.
    Elapsed time is 0.090024 seconds.
    Elapsed time is 1.193596 seconds.
    Elapsed time is 36.568043 seconds.
    >>

where

    fid = fopen('c:\tmp\test.txt');
    M   = cumsum(ones( 3, 64e6 ), 1 );
    fprintf( 'Col1\t%4.1f\tCol2\t%4.1f\tCol3\t%4.1f\n', M )
    fclose( fid );
    disp( '%s%f%s%f%s%f' )
    for jj = 1 : 6
        tic
        fid = fopen('c:\tmp\test.txt');
        cac = textscan( fid, '%s%f%s%f%s%f', 10^jj, 'Delimiter', '\t' );
        fclose( fid );
        toc
    end
    disp( '%s%s%s%s%s%s' )
    for jj = 1 : 6
        tic
        fid = fopen('c:\tmp\test.txt');
        cac = textscan( fid, '%s%s%s%s%s%s', 10^jj, 'Delimiter', '\t' );
        fclose( fid );
        toc
    end

Answer 2

[dpb]

Don't know that there is any metric to predict run time other than testing as it's so dependent upon the machine characteristics, not just size.

Two things I can think of to try --

a) Use the specific format for the data file -- strings for string, numeric for numbers. Skip ('%*s' for example to skip a string field) any fields that aren't mandatory. Use "'collectoutput',true" to gather the various types together. This will bypass a subsequent conversion step.

b) Use the feature of textscan to process the file in pieces -- say 1 to a few MB roughly per pass.

[Alexandru]

The same column sometimes contains strings, sometimes numbers would skipping the field still work?

[per isakson]

Skipping columns with %*s works regardless of which characters are in the column.

[Alexandru]

Yes, but what I am saying is that I want the numeric part of each column.

If a column is ['char'; 23.1; 16; ] I need to extract [NaN; 23.1; 16; NaN]. Hope it's clear now...

[per isakson]

Yes, if it is a reasonable number of different string constants and you know them beforehand.

    >> cac = textscan( 'char; 23.1; 16', '%f', 'Delimiter', ';' ...
                    ,  'treatAsEmpty', {'char'} );

    >> cac{:}
    ans =
           NaN
       23.1000
       16.0000

nan regardless of case is converted to "NaN"