Parsing the Ms access data base

Question

Gopalakrishnan venkatesan 2017-3-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/332267-parsing-the-ms-access-data-base

评论： Guillaume 2017-3-29

I have a database with two tables. I tried to extract the data from the tables and i was able to extract also using the command below,

rs=invoke(hopen,'OpenRecordset','Data');  
fieldlist=get(rs,'Fields');
ncols=get(fieldlist,'Count');
for c=1:double(ncols)
       fields{c} = get(fieldlist,'Item',c-1);
       resultset.columnnames{c} = get(fields{c},'Name');
  end;
  nrecs=0;
   while get(rs,'EOF') == 0
        nrecs = nrecs + 1;
        for c=1:ncols
           resultset.data{nrecs,c} = get(fields{c},'Value');
        end;
        invoke(rs,'MoveNext');
   end

Only problem is when I have a multiple values in the cell i can able to extract only first element in the cell.

For example,

in the above figure, the column carsSelected consists of multiple value in the cell. In this case i cannot able to extract all the elements in the cell? How can I do this?

Thanks a lot

4 个评论
显示 2更早的评论隐藏 2更早的评论

Guillaume 2017-3-28

Can you show the design view of that Color table?

Gopalakrishnan venkatesan 2017-3-28

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2017-3-28

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/332267-parsing-the-ms-access-data-base#answer_260717

在 MATLAB Online 中打开

Well, I learnt something today. Access does allow multivalued fields. However, it does so by hiding much of the machinery that you'd use in a normal database design (that is it hides the table modeling the relationship together with the queries required to retrieve the records).

It looks like you're using DAO to connect to the database. When I first tried, using the older ADO technology, the value property of the field is the display string (i.e. '1; 2; 3; 4'). However, with the newer DAO, according to MSDN the value property of a multivalued field of a recordset is itself a recordset. So to retrieve all the values, you'll need another loop iterating over this child recordset.

So I assume this will work:

    for c=1:ncols
           if fields{c}.IsComplex  %true for multivalued
               rs = fields{c}.Value;
               value = [];
               rs.MoveFirst;
               while ~rs.EOF
                   subfield = get(rs.Fields, 'Item', 0);
                   value = [value, subfield.Value];
               end
               resulset.data{nrecs, c} = value;
           else
               resultset.data{nrecs,c} = fields{c}.Value;
           end
     end

Note that you can replace most of the get(...) access by direct dotted access as I've done above. The exception appears to be the Item property that matlab refuses to see other than with get.

2 个评论
显示无隐藏无

Gopalakrishnan venkatesan 2017-3-29

在 MATLAB Online 中打开

Thats great. Works really good. Thanks a lot

one step was missing. I have added it now,

    for c=1:ncols
           if fields{c}.IsComplex  %true for multivalued
               rs = fields{c}.Value;
               value = [];
               rs.MoveFirst;
               while ~rs.EOF
                   subfield = get(rs.Fields, 'Item', 0);
                   value = [value, subfield.Value];
                   rs.MoveNext
               end
               resulset.data{nrecs, c} = value;
           else
               resultset.data{nrecs,c} = fields{c}.Value;
           end
     end