I'd like to convert CSV data to a Markdown table, using the first row as a header.
Input
Col1,Col2,Col3
r1c1,r1c2,r1c3
r2c1,r2c2,r2c3
Output
| Col1 | Col2 | Col3 |
|---|---|---|
| r1c1 | r1c2 | r1c3 |
| r2c1 | r2c2 | r2c3 |
Has someone already solved this problem?
Many thanks.
--
Kevin
There are various tools online, but if you are looking for something light and KM-only, then the first question with anything labelled "CSV" is what flavour ?
(In practice CSV is the name of a family of similar formats, rather than a single well-defined grammar)
Could you give us a sample ?
(your schematic above doesn't quite fill us in on issues like string vs number, quoting vs not, the handling of potential commas within a string value, etc etc)
Some for example, double-quote all strings, others, like Excel, only quote strings which contain commas.
Boolean true/false or checkbox state value are variously encoded.
Then there are the questions of how you would like values (particularly any numeric, currency or boolean values) to be aligned and formatted in the Markdown.
Excel CSV, for example, might look like this, but others will differ:
Stringish,Integerish,Currencyish,Floatish,Boolean,String again
alpha,1,£3.50 ,1.62,TRUE,ok
"beta, version",2,£5.70 ,3.67,TRUE,this
gamma,4,£3.50 ,4.78,FALSE,is
delta,"100,000",£5.70 ,3.78,FALSE,the
epsilon,"1,000,000",£3.50 ,3.89,TRUE,"last, column"
zeta,3.40E+04,£5.70 ,2.59,FALSE,for now
,,,,,
,,,,,
You would also need to choose a particular type of Markdown table. The [original Markdown spec](https://daringfireball.net/projects/markdown/syntax) suggested writing tables with HTML tags, and other approaches sketched out since then (Github flavour, MMD etc) also vary a bit in the details.
(As far as I know, the [CommonMark attempt to standardize MD](https://spec.commonmark.org/0.29/) still doesn't include tables, and assumes HTML markup too)
Maybe just show us samples of the expected inputs and outputs ?
I am looking for something light and KM-only, as I'm a KM novice and want a better understanding of the product.
I will be keying the data into a text editor. I won't have quotes or commas within a string, so my data will be simple.
Col1,Col2,Col3
r1c1,r1c2,r1c3
r2c1,r2c2,r2c3
Kind regards.
--
Kevin
simple
Numeric ?
Have you looked at any of the resources found by a google search of:
Convert CSV to Markdown table
Looks like lots of choices.
Here is one way of doing it in Keyboard Maestro, at least for the rows of simple comma-delimited string values which you show.
You can place the string in a KM variable, for example with a Copy action, and then run a custom Execute JavaScript for Automation action on it to obtain something like:
| Col1 | Col2 | Col3 |
| :--: | :--: | :--: |
| r1c1 | r1c2 | r1c3 |
| r2c1 | r2c2 | r2c3 |
MMD Table from simple comma-delimited lines.kmmacros (30.1 KB)
(() => {
'use strict';
// MMD Table (all columns center aligned)
// from lines of comma delimited string values.
// Rob Trew @2020
// Ver 0.04
// Update: 2020-10-26
// Where any lines are unexpectedly long,
// additional columns with heading labelled
// (surplus cell)
// are now created.
// main :: IO ()
const main = () => {
const
kmVariableName = 'csvRows',
txt = Application('Keyboard Maestro Engine')
.getvariable(kmVariableName);
const
rows = lines(txt).map(
x => x.split(',')
.map(cell => cell.trim())
);
return either(
// Explanatory message in dialog,
x => alert('Table from CSV')(
`${x} \n\n\t(in ${kmVariableName})`
)
)(
// or value returned to Keyboard Maestro.
// e.g. for pasting.
x => x
)(
1 < rows.length ? (
mmdTableFromRulerAndRowsLR([])(rows)
) : Left('No data rows found.')
)
};
// ------------------ MMD TABLE ------------------
// mmdTableFromRulerAndRowsLR :: [(-1|0|1)] ->
// [[String]] -> Either String String
const mmdTableFromRulerAndRowsLR = alignments =>
rows => 0 < rows.length ? (() => {
const
unknownAlignments = alignments.filter(
x => ![-1, 0, 1].includes(x)
);
return 0 < unknownAlignments.length ? (
Left(
'Alignments are drawn from {-1, 0, 1}. Found: ' +
unknownAlignments.toString()
)
) : (() => {
const
intCols = maximum(map(length)(rows)),
headerRow = rows[0],
rowsOfConsistentLength = [
headerRow.concat(
take(intCols - headerRow.length)(
repeat('(surplus cell)')
)
)
].concat(map(row => {
const d = intCols - row.length;
return 0 < d ? (
row.concat(
take(d)(repeat('-'))
)
) : row;
})(rows.slice(1))),
cols = transpose(rowsOfConsistentLength),
// Ruler expanded to full column count if shorter,
// using either last specified alignment or centering
// by default (with 0) if the alignment list is empty.
ruler = take(intCols)(alignments.concat(
replicate(intCols)(
0 < alignments.length ? (
last(alignments)
) : 0
)
)),
rulerFns = {
'-1': [justifyLeft, x => ':' + x + '-'],
'0': [center, x => ':' + x + ':'],
'1': [justifyRight, x => '-' + x + ':']
};
return Right(unlines(
map(
row => '| ' + row.join(' | ') + ' |'
)(
transpose(zipWithList(
alignment => cells => {
const
widest = maximum(
map(cell => cell
.includes('<br>') ? (
maximum(
map(length)(
splitOn('<br>')(
cell
)
))
) : cell.length)(cells)
);
return map(
rulerFns[alignment][0](widest)(' ')
)([
// Title line.
cells[0],
// Second row MMD ruler.
rulerFns[alignment][1](
'-'.repeat(
max(0)(widest - 2)
)
),
// Rest.
...cells.slice(1)
]);
}
)(ruler)(cols)))
));
})()
})() : Left('No rows to tabulate.');
// --------------------- JXA ---------------------
// alert :: String => String -> IO String
const alert = title =>
s => {
const sa = Object.assign(
Application('System Events'), {
includeStandardAdditions: true
});
return (
sa.activate(),
sa.displayDialog(s, {
withTitle: title,
buttons: ['OK'],
defaultButton: 'OK'
}),
s
);
};
// --------------------- GENERIC ---------------------
// https://github.com/RobTrew/prelude-jxa
// Left :: a -> Either a b
const Left = x => ({
type: 'Either',
Left: x
});
// Right :: b -> Either a b
const Right = x => ({
type: 'Either',
Right: x
});
// center :: Int -> Char -> String -> String
const center = n =>
// Size of space -> filler Char ->
// String -> Centered String
c => s => {
const gap = n - s.length;
return 0 < gap ? (() => {
const pre = c.repeat(Math.floor(gap / 2));
return pre + s + pre + c.repeat(gap % 2);
})() : s;
};
// either :: (a -> c) -> (b -> c) -> Either a b -> c
const either = fl =>
// Application of the function fl to the
// contents of any Left value in e, or
// the application of fr to its Right value.
fr => e => 'Either' === e.type ? (
undefined !== e.Left ? (
fl(e.Left)
) : fr(e.Right)
) : undefined;
// justifyLeft :: Int -> Char -> String -> String
const justifyLeft = n =>
// The string s, followed by enough padding (with
// the character c) to reach the string length n.
c => s => n > s.length ? (
s.padEnd(n, c)
) : s;
// justifyRight :: Int -> Char -> String -> String
const justifyRight = n =>
// The string s, preceded by enough padding (with
// the character c) to reach the string length n.
c => s => n > s.length ? (
s.padStart(n, c)
) : s;
// last :: [a] -> a
const last = xs => (
// The last item of a list.
ys => 0 < ys.length ? (
ys.slice(-1)[0]
) : undefined
)(list(xs));
// length :: [a] -> Int
const length = xs =>
// Returns Infinity over objects without finite
// length. This enables zip and zipWith to choose
// the shorter argument when one is non-finite,
// like cycle, repeat etc
'GeneratorFunction' !== xs.constructor
.constructor.name ? (
xs.length
) : Infinity;
// lines :: String -> [String]
const lines = s =>
// A list of strings derived from a single
// newline-delimited string.
0 < s.length ? (
s.split(/[\r\n]/)
) : [];
// list :: StringOrArrayLike b => b -> [a]
const list = xs =>
// xs itself, if it is an Array,
// or an Array derived from xs.
Array.isArray(xs) ? (
xs
) : Array.from(xs || []);
// map :: (a -> b) -> [a] -> [b]
const map = f =>
// The list obtained by applying f
// to each element of xs.
// (The image of xs under f).
xs => [...xs].map(f);
// max :: Ord a => a -> a -> a
const max = a =>
// b if its greater than a,
// otherwise a.
b => a < b ? (
b
) : a;
// maximum :: Ord a => [a] -> a
const maximum = xs => (
// The largest value in a non-empty list.
ys => 0 < ys.length ? (
ys.slice(1).reduce(
(a, y) => y > a ? (
y
) : a, ys[0]
)
) : undefined
)(list(xs));
// min :: Ord a => a -> a -> a
const min = a =>
b => b < a ? b : a;
// repeat :: a -> Generator [a]
function* repeat(x) {
while (true) yield x;
}
// replicate :: Int -> a -> [a]
const replicate = n =>
// A list of n copies of x.
x => Array.from({
length: n
}, () => x);
// showLog :: a -> IO ()
const showLog = (...args) =>
console.log(
args
.map(JSON.stringify)
.join(' -> ')
);
// take :: Int -> [a] -> [a]
// take :: Int -> String -> String
const take = n =>
// The first n elements of a list,
// string of characters, or stream.
xs => 'GeneratorFunction' !== xs
.constructor.constructor.name ? (
xs.slice(0, n)
) : [].concat.apply([], Array.from({
length: n
}, () => {
const x = xs.next();
return x.done ? [] : [x.value];
}));
// transpose :: [[a]] -> [[a]]
const transpose = xss => {
// If any rows are shorter than those that follow,
// their elements are skipped:
// > transpose [[10,11],[20],[],[30,31,32]]
// == [[10,20,30],[11,31],[32]]
const go = xss =>
0 < xss.length ? (() => {
const
h = xss[0],
t = xss.slice(1);
return 0 < h.length ? [
[h[0]].concat(t.reduce(
(a, xs) => a.concat(
0 < xs.length ? (
[xs[0]]
) : []
),
[]
))
].concat(go([h.slice(1)].concat(
t.map(xs => xs.slice(1))
))) : go(t);
})() : [];
return go(xss);
};
// unlines :: [String] -> String
const unlines = xs =>
// A single string formed by the intercalation
// of a list of strings with the newline character.
xs.join('\n');
// zipWithList :: (a -> b -> c) -> [a] -> [b] -> [c]
const zipWithList = f =>
// A list constructed by zipping with a
// custom function, rather than with the
// default tuple constructor.
xs => ys => ((xs_, ys_) => {
const lng = Math.min(length(xs_), length(ys_));
return take(lng)(xs_).map(
(x, i) => f(x)(ys_[i])
);
})([...xs], [...ys]);
return main();
})();
mdpre already does this - and a lot more besides. (It’s open source.)
I assume ultimately you’re making a document from the Markdown. In which case mdpre has a lot to offer you.
If you’re making slides then my companion open source app md2pptx works well with mdpre.
I missed the alignment point in my previous reply...
With mdpre you prefix the CSV to convert with =csv and suffix it with =endcsv. If you use mdpre you can do 2 further things:
=colalign l r r c so that mdpre generates the right heading alignment line.=colwidth 2 2 1 so that mdpre generates more or fewer dashes in the same heading line. md2pptx interprets this as relative width instructions. (I don’t think any other Markdown processor cares about the number of dashes.)So these have been enormously helpful in generating my own presentations.
Similar footnote re the KM macro version there:
Left Centre Right alignments are encoded as a list of values drawn from: {-1, 0, 1}. If nothing is specified, 0 (centered) is the default.
Column width and ruler length adjusts automatically to the maximum cell width of each column, plus (for each column) one space for padding to left and one for padding to right.
(Updated now above to create additional columns, named surplus cell if any rows have more cells than expected)
Many thanks to all who have responded, it's appreciated.
I look forward to exploring further.
Regards.
--
Kevin