Values with 10 digits not importing into SQLite

Question

So, I have no idea what to try anymore, but this is the problem I am facing at the moment:

I have a C# WPF application that imports a .csv file from the server, reads it and then uploads it into SQLite, since adding a new table I'm facing some issues with 2 columns, both numeric and containing between 8 and 10 digits. The cells that contain 8 and 9 digits have no problem, but when I try to upload 10 digits I get a blank cell in SQLite.

The most annoying about this is that there is no error message. I've tried to save into SQLite as numeric, integer, text, ... Nothing seems to work here.

This is my code after the .csv file is opened:

//The code before this part is basically using Jet.OleDb, connect to the file and create a DataSet and DataTable, these contain the right values

DataSet ds = new DataSet("CSVFile");
adapter.Fill(ds);
var dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows) {        
    var eDel = dr[(0)].ToString();
    var eRef = dr[(1)].ToString();
    cmd.CommandText = "INSERT INTO delInc (DELIVERY,REFERENCEDOC) VALUES (@cDel,@cRef)";
    cmd.Parameters.AddWithValue("@cDel", eDel);
    cmd.Parameters.AddWithValue("@cRef", eRef);
    }
    cmd.ExecuteNonQuery();

In my .csv file I have:

DELIVERY    | REFERENCEDOC
121859657   | 59151654
121859658   | 59183792
8185619953  | 7719802361
8185619954  | 380184551

But, in SQLite I only see:

DELIVERY    | REFERENCEDOC
121859657   | 59151654
121859658   | 59183792
            |   
            | 380184551

This is my DDL:

CREATE TABLE delInc 
(
    DELIVERY NUMERIC,
    REFERENCEDOC NUMERIC
);

As I said before, I also tried it with TEXT and INTEGER, both don't work.

SQlite can handle 64bit signed integers i.e. up to 9223372036854775807, if larger, then it stores it as an up to 8 bytes floating point. Column type is pretty much irrelevant as any type can be stored in any column (except a rowid or alias thereof). I believe that your issue is that the values are being truncated/nullified either in the insert or by whatever you are using to see the values. Example SQL Fiddle [INSERT INTO mytable VALUES (92233720368547758088888888888888888888,9223372036854775807);](http://sqlfiddle.com/#!7/b7ea87/1) — MikeT, Oct 04 '19 at 12:03

score 0 · Answer 1 · answered Oct 04 '19 at 10:16

0

you won't be able to get values over 2147483647 into INTEGER as that it is only a 4Byte number system, you may however be able to fix it with BIGINT which is an 8Byte number system.

See here for more https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/integer-types.html

answered Oct 04 '19 at 10:16

Harvey

114
1
8

1

That is true for MySQL and many others, but not in SQLite, see https://stackoverflow.com/a/7337945/5450760 – Giuliano Oct 04 '19 at 10:23

score -1 · Answer 2 · answered Oct 04 '19 at 12:45

-1

Try it again with a longer number/string in the first row of your dataset. OleDB might be trying to guess lengths based on the first few rows, so this should tell you whether that's the case...

answered Oct 04 '19 at 12:45

drkmtr

164
1
6

Values with 10 digits not importing into SQLite

2 Answers2