Parse CSV (Comma Separated Values) Line [Example]

MACRO:   Parse CSV Line [Example]

VER: 3.0    2016-11-13 ~~~


Parse CSV Line [Example].kmmacros (10.0 KB)


PURPOSE: Extract One Line of CSV Data, & Set KM Variables
A KM Variable will be set for each CSV Field Found
RETURN list of KM Variable names and values

VER: 2.0 2016-08-17

METHOD: Use JXA script to find CSV fields using:

  • Default RegEx: (.+?)(?:, ?|$)
    • Comma MUST immediately follow value
    • An optional space may appear after comma
  • RegEx for values in quotes: "([^"]*)"

* @JMichaelTX
* Based on macro/script by @DanThomas


  • Change the below two "Set Variable" Actions to use your data

See Header Comments in below script for more info.

Example Results

# Script



PURPOSE:  Extract One Line of CSV Data, & Set KM Variables
          RETURN List of KM Variables and Values

VER: 3.0    2016-11-14

  • @DanThomas   -- Ver 1 which extracted and returned the data, one line per item
  * @JMichaelTX -- Ver 3 which ADDED Set of KM Variables, and get RegEx from KM
  • csvLine
  • csvVarPrefix
  • csvRegEx
  • One Variable for each match found of CSV data
  • Variable Name:  csvVarPrefix + sequence#
    (Example:  TEST_myVar_2)
  • Search Variable using Regular Expression



(function() {
  'use strict';

  try {
    var kme = Application("Keyboard Maestro Engine");
    var input       = kme.getvariable("csvLine")  // || '"TEST one","TEST two","TEST three"';
    var kmVarPrefix = kme.getvariable("csvVarPrefix") || 'TEST_myVar_';
    var regExStr    = kme.getvariable("csvRegEx") || "(.+?)(?:, ?|$)";
    if (!input)
      throw Error("Variable '" + csvLine + "' is empty");

    var regexp = new RegExp(regExStr,"g");
    var matches;
    var matchList = [];
    //while ((matches = regexp.exec(input))[1] !=="") {
    while ((matches = regexp.exec(input)) !== null) {
    var kmVar = ""
    var numMatches = matchList.length;
    console.log("Number of Matches: " + numMatches);
    for (var iMatch = 0; iMatch < numMatches; iMatch++) {
      kmVar = kmVarPrefix + (iMatch+1).toString();  // ADD a numeric suffix to variable name
      console.log(kmVar + ": " + matchList[iMatch])
      //--- SET THE KM VARIABLE ---
      kme.setvariable(kmVar, { to: matchList[iMatch] });
    } // END for matchList

    return    // matchList.join("\n")
  } catch (e) {
    return "[**ERROR**] " + e.message;

2 posts were split to a new topic: How Do I Use Data from CSV Text to Set Headings & Variables?

Excellent, thank you, Michael!

I'm new to this and trying to use Parse CSV Line macro. So sorry if not a great question. I have this CSV data:

"Cue #","IN","Temp","Status"
"1m1","1:00:00:00","","To Write"

I'm trying to create a macro that sets variables that show that the status of "1m1" is "To Write". Can I do this with this macro?

Could you zoom back a bit and give some context ?

What is the problem that you need to solve ?

Yes. I'm trying to write a macro that will create variables from CSV data. The attached file is CSV file with two lines. The first line is the rows or header. I want to make a MK macro that will result in '%variable%cue_number% = 1m1' and '%variable%status% = To Write'.

This is a very small and simple example to make the point. once this works I want to expand the macro to accommodate many more rows in the CSV file. I also want the macro to eventually work regardless of the original order of the the rows. For example, if Status were the first row (it is currently the second) , the variable should still end up the same. Is that a better explanation? I really appreciate the help!
Keyboard Maestro Prototype - Cue Log - (992 Bytes)

And btw.. I have your "Parse CSV Line [Example]" working and I understand it pretty well!

I'm trying to write a macro that will create variables

More broadly, what is that macro producing ?

(or what problem is it aiming to solve ?)

The XY Problem

XY problem - Wikipedia

Depending on the broader goals and context, it may also be worth looking at Sqlite, if, for example you want to produce a series of rows:

Thanks so much for this info! I will think about this in terms of 'XY Problem' . (I have found that often figuring out how to ask the question properly is the fastest way to solving it).

And I will check out sqlite3. Thanks so much and enjoy the holiday.

1 Like