How to import csv file with missing trailing column data

[AndyS01]

I have a csv file as (follows):

Quote

Row1C1,Row1C2,Row1C3,Row1C4
Row2C1,Row2C2,Row2C3
Row3C1,Row3C2
Row4C1,Row4C2,Row4C3,Row4C4

I get a failure when I import this csv file.  This is the error:

test5.csv line 2: expected 4 columns of data but found 3

Here are my SQL commands:
 

mode csv
CREATE TABLE if not exists TestTable (C1,C2,C3,C4);
INSERT INTO TestTable VALUES ('a1','a2','a3','a4');
INSERT INTO TestTable (C2) VALUES ('random text');

.import test5.csv TestTable

select * from  TestTable;

This csv file is a test file, the actual csv file contains a couple of thousand lines.  Here is the error:

How can I import this csv file?

Edited April 28, 2019 by AndyS01

[FrancescoDiMuro]

@AndyS01

The error says it clearly; you have a table with four columns, but the second line of your CSV file doesn't contain more than three columns, so the error is showed.

You should work on your file to have always the same number of columns, or you can use one of the many ways to import null values from a CSV file.

Maybe someone else has a better suggestion

Edited April 28, 2019 by FrancescoDiMuro

[water]

In the wiki you find at least two UDFs handling CSV files. Maybe they provide a means to read incomplete records.
https://www.autoitscript.com/wiki/User_Defined_Functions#Files.2C_Databases_and_web_connections

[Malkey]

Try this example.

#include <Array.au3>

Local $sFileName = "testaa.csv"
$a = _CSVtoArray($sFileName)
_ArrayDisplay($a)


; The CSV file does not need to have equal number of columns on each row.
Func _CSVtoArray($sFileName, $sColDelim_Item = ",")
    Local $iCol = 0, $vValue = FileRead($sFileName)
    Do
        $iCol += 1
        Local $aArray[0][$iCol]
        _ArrayAdd($aArray, $vValue, 0, $sColDelim_Item)
    Until @error <> 3 ; @error = 3 - $vValue has too many columns to fit into $aArray.
    Return $aArray
EndFunc   ;==>_CSVtoArray