title: Extract data from CSV with Regex and convert it to JSON
body: <p>Imagine you have a table in a CSV file with this kind of layout:</p>
  
  <pre>
  name,property1 [unit1],property2 [unit2]
  name1,4.5,2.3
  name2,3.2,7.4
  name3,5.5,6.1
  </pre>
  
  <p>I need to convert each row to this kind of JSON structure (ie, for row 1):</p>
  
  <pre><code>{
      "name1": [
          {
              "properties": [
                  {
                      "property_1": "_value_",
                      "unit": "unit1"
                  },
                  {
                      "property_2": "_value_",
                      "unit": "unit2"
                  }
              ]
          }
      ]
  }
  </code></pre>
  
  <p>On top of it all, I have to explain that I am using Qt 4.7 and can't update; also, I can't install Qxt so I'm relying on <a href="https://github.com/ereilin/qt-json" rel="nofollow noreferrer">qt-json</a> for the JSON parsing/encoding. More, the CSV file is not created/maintained by me, so I can't really change it either.</p>
  
  <p>So with all of this, I realised I need a few things, so this is a kind of multiple question:</p>
  
  <ul>
  <li>how should I write the RegEx to read the <em>unit</em> in each column's header? Please note that the <em>unit</em> is enclosed in rect-parenthesis.</li>
  <li>imagine I extract both the header row and the other rows into a <code>QList&lt;QString&gt;</code>, separating each column as a string. How can I manage to sync all the bits of data in order to create the JSON structure I need on a QString? (I think I need it in a QString so I can dump each row in a different file, but I'm open to other options as well)</li>
  </ul>
  
  <p>Just one final note - I also need to this to be somewhat scalable. The CSV files on which this will be apllied are very heterogenous in column count: some have 8 columns, others have 20.</p>
  
  <p>I know it is not a good practice to post "multiquestions", but the thing is I'm feeling too overwhelmed with all of this, and because I have virtually no experience with Qt, I can't even define a plan to attack this. Hope someone can share some pointers. Thanks!</p>
  
  <p><strong>EDIT</strong>
  So, I've been thinking a little more about this and I don't actually know if this is a good idea/feasible but here is what I thought of:</p>
  
  <ul>
  <li>when going through the header row, I would check if each column string had a hit for the RegEx. If so, I would store the column index and the <em>unit</em> string in a list;</li>
  <li>then, when going through the other rows, in order to parse them into JSON, I would check in each column if it matched the index in the previous list, and if so, I would then add the <em>unit</em> to the map (as qt-json docs explains)</li>
  </ul>
  
  <p>Does this make any sense? Can anyone mock up a skeleton I can work on for this?</p>
  
  <p><strong>EDIT2</strong></p>
  
  <p>I've managed to get a few things working so far, but still not working as it should. Right now I have managed to read properly from the CSV file, but the output isn't coming out right. Can anyone share some insight? </p>
  
  <p>NOTE: the <em>processLineFromCSV</em> function returns a QStringList obtained like so: <code>QStringList cells = line.split(separator_char);</code></p>
  
  <p>NOTE2: the RegEx was obtained from <a href="https://stackoverflow.com/questions/17040766/find-substr-between-delimiter-characters-in-qt-with-regex">this</a> answer.</p>
  
  <p>NOTE3: Check below for the type of output I'm getting. Right now I think the problem relates more to the usage of the <code>qt-json</code> lib than actually the rest of the code, but any help is welcome! :)</p>
  
  <p>The code so far:</p>
  
  <pre><code>QFile file(csvfile);
  
      if (file.open(QIODevice::ReadOnly | QIODevice::Text))
      {
          bool first = true;
          QVariantMap map;
          QVariantMap propertyMap;
          QList&lt;QVariant&gt; generalList, propertiesList;
  
          while (!file.atEnd())
          {
              QString line = file.readLine();
              if(first == true){
                  headerList = processLineFromCSV(line, separator_char);
                  first = false;
              }else{
  
              QStringList cellList = processLineFromCSV(line, separator_char);
  
              int i=0;
  
              for(i; i&lt;cellList.size(); i++)
              {
                  // check the header cell for "[unit]" string
                  // returns -1 if does not have the string
                  // if it has the string, it's stored in capturedUnits[1]
                  int test = exp.indexIn(headerList.at(i));
  
                  // store the captured units in a QStringList
                  QStringList capturedUnits = exp.capturedTexts();
  
                  if(test==-1){ // if header does not have a captured unit - general column
                      QString name = headerList.at(i);
                      QString sanitizeName= name.remove(exp.capturedTexts().at(0), Qt::CaseSensitive);
                      map[sanitizeName] = cellList.at(i);
                  }
                  else{ // if header string has a captured unit - property column
  
                      QString propertyName = headerList.at(i); // extract string in header
                      QString sanitizedPropertyName = propertyName.remove(exp); //remove the unit regex from the string
                      sanitizedPropertyName.remove(QChar('\n'), Qt::CaseSensitive); // clear newlines
  
                      if(sanitizedPropertyName.startsWith('"') &amp;&amp; sanitizedPropertyName.endsWith('"'))
                      {
                          sanitizedPropertyName.remove(0,1);
                          sanitizedPropertyName.remove(sanitizedPropertyName.length(),1);
                      }
  
                      QString value =cellList.at(i); // extract string in value
                      QString sanitizedValue = value.remove(QChar('\n'), Qt::CaseSensitive); // clear newlines
  
                      if(sanitizedValue.startsWith('"') &amp;&amp; sanitizedValue.endsWith('"'))
                      {
                          sanitizedValue.remove(0,1);
                          sanitizedValue.remove(sanitizedValue.length(),1);
                      }
  
                      propertyMap[sanitizedPropertyName]= sanitizedValue; // map the property: value pair
                      propertyMap["unit"] = capturedUnits.at(1); // map the unit: [unit] value pair
  
                      QByteArray general = QtJson::serialize(map); // serialize the pair for general column
                      QByteArray properties = QtJson::serialize(propertyMap); // serialize the pair for property column
  
                      QVariant genVar(general);
                      QVariant propVar(properties);
  
                      generalList.append(genVar);
                      propertiesList.append(propVar);
                  }
              }
          }}
          QByteArray finalGeneral = QtJson::serialize(generalList);
          QByteArray finalProperties = QtJson::serialize(propertiesList);
  
          qDebug() &lt;&lt; finalGeneral;
          qDebug() &lt;&lt; finalProperties;
  
  
          file.close();
      }
  </code></pre>
  
  <p>The ouput:</p>
  
  <pre><code>"[
      "{ \"name\" : \"name1\" }",
      "{ \"name\" : \"name1\" }",
      "{ \"name\" : \"name2\" }",
      "{ \"name\" : \"name2\" }",
      "{ \"name\" : \"name3\" }",
      "{ \"name\" : \"name3\" }"
  ]" 
  "[
      "{ \"property1 \" : \"4.5\", \"unit\" : \"unit1\" }",
      "{ \"property1 \" : \"4.5\", \"property2 \" : \"2.3\", \"unit\" : \"unit2\" }",
      "{ \"property1 \" : \"3.2\", \"property2 \" : \"2.3\", \"unit\" : \"unit1\" }",
      "{ \"property1 \" : \"3.2\", \"property2 \" : \"7.4\", \"unit\" : \"unit2\" }",
      "{ \"property1 \" : \"5.5\", \"property2 \" : \"7.4\", \"unit\" : \"unit1\" }",
      "{ \"property1 \" : \"5.5\", \"property2 \" : \"6.1\", \"unit\" : \"unit2\" }"
  ]"
  </code></pre>