CODEWITHSUNDEEP
csvsqlite
Kevin Johnson
Kevin Johnson

Reputation: 47

.Import CSV to sqlite3 database

I am absolutely banging my head against the wall because no other answers have worked for me.

I have a CSV file...here are 10 lines of it as an example

Row 1:    https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J4813A                  
Row 2:    F.05.80   6-Jul-15    3-Nov-15    Release notes   1.67 MB »
Row 3:    https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J4903A                  
Row 4:     I.10.107 24-Aug-15   2-Nov-15    Release notes   3.49 MB »
Row 5:     https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J9019B                 
Row 6:     Q.11.76  6-Feb-17    21-Feb-17   Release notes   2.80 MB »
Row 7:     https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J9022A                 
Row 8:     N.11.76  6-Feb-17    21-Feb-17   Release notes   3.11 MB »
Row 9:     https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J9147A                 
Row 10:     W.15.14.0015    23-Mar-17   30-Mar-17   Release notes   9.16 MB »

https://i.sstatic.net/H9eoI.jpg here is a screenshot of the CSV file

I also have a database file created for this express purpose. Its name is Test.db. In that database, there is a table named output

When I run .schema Output i get

`CREATETABLE(Output("https://h10145.www1.hpe.com/downloads/SoftwareReleases.aspx?ProductNumber=J4813A" TEXT);

I am attempting to use the SQLite command line to resolve this

`C:\Windows\System32>sqlite3 c:\source\test.db
sqlite> .separator ,
sqlite> .mode csv
sqlite> .import c:/source/output_file.csv Output`

I get this error message c:/source/output_file.csv:59: expected 1 columns but found 6 - extras ignored This error occurs on every single line, from 1 to 59

What am i missing here?

Upvotes: 1

Views: 755

Answers (2)

Kevin Johnson
Kevin Johnson

Reputation: 47

Answering my own question here :

The CSV i was using to import over was poorly formatted. I re-created it with 1 column and a specific header field and it worked without issues

Thanks Yunnosch for your guidance

Upvotes: 0

Yunnosch
Yunnosch

Reputation: 26703

The schema for your table looks broken, probably by misused formatting, but it looks like having only one column. Your input file in the picture shows that you have many ,.
(Your input quote in the question did not show that by the way.)
You are using , as separators to fill a table with one column.
I.e. sqlite sees many columns in the import and only has one column to fill.

Make a table with six columns.
Start with a safe one i.e. six columns of varchar(300).

Alternatively, only import one column, by using a separator which does not occur in your input file. That would work with a table which only has one column.

Upvotes: 1

Related Questions