joriszwart.nl

I code dreams™

Convert CSV and TSV

Chart.js CSV HTML JSON Markdown SQL XML Table!

Introduction

Convert from your CSV or TSV to HTML, JSON, Markdown, SQL and XML in real time. Additionally, chart and table renderings are available.

Input

Paste your CSV or TSV here.

The Code

import './chart.min.js'


const templateinline = `
 <h3>As rendered table</h3>
 <div class="table"></div>

 <h3>As HTML</h3>
 <pre class="html"></pre>

 <h3>As JSON</h3>
 <pre class="json"></pre>

 <h3>As Markdown</h3>
 <pre class="markdown"></pre>

 <h3>As XML</h3>
 <pre class="xml"></pre>

 <h3>As chart</h3>
 <div class="chart">
  <canvas id="chart0" width="1200" height="500"></canvas>
 </div>
`


// TODO use
const tab = (title, index) => `
  <input type="radio" data-tab="tab${index}" id="tab${index}" name="tabs${index}" checked>
  <label for="tab${index}">${title}</label>
`


// tabs
const template = `
<div class="tabs">
  <input type="radio" data-tab="tab1" id="tab1" name="tabs" checked>
  <label for="tab1">Table!</label>

  <input type="radio" data-tab="tab2" id="tab2" name="tabs">
  <label for="tab2">Chart<sup>beta</sup></label>

  <input type="radio" data-tab="tab3" id="tab3" name="tabs">
  <label for="tab3">HTML</label>

  <input type="radio" data-tab="tab4" id="tab4" name="tabs">
  <label for="tab4">JSON</label>

  <input type="radio" data-tab="tab5" id="tab5" name="tabs">
  <label for="tab5">Markdown</label>

  <input type="radio" data-tab="tab6" id="tab6" name="tabs">
  <label for="tab6">SQL</label>

  <input type="radio" data-tab="tab7" id="tab7" name="tabs">
  <label for="tab7">XML</label>

  <div data-pane="tab1" class="table"></div>
  <div data-pane="tab2" class="chart"><canvas id="chart0" width="1200" height="500"></canvas></div>
  <pre data-pane="tab3" class="html"></pre>
  <pre data-pane="tab4" class="json"></pre>
  <pre data-pane="tab5" class="markdown"></pre>
  <pre data-pane="tab6" class="sql"></pre>
  <pre data-pane="tab7" class="xml"></pre>
</div>
`

const renderTable = (records, headers) => {
    const table = document.createElement('table')

    // head
    const thead = table.createTHead()
    const tr = thead.insertRow()
    for (const header of headers) {
        const th = document.createElement('th')
        tr.append(th)
        th.textContent = header
    }

    // body
    const tbody = table.createTBody()
    for (const record of records) {
        const row = tbody.insertRow()
        for (const [index, value] of record.entries()) {
            const cell = row.insertCell()
            cell.innerText = value
            cell.dataset.label = headers[index]
        }
    }

    return table
}

const renderJSON = (records, headers) => JSON.stringify({ headers, records }, null, 4)

const renderMarkdown = (records, headers) => {
    const markdown = []

    // calculate maximum cell widths per column
    const headerWidths = headers.map(header => header.length)
    const mapmax = (accu, current) => current.map((cell, index) => Math.max(accu[index], cell.length))
    const widths = records.reduce(mapmax, headerWidths)

    // headers
    let delimiter = ''
    for (const [index, header] of headers.entries()) {
        markdown.push(delimiter + (header || '').padEnd(widths[index]))
        delimiter = ' | '
    }
    markdown.push('\n')

    delimiter = ''
    for (const [index, header] of headers.entries()) {
        markdown.push(delimiter + '-'.repeat(widths[index]))
        delimiter = ' | '
    }
    markdown.push('\n')

    // body
    for (const record of records) {
        let delimiter = ''
        for (const [index, value] of record.entries()) {
            markdown.push(delimiter + (value || '').padEnd(widths[index]))
            delimiter = ' | '
        }
        markdown.push('\n')
    }

    return markdown.join('')
}

const renderSQL = (records, headers) => {
    const sql = []

    // create table 
    // TODO needs field type guessing

    // headers
    const columns = headers.join(', ')
    sql.push(`INSERT INTO\n\tfoobaz (${columns})\nVALUES`)

    // rows
    let delimiter = ''
    for (const record of records) {
        const quoter = v => `'${v}'`
        const values = record.map(quoter).join(', ')
        sql.push(`${delimiter}\t(${values})`)
        delimiter = ', '
    }
    sql.push(';')

    return sql.join('\n')
}

const renderXML = (records, headers) => {
    const doc = document.implementation.createDocument(null, '')

    const items = doc.createElement('items')
    const columns = doc.createElement('columns')
    const rows = doc.createElement('rows')

    // headers
    for (const header of headers) {
        const column = doc.createElement('column')
        column.setAttribute('caption', header)
        columns.append(column)
    }

    // rows
    for (const record of records) {
        const row = doc.createElement('row')
        for (const cell of record) {
            const value = doc.createElement('value')
            value.append(cell)
            row.append(value)
        }
        rows.append(row)
    }

    doc.append(items)
    items.append(columns)
    items.append(rows)

    return new XMLSerializer().serializeToString(doc)
}

const guessDelimiter = csv => {
    const delimiters = {
        ';': 0,
        ',': 0,
        '\t': 0
    }

    for (let i = 0; i < Math.min(csv.length, 500); i++) {
        if (csv[i] in delimiters) {
            delimiters[csv[i]]++
        }
    }

    // TODO does it make sense using this data?
    // what does excel propose if you use this data for a chart?
    //     year,count,count2
    // 2011,123,50
    // 2012,125,75
    // 2013,100,25

    const count = Math.max(...Object.values(delimiters), Number.NEGATIVE_INFINITY)
    const delimiter = Object.entries(delimiters).find(c => c[1] === count)
    //    console.log(`[${delimiter[0]}]`)
    return delimiter[0]
}

const renderChart = (ctx, records, headers) => {
    const datasets = headers.slice(1).map((header, index) => ({
        label: header,
        data: records.map(r => r[index])
    }))

    const labels = records.map(r => r[0])

    console.log(datasets, labels)

    new Chart(ctx, {
        type: 'line',
        data: { labels, datasets }
    })
}


const output = document.querySelector('div.output')
output.innerHTML = template

const update = csv => {
    // don't strive to be full csv compliant, use other tools to sanity your csv first

    // TODO quoted values (needs field type guessing)
    const delimiter = guessDelimiter(csv)
    const lines = csv
        .split('\n')
        .map(line => line.split(delimiter))

    const headers = lines[0]
    let records = lines
    records.shift()

    const table = renderTable(records, headers)
    output.querySelector('.table').textContent = ''
    output.querySelector('.table').append(table)

    const html = table.outerHTML
    output.querySelector('.html').textContent = ''
    output.querySelector('.html').append(html)

    const json = renderJSON(records, headers)
    output.querySelector('.json').textContent = ''
    output.querySelector('.json').append(json)

    const markdown = renderMarkdown(records, headers)
    output.querySelector('.markdown').textContent = ''
    output.querySelector('.markdown').append(markdown)

    const sql = renderSQL(records, headers)
    output.querySelector('.sql').textContent = ''
    output.querySelector('.sql').append(sql)

    const xml = renderXML(records, headers)
    output.querySelector('.xml').textContent = ''
    output.querySelector('.xml').append(xml)

    renderChart('chart0', records, headers)
}

const input = document.querySelector('textarea')
input.addEventListener('input', e => update(e.currentTarget.value.trim()))
update(input.value.trim())