MATLAB: Putting a variable string into a comma delimited string

cell arraycomma delimitedstringstrjoin

Hi,

I am trying to insert an array of numbers and strings into a sql table. I have gotten it to work by using triple quotes around the string I want, but I want this string to be defined by a variable rather than fixed. I am trying to get this output from strjoin :

'285026, 'TC 02 13', 17.3, 17.1, 16.0, 18.0, 16.0, 18.0, 12, 13, 0.07, 0.03'

My code is as follows:

TCListBox=[TCListG1; TCListG2; TCListG3; TCListG4];
TC = TCListBox{i};
Inserted = [test_no, TC, frz, melt, freezelowpass, freezehighpass, meltlowpass, melthighpass,...
    npfrz, npmelt, frzslope, meltslope];
strjoin(Inserted, ', ')

Where TCListGn are cell arrays of strings and all the other variables besides TC are numbers.

I am using R2015a.

Best Answer

Wrong tool for the job; while it can write flexible delimiters, I don't see any way to make it write the surrounding quotes where needed...revert to the old standby--

>> test_no=285026; TC='TC 02 13';frz=17.3;melt=17.1;freezelowpass=16.0;freezehighpass=18.0;meltlowpass=16.0;melthighpass=18;npfrz=12;npmelt=13;frzslope=0.07;meltslope=0.03;
fmt=['%d,''%s'',' repmat('%.1f,',1,6) repmat('%d,',1,2) '%.2f,','%.2f'];
s=sprintf(fmt,test_no, TC, frz, melt, freezelowpass, freezehighpass, meltlowpass, melthighpass, ...
                                                                  npfrz, npmelt, frzslope, meltslope)
s =
  '285026,'TC 02 13',17.3,17.1,16.0,18.0,16.0,18.0,12,13,0.07,0.03'
>>

Related Solutions

MATLAB: Inserting Data into SQL database table without database toolbox

You have

Inserted = {acell, TC, frz, melt, frzminpass, frzmaxpass, meltminpass, meltmaxpass,...
  npfrz, npmelt, frzslope, meltslope};

So Inserted is a cell array. Then:

sqlinsert = ['INSERT INTO cct_pcm_test_results (acell; TC; frz; melt; frzminpass; frzmaxpass; meltminpass; meltmaxpass; npfrz; npmelt; frzslope; meltslope)' ...
  'VALUES ' (Inserted)];

which concatenates a char vector with a cell array (note that the () brackets around Inserted don't do anything) and creates a cell array with one more element. It does not insert the content of the cell array into the char vector. Indeed, as the error tells your there's no execute method that takes cell arrays as inputs.

You will have to use sprintf or the newer compose to build your SQL. Possibly, the simplest is to convert your cell array to string to let matlab do the type conversion automatically, then use compose and strjoin:

sqlvals = string(Inserted);  %use default conversion from whatever is Inserted to string. May not always be right
sqlinsert = compose(['INSERT INTO cct_pcm_test_results (acell; TC; frz; melt; frzminpass; frzmaxpass; meltminpass; meltmaxpass; npfrz; npmelt; frzslope; meltslope), ...
                     ' VALUES (%s)'], ...
                    strjoin(sqlval, ', '));

The above should work if the values are numeric but will fail if the values are text since it doesn't insert the '' around the values.

Also, I'm not sure you can separate the fields by ;. It's normally a ,.

MATLAB: Using textscan to skip specific rows

Second attempt: this time we will first convert the awful file format into a standard CSV format with one header line, no empty rows, no quoted numbers, and no repeated headers. I used regexprep and simply defined appropriate regular expressions:

fnm = 'events file.csv';
str = fileread(fnm);
rgx = {'\s*\n\s*"type"[a-z",]+','"(\d+)\s*"'};
rep = {'','$1'};
str = regexprep(str,rgx,rep);

You could also save str if you want an improved CSV file. Now we can easily extract the data from this improved CSV string:

fmt = '%f%*q%f%f%f';
opt = {'HeaderLines',1, 'CollectOutput',true, 'Delimiter',','};
C = textscan(str,fmt,opt{:});
M = C{1}

giving:

M =
        92       14287           0           1
        20       16459           0           2
        14       19147           0           3
        14       20646           0           4
        14       22047           0           5
        14       24431           0           6
        14       26844           0           7
        14       28819           0           8
        14       30071           0           9
        14       31843           0          10
        14       34788           0          11
        14       36883           0          12
        14       38439           0          13
        14       39571           0          14
        14       40691           0          15
        14       41368           0          16
        14       42443           0          17
        20       42708           0          18
        14       44955           0          19
        14       46092           0          20
        14       47771           0          21
        14       48632           0          22
        14       52780           0          23
        14       54312           0          24
        14       56616           0          25
        14       58724           0          26
        14       59831           0          27
        14       61911           0          28
        14       62859           0          29
        14       64884           0          30
        14       67534           0          31
        14       68367           0          32
        14       70052           0          33
        20       70311           0          34
        14       72450           0          35
        14       73476           0          36
        14       75143           0          37
        14       76615           0          38
        20       76622           0          39

Tested on this file:

Best Answer

Related Solutions

MATLAB: Inserting Data into SQL database table without database toolbox

MATLAB: Using textscan to skip specific rows

Related Question