Import and format CSV file

Hi,

I have problems to import and convert formats in matlab, I have a CSV file containing six columns, are in separate bank format for point(.), as follows:

Column 1 = Date and Time

Column 2 = opening

Column 3 = Maximum

Column 4 = closure

Column 4 = min

Column 5 = volume

clc;
clear all;
filename='D:\BD\Bars.csv'; 
DataHora= xlsread(filename,'','A:A'); %%tentativa 1
Abertura= xlsread(filename,'','B:B'); %%tentativa 1
%[DataHora Abertura ] = csvimport(filename, 'columns', {'A', 'B'});%%tentativa 2

disp(DataHora);
disp(Abertura);

======= presented results======

NaN;
NaN;
NaN;
NaN;
......

2008.01.02 00:00:00  33.18  33.46  32.76  33.18  7590
2008.01.03 00:00:00  33.06  33.27  32.53  32.53  6481
2008.01.04 00:00:00  32.66  32.93  30.85  31.04  14691
2008.01.07 00:00:00  31.07  31.16  29.99  30.13  11876
2008.01.08 00:00:00  30.62  32.27  30.61  31.79  11812
2008.01.09 00:00:00  31.69  32.39  31.37  32.39  9517
2008.01.10 00:00:00  32.41  32.6  31.41  32.07  12151
2008.01.11 00:00:00  31.73  32.14  31.1  31.1  8433
2008.01.14 00:00:00  31.43  31.54  30.77  31.04  7677
2008.01.15 00:00:00  30.62  30.77  29.55  29.67  17279

[Image Analyst]

You forgot to attach the file and you forgot to state your problems. Try this link.

Answer 1

[per isakson]

"bank format for point(.)," and "converts the period (.) to a comma (,) code to import" &nbsp I don't fully understand. The screen-clip does it show a piece of the csv-file?

Your csv-file uses for

• list separator - I guess ','
• decimal separator - I guess '.'
• thousand separator - ????

Try

    >> out = cssm('cssm.txt')
    out =
       1.0e+05 *
        7.3456    0.0005    0.0005    0.0005    0.0005    0.0206
        7.3459    0.0005    0.0005    0.0005    0.0005    0.0277
        7.3462    0.0005    0.0005    0.0005    0.0005    0.0229
        7.3465    0.0005    0.0005    0.0005    0.0005    0.0526
        7.3468    0.0005    0.0005    0.0005    0.0005    0.0301
        7.3478    0.0005    0.0005    0.0005    0.0005    0.0333

where

    function    out = cssm( filespec ) 

        fid = fopen( filespec );
        cac = textscan( fid, '%s%s%f%f%f%f%f'                   ...
                      , 'Delimiter', ',', 'CollectOutput', true );
        fclose( fid );

        str = cell2mat(cac{1});
        sdn = datenum( str, 'mm/dd/yyyyHH:MM');

        out = cat( 2, sdn, cac{2} );    
    end

and where cssm.txt contains

    03/01/2011,00:00,50.38,51.62,50.38,50.91,2061
    04/01/2011,00:00,51.42,51.42,49.81,50.21,2771
    05/01/2011,00:00,49.60,50.82,49.60,49.81,2287
    06/01/2011,00:00,49.95,50.05,48.35,48.60,5259
    07/01/2011,00:00,48.60,48.91,47.90,48.16,3012
    10/01/2011,00:00,48.47,48.89,47.57,47.63,3333

(thanks to OCR)

Answer 2

[ilson]

hi,

managed to solve. but now need to convert the units during conversion. as attached image. converts the period (.) to a comma (,) code to import

if true
  % code
fid = fopen('I:\BD\xxx.csv');
if fid>0 
   bd = textscan(fid,'%s %s %s %s %s %s %s','Delimiter',',','HeaderLines',0);
   fclose(fid);
end   
end

Answer 3

[Thorsten]

You can use

 num= 48.68;
 num1= floor(num);
 num2= (num - num1)*1000;