dtchebotarev.github.io/sql-values.html at master · DTchebotarev/dtchebotarev.github.io · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
<!DOCTYPE html>
<html>
  <head>
    <link rel="stylesheet" href="https://cdn.simplecss.org/simple.min.css">
    <title>TSV to SQL values</title>
    <script type='text/javascript'>

      let final_sql;
      const INSTRUCTIONS_STRING = 'Hit ctrl/cmd + v to paste from google sheets (or any other tsv data). Do this as many times as you need.\n'

      function parseLine(text, isHeader = false) {

        const cells = text.split('\t');

        const tableRow = document.createElement('tr');
        const sqlCells = [];
        for (cell of cells) {
          let tableCell = isHeader ? document.createElement('td') : document.createElement('th');
          tableCell.innerHTML = cell;
          tableRow.appendChild(tableCell);
          let formattedCell;
          if (cell.length == 0 || cell[0] == '#') {
             formattedCell = ['NULL'];
          } else if (!isNaN(cell)) {
             formattedCell = [cell];
          }
          else {
             formattedCell = isHeader ? [cell] : ["'" + cell.replaceAll("'", "''") + "'"];
          };
          sqlCells.push(formattedCell);
        }

        const sqlLine = (isHeader ? '(' : '  (') + sqlCells.join(', ') + ')';

        return [sqlLine, tableRow];
      }

      function parseTable(text) {

        const sqlLines = [];
        const table = document.createElement('table');

        const lines = text.split('\n');

        const [parsedHeaderSQL, parsedHeaderTable] = parseLine(lines.shift(), isHeader = true);
        table.appendChild(parsedHeaderTable);

        for (line of lines) {
          const [sqlLine, tableRow] = parseLine(line);
          sqlLines.push(sqlLine);
          table.appendChild(tableRow);
        }

        final_sql = '( VALUES\n' + sqlLines.join(',\n') + '\n) AS t' + parsedHeaderSQL;
        const codeBlock = document.createElement('pre');
        codeBlock.innerText = final_sql;
        codeBlock.setAttribute('id','sql_code');

        return [codeBlock, table];
      }

      function updatePage(sql, table) {
        document.getElementById('table').innerHTML = '';
        document.getElementById('table').appendChild(table);

        document.getElementById('sql').innerHTML = '';
        document.getElementById('sql').appendChild(sql);

        for (el of document.querySelectorAll("[hidden=true]")) {
          el.hidden = false;
        };

      };

      document.addEventListener("paste", (event) => {
        event.preventDefault();
        const paste = (event.clipboardData || window.clipboardData).getData('text');
        const [sql, table] = parseTable(paste);
        updatePage(sql, table);

      });

      function copyToClipboard() {
        navigator.clipboard.writeText(final_sql).then(
          () => {
            document.getElementById('copy_button').innerText = 'Copied!';
            setTimeout(function() { document.getElementById('copy_button').innerText = 'Copy SQL to clipboard'; }, 2000);
          }
        );

      };

    </script>
  </head>
  <body>
    <h1>TSV to SQL values</h1>
    <div>
      <mark>TL;DR</mark> Paste (<kbd>cmd + v</kbd> or <kbd>ctrl + v</kbd>) data from google sheets,
      or any other tsv data, and see your data transformed into a SQL <code>VALUES</code> statement.
      <br><br>
      Features of this tool:
      <ul>
        <li>100% offline, no data ever leaves your browser.</li>
        <li>Empty cells, cells with errors (in fact any cell starting with <code>#</code>) are cast as NULL.</li>
        <li>Cells with numbers are cast as numbers.</li>
        <li>Cells with text (and everything else) are cast as strings, single quotes are escaped.</li>
      </ul>
      This tool expects a header row, and that the header column names are valid SQL column names.
    </div>
    <div hidden=true class='notice'>Do this as many times as you need.</div>
    <article hidden=true>
      The data you pasted has been transformed into a SQL <code>VALUES</code> statement.
      <br><br>
      <button onclick='copyToClipboard()' id='copy_button'>Copy SQL to clipboard</button>
      <br><br>
      <details hidden=true>
        <summary>Here is the data you pasted:</summary>
        <div id = 'table'></div>
      </details>
      <details>
        <summary>Here is the SQL generated for you:</summary>
        <div id = 'sql'></div>
      </details>
    </article>
    <section>
      <div>Find the code on <a href="https://github.com/dtchebotarev/dtchebotarev.github.io">github</a>. Use at own risk.</div>
    </section>
  </body>
</html>