Jump to content

How to import csv file with missing trailing column data


Recommended Posts

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 by AndyS01
Link to post
Share on other sites

@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 by FrancescoDiMuro

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...