How to seperate column correctly using readtable?

Question

JamJan 2021-6-3

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/846895-how-to-seperate-column-correctly-using-readtable

编辑： Stephen23 2021-6-10

Hi,

I have been using readtable for quite some while, but for some reason I started running into problems a few days ago. Attached find an example of a .csv file. I would like to split the values from:

FLIP001,111111.1234567890,"2001-10-29 01:01:00","2001-10-29 01:01:00",7

to a table consisting of:

Var1; Var2; Var3; Var4; Var5

FLIP001; 111111.1234567890; "2001-10-29 01:01:00"; "2001-10-29 01:01:00"; 7

(Semicolons are seperating the columns here)

For some reason I do not manage to use the delimiter correctly. Can someone help me to split these variables correctly using, for instance, readtable or any other way?

Thank you!

2 个评论
显示无隐藏无

Rik 2021-6-10

I recovered the removed content from the Google cache (something which anyone can do). Editing away your question is very rude. Someone spent time reading your question, understanding your issue, figuring out the solution, and writing an answer. Now you repay that kindness by ensuring that the next person with a similar question can't benefit from this answer.

Stephen23 2021-6-10

编辑：Stephen23 2021-6-10

Original question by JamJam retrieved from Bing Cache (the CSV file could not be retrieved):

https://web.archive.org/web/20210610120610/https://cc.bingj.com/cache.aspx?q=www.mathworks.com%2Fmatlabcentral%2Fanswers%2F846895&d=85416763367&mkt=en-WW&setlang=de-DE&w=YCSz1hbIfp0sHYxJhJDFtOPDkUk3Dho4

http://web.archive.org/web/20210610120806/https://www.mathworks.com/matlabcentral/answers/846895-how-to-seperate-column-correctly-using-readtable

How to seperate column correctly using readtable?

I have been using readtable for quite some while, but for some reason I started running into problems a few days ago. Attached find an example of a .csv file. I would like to split the values from:

FLIP001,111111.1234567890,"2001-10-29 01:01:00","2001-10-29 01:01:00",7

to a table consisting of:

Var1; Var2; Var3; Var4; Var5

FLIP001; 111111.1234567890; "2001-10-29 01:01:00"; "2001-10-29 01:01:00"; 7

(Semicolons are seperating the columns here)

For some reason I do not manage to use the delimiter correctly. Can someone help me to split these variables correctly using, for instance, readtable or any other way?

Thank you!

请先登录，再进行评论。

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

Answer 1

Star Strider 2021-6-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/846895-how-to-seperate-column-correctly-using-readtable#answer_715935

This appears to work —

C1 = readcell('https://www.mathworks.com/matlabcentral/answers/uploaded_files/640960/FakeCsv.csv');
CS1 = cellfun(@(x)strsplit(x,','), C1, 'Unif',0);
cellrows = cellfun(@(x)size(x,2),CS1);
CS2 = reshape([CS1{:}], cellrows(1), []).';
T1 = cell2table(CS2,'VariableNames',compose('Var%d',1:size(CS2,2)))
T1 = 5×5 table
       Var1                Var2                       Var3                         Var4                Var5  
    ___________    _____________________    _________________________    _________________________    _______

    {'FLIP001'}    {'111111.1234567890'}    {'"2001-10-29 01:01:00"'}    {'"2001-10-29 01:01:00"'}    {'7'  }
    {'FLIP001'}    {'111111.1234567890'}    {'"2001-10-29 01:01:00"'}    {'"2001-10-29 01:01:00"'}    {'8'  }
    {'FLIP001'}    {'111111.1234567890'}    {'"2001-10-29 01:01:00"'}    {'"2001-10-29 01:01:00"'}    {'16' }
    {'FLIP001'}    {'111111.1234567890'}    {'"2001-10-29 01:01:00"'}    {'"2001-10-29 01:01:00"'}    {'128'}
    {'FLIP001'}    {'111111.1234567890'}    {'"2001-10-29 01:01:00"'}    {'"2001-10-29 01:01:00"'}    {'7'  }

Completing this with the conversions:

T1.Var2 = str2double(T1.Var2);
T1.Var3 = datetime(T1.Var3, 'InputFormat','"yyyy-MM-dd HH:mm:ss"');
T1.Var4 = datetime(T1.Var4, 'InputFormat','"yyyy-MM-dd HH:mm:ss"', 'InputFormat','"yyyy-MM-dd HH:mm:ss"');
T1.Var5 = str2double(T1.Var5)
T1 = 5×5 table
       Var1           Var2               Var3                    Var4            Var5
    ___________    __________    ____________________    ____________________    ____

    {'FLIP001'}    1.1111e+05    29-Oct-2001 01:01:00    29-Oct-2001 01:01:00      7 
    {'FLIP001'}    1.1111e+05    29-Oct-2001 01:01:00    29-Oct-2001 01:01:00      8 
    {'FLIP001'}    1.1111e+05    29-Oct-2001 01:01:00    29-Oct-2001 01:01:00     16 
    {'FLIP001'}    1.1111e+05    29-Oct-2001 01:01:00    29-Oct-2001 01:01:00    128 
    {'FLIP001'}    1.1111e+05    29-Oct-2001 01:01:00    29-Oct-2001 01:01:00      7 

There does not appear to be a more straightforward way to parse that. The detectImportOptions options may work, however I have found those to be difficult to get right. so I went for a more direct approach.

.