Parsing poorly structured CSV data

[aodhan-domhnaill]

I have CSV that looks like this,

A,1,2,3
A,4,5,6
B,1,2
B,3,4
B,5,6
C,6,7,8,9

Basically someone decided to append different dimension data together

I'm trying this to read line by line,

SELECT
    *
FROM CSV('\t', `file_path.csv`, 'UTF8', true)
WHERE c1 LIKE 'A%';

How would I split each line into columns?

[kpym]

You can use the --allow-uneven-fields option to tell csvq that the number of columns is not equal in every row.

> csvq  --allow-uneven-fields --no-header 'select * from `file_path.csv`'
+----+----+----+------+------+
| c1 | c2 | c3 |  c4  |  c5  |
+----+----+----+------+------+
| A  | 1  | 2  | 3    | NULL |
| A  | 4  | 5  | 6    | NULL |
| B  | 1  | 2  | NULL | NULL |
| B  | 3  | 4  | NULL | NULL |
| B  | 5  | 6  | NULL | NULL |
| C  | 6  | 7  | 8    | 9    |
+----+----+----+------+------+