Jump to content
AndyS01

How to import csv file with missing trailing column data

Recommended Posts

Posted (edited)

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

Share this post


Link to post
Share on other sites
Posted (edited)

@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

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

 

Share this post


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

×
×
  • Create New...