0

I'm attempting to insert/wrangle data from one SQLite table to another. Basically my workflow is importing a CSVkit processed CSV into a table named import, creating a new table named export, and then exporting that new table to a fresh CSV.

The only part that I'm not sure about is the SQL part, I've prepared an example dataset.

Import

Ntype   |Color|Code
:-------|:----|:---
example1|Blue |xyz
example1|Red  |xyx
example2|Black|zyz
example3|Grey |zyz

Export

Name    |Option_Name|Option_Value|Code
:-------|:----------|:-----------|:---
example1|Default    |Default     |
example1|Color      |Blue        |xyz
example1|Color      |Red         |xyx
example2|Default    |Default     |zyz
example3|Default    |Default     |zyz

How would this even be approached? Something using a COUNT function, CASE, GROUP BY?

I'm working from this so far, 

INSERT INTO export (Name, Option_Name, Option_Value, Code)
  SELECT
    Ntype,
    ???,
    ???,
    Code
  FROM import

Just for completeness sake, this is the script I'm working from,

#!/bin/sh
## DESCRIPTION: CSV Import, Wrangle, CSV Export

[ -f "$1" ] && {
    FILE="$1"
} || {
    echo "$0 FILE"
    exit 1
}

## Create Temp Dir
T_DIR="$(mktemp tdir.XXXXX)"
trap 'rm -rf $T_DIR' 0 1 2 3 15

## csvtk grep ..., pass pre-processed CSV to STDIN
## But for this quick example I speciifed the filename.
csvtk csv2tab "$FILE" > "$T_DIR/import.tsv"
sqlite3 << EOF
.separator "\t"
.import $T_DIR/import.tsv import

CREATE TABLE export(
    Name  NVARCHAR(255) NOT NULL,
    Option_Name   NVARCHAR(255),
    Option_Value   NVARCHAR(255),
    Code    NVARCHAR(255));


## Additional SQL queries

EOF

exit 0
J. M. Becker
  • 2,755
  • 30
  • 32
  • It seems like you're trying to code the CSV file from it's relatively _natural_ form to an [Entity-Attribute-Value](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) type schema. Depending on how complicated your real schema is, and how many columns you have etc., you'd perhaps be better served by writing the translation script in a regular programming language. – Horia Coman Apr 10 '18 at 13:37
  • Good catch, yea there clearly is a small EAV in there. The problem is though, that this is not representative of the majority of changes required. It's only for a few columns in a pre-defined set. – J. M. Becker Apr 10 '18 at 13:41
  • If you don't have any other constraints I'd still go for a Python/JS/Ruby etc. script rather than SQL. Whatever SQL you could come up with - and it'd probably be very specific to SQLite will be very gnarly. – Horia Coman Apr 10 '18 at 13:46
  • Gnarly is not really a huge problem in this situation, because this is a wrangle. I can break out and back in, I'm working from an Shell script. Know what, I'm gonna whip up an example of the script also. To give you the full picture. – J. M. Becker Apr 10 '18 at 13:49
  • You have not told us what the SQL should do, and I am unable to deduce this from the example data. – CL. Apr 10 '18 at 18:48
  • @CL. import into export .... select.... Updated the question with where I'm at. – J. M. Becker Apr 10 '18 at 22:20
  • And what is that "???"? Where does the "Default" come from? What is so special about blue and red? – CL. Apr 11 '18 at 07:25
  • @CL., "Default" indicates that it is a place holder line, for an item with variants. "???" is literally that I have no idea. – J. M. Becker Apr 12 '18 at 00:06

0 Answers0