How to read an excel /csv files with columns that have both text and numbers?

Everytime I try to use readcell , readtable.. I get one or alll of the following problems:

1. Numeric columns get merged into one cell array ex : {1.5,2.5} vs them being in two unique cells
2. Additional columns that dont exist in my csv/xlsx files with 1x1 missing filled in
3. Nan for string entries

I saw online that a column with text and numeric values dont mix well. Anyone have any suggestions?

I am also trying to find a specific string value index (xdist_mm,Power_watts) for each file to then import the data under each of these headers into a seperate array for analysis. I tried strfind and contains without much sucess)

Thank you

Answer 1

[Stephen23]

fnm = 'sample.csv';
tmp = readcell(fnm, 'Delimiter',',');
idx = cellfun(@ischar,tmp(:,1));
assert(all(diff(idx)<1))
nhl = nnz(idx)-1;
tbl = readtable(fnm, 'Delimiter',',', 'NumHeaderLines',nhl)

tbl = 123x2 table
    xdist_mm    Power_watts
    ________    ___________

        65      1.5135e-05 
      64.9      2.0816e-05 
      64.8      2.3005e-05 
      64.7      2.5731e-05 
      64.6      2.8835e-05 
      64.5      3.2803e-05 
      64.4      3.7081e-05 
      64.3      4.2204e-05 
     64.27      4.4028e-05 
     64.17        5.03e-05 
     64.07       6.312e-05 
     63.97      6.6702e-05 
     63.87      7.8218e-05 
     63.77      9.2614e-05 
     63.67      0.00011157 
     63.57      0.00013425 

hdr = cell2struct(tmp(1:nhl,2),tmp(1:nhl,1))

hdr = struct with fields:
                  Distance_yaw: 1502
                         Units: 'Meter'
               Scan_start_time: '2024-06-26_09-35-52'
           Analog_filter_value: 4
          Digital_filter_value: 10000
                   Filter_type: 3
                     ID_string: 'NOreo 2936-R v1.3.4 04/27/20 SN9520'
                  Responsivity: 4.2361e-04
                    Zero_value: 0
                       Channel: 2
                   Test_number: 1503
              Z_stage_start_mm: 100
              Y_stage_start_mm: 2.2800
                    SCC_number: 107
               alpha_test1_deg: 2.6275
               alpha_test2_deg: -6.1321
           Z_stage_W1_start_mm: 100
           Y_stage_W1_start_mm: 2.2800
          X_stage_W1_center_mm: 59.9700
        Rot_stage_W1_start_deg: -17.3725
           Z_stage_W2_start_mm: 10
           Y_stage_W2_start_mm: 3
          X_stage_W2_center_mm: 5
        Rot_stage_W2_start_deg: -26.1321
                Pin_grating_ID: 0
           Wavelength_test_num: 1
       Baseplate_configuration: 1
          Instrument_rev_level: 1
           Y_stage_IS_start_mm: 46.1210
         Z_slit_to_IS_shift_mm: -0.9750
         X_slit_to_IS_shift_mm: -0.9460
         Analog_filter_setting: 3
         Digital_filter_counts: 100
              Filter_selection: 3
    X_stage_IS_W1_increment_mm: 3
           loop_Z_increment_mm: 0.2000
                 loop_Z_number: 11
             baseplate_hole_ID: NaN
     stage_home_slit_offset_mm: 100
         detection_hole_pin_ID: 0
            source_hole_pin_ID: 0
             X_stage_center_mm: 59.9700
           Rot_stage_start_deg: -17.3725
              X_stage_close_mm: 65
                X_stage_far_mm: 53
           z_vertex_to_slit_mm: 0