SQLite INTEGER

[spl]

Probably more for Jim as he has SQLite experience. I am revising an old script I started in 2009 regarding SQLite and SQL Server communication. I have scripts that easily import SQLite db/tables into 64-bit SQL Server 2022. Problem is each column is imported as nVarchar(MAX) and I would like numeric columns to be typed appropriately. I have scripts, working via ADO, which can interpret a column datatype and with a map lookup assign it to a comparable SQL Server type. To convert a SQLite table to SQL Server, I am using a datatable, albeit rather than a SQLite=>.csv>GetTypes=>create blank types SQL Server table=>Bulk Import. And I have searched for a way to extract datatypes from a CLR datatable created by SQLite/csv Import with no success.

SQL Lite recognizes INTEGER as one of it's 4-5 datatypes, and according to the docs I read can handle data for boolean bit = > bigInit. Great! but SQL Server is a little more exacting breaking numerics into a multitude of types.

Is there a way to match an SQLite INTEGER into specific SQL Server types?

[JTaylor]

I am not sure I can be of much help.   Other than the affinities(https://www.sqlite.org/datatype3.html), I am not aware of any further specificity.   

Maybe a RegEx search for strings in columns while in the nVarChar(MAX) form?

Sorry I don't have a better answer :-(   Maybe someone else will have an idea.

Jim

[spl]

Yeah, affinity was the ticket. Just had to reverse my thinking. I was assuming a lookup from sql server type to sqlite type. Just kept the create statement in sql server format, SQLite accepts and creates the table accordingly.

Code Select Expand

CREATE TABLE safety ( [Date] Varchar(255), [Injury Location] Varchar(255), [Gender] Varchar(255), [Age Group] Varchar(255), [Incident Type] Varchar(255), [Days Lost] Float, [Plant] Varchar(255), [Report Type] Varchar(255), [Shift] Varchar(255), [Department] Varchar(255), [Incident Cost] int, [WkDay] Varchar(255), [Month] int, [Year] int )

and interprets with affinity. But to complicate things, I have been using CLR datatables, which [unless someone else has the magic formula] do not iterate datatypes for .csv - so had to revert to ADO which does - then revert back to datatables to insert into SQLite schemas just created. 

With testing now, I can use filebrowser to locate a .txt or csv file, interpret the formatting, create a typed table SQL create statement in SQLite db and finally import data.

[JTaylor]

Glad to hear that was helpful.  Sounded like what you needed but wasn't sure as I didn't know what you had previously read.

jim

[td]

And I learned something new.

[spl]

And I learned something new.

and while we're on the subject... One thing I learned was that quoted comma-separated .csv will normally iterate all columns as Varchar, so my function re-writes files as bar-delimited and numeric or date types were resolved. And, never forget that extended properties such as FMT= are ignored in ADO connection strings unless at least basic schema.ini is created for the csv/text file. So my function reads the first line [assumed headers] for a file and interprets the format

• Comma-delimited with quotes
• Comma-delimited
• Tab-delimited
• Bar-delimited |
• Caret-delimited ^
• SemiColon-delimited ;

and create a quick schema.ini which then ADO uses.

Code Select Expand

[safety.csv]
CharacterSet=ANSI
Format=CSVDelimited
ColNameHeader=True