Import data with 1000 seperator

Hello,

I want to import data from a csv file with numeric values separated by a thousand separator.

Ex: 1 000,00

I use readtable function with option opts = setvaropts(opts, "ACC_Z", "ThousandsSeparator", " ");

But it doesn't work and I get the value 1 instead of 1000 in the imported table.

Should I do anything else?

Thanks.

[dpb]

You would also have to set the whitespace values to the null string.

I wouldn't bet on that more than an outside chance.

Attach the file to let somebody have one to poke at without having to create a sample on their own...

[Dyuman Joshi]

You won't get a thousands separator in numeric data. You will have to use one of the text data types.

[Stephen23]

"1 000,00"

Note also the decimal comma.

[dpb]

That's the Q? -- is the posted example one or two elements of "1 000,00" --> 1000.00 or as OP says it is a CSV file, "1 000,00" --> 1000, 0??? As posted, it's indeterminate. One may presume one or the other and the first is more likely to be the case and it isn't actually a real CSV file format, but just unable to tell for sure without more info...and, of course, posting the actual file is always the best way to resolve such conundrums.

Answer 1

[dpb]

It's still questionable what is the content of actual file, but it appears one can make the OP's presumption come true...now whether that's what the file content really is or not is anybody's guess.

l="1 000,00";               % assume a record
writelines(l,'Test.csv')    % create a file
type Test.csv               % see what we made for sure

1 000,00

opt=detectImportOptions('Test.csv') % create an import object

opt = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Var1', 'Var2'}
                VariableTypes: {'char', 'double'}
        SelectedVariableNames: {'Var1', 'Var2'}
              VariableOptions: [1-by-2 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [1 Inf]
            VariableNamesLine: 0
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

opt.Whitespace='';                                              % the blank isn't whitespace here...
opt.VariableTypes(1)={'double'};                                % try to convince it should be a number besides
opt=setvaropts(opt,{'Var1'},'ThousandsSeparator'," ");          % but a separator.
tD=readtable('Test.csv',opt)                                    % and see what happens...

tD = 1×2 table
    Var1    Var2
    ____    ____

    1000     0  

[Stephen23]

writelines("1 000,00",'test.csv')
type test.csv

1 000,00

t = readtable('test.csv', "Delimiter",",", "ThousandsSeparator"," ")

t = 1×2 table
    Var1    Var2
    ____    ____

    1000     0