Force Data Types (CSV)

[GCadmin]

Hello -

Really like the product, but having one problem, given some tables that I'm converting.

Is there no way to force a data type (say, TEXT instead of INTEGER) in a CSV file? I can switch to JSON, if that input format allows data type assignment.

Any suggestions would be much appreciated!

Best ~ Cliff

[thombashi]

Hello,

Thank you for your feedback.

Unfortunately, there is no way to achieve that for now. However, I will consider implementing that feature for the future release.

The initial idea is passing type hints to sqlitebiter when converting. The below is an example for this idea.

How did you feel about this? I would appreciate any comments or suggestions.

Sample CSV file: sample.csv

a,b,c
1,1,1
2,2,2
3,3,3

type hint config file: type_hint.json

{
    "global" : {
        "type_hint": {
            "a": "auto",
            "b": "text",
            "c": "real"
        }
    }
}

Execution example

Before (current)

sqlitebiter file sample.csv

for now, all of the fields will be converted into integers:

'sample' (a INTEGER, b INTEGER, c INTEGER);

After

sqlitebiter file --xxx type_hint.json sample.csv 

Passing type hints to specify field type:

'sample' (a INTEGER, b TEXT, c REAL);

[GCadmin]

Thanks for the quick response!

Yes, that proposed sample looks good; would certainly work in my situation.

Alternatively, maybe just allow for a type hint in the CSV itself, behind the column name. For example: "Column_a text", "Column_b integer", ...

Or, even allow for submission of an sqlite file with the create table statement. For example: sqlitebiter -o sample.sqlite file .CSV tabledefs .SQL

The last example would allow for the greatest flexibility.

Note - we'd be willing to subsidize any related development effort; either as enhancements to sqlitebiter, or custom to support our project. Please feel free to reach out directly if that is of interest to you.

Best ~ Cliff cliff.gray@grayconsulting.com

On Sat, Jan 12, 2019, 3:05 AM Tsuyoshi Hombashi notifications@github.com wrote:

Hello,

Thank you for your feedback.

Unfortunately, there is no way to achieve that for now. However, I will consider implementing that feature for the future release.

The initial idea is passing type hints to sqlitebiter when converting. The below is an example for this idea.

How did you feel about this? I would appreciate any comments or suggestions. Sample CSV file: sample.csv

a,b,c 1,1,1 2,2,2 3,3,3

type hint config file: type_hint.json

{ "global" : { "type_hint": { "a": "auto", "b": "text", "c": "real" } } }

Execution example Before (current)

sqlitebiter file sample.csv

for now, all of the fields will be converted into integers:

'sample' (a INTEGER, b INTEGER, c INTEGER);

After

sqlitebiter file --xxx type_hint.json sample.csv

Passing type hints to specify field type:

'sample' (a INTEGER, b TEXT, c REAL);

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/thombashi/sqlitebiter/issues/59#issuecomment-453732094, or mute the thread https://github.com/notifications/unsubscribe-auth/AS3RORJyrNUv02k5v8K-fy_tuEZmyRYoks5vCaVVgaJpZM4Z78Oi .

--

​

Cliff F Gray, ETA CPP

Principal, Gray Consulting

Chicago, IL c 773.318.0818

cliff.gray@grayconsulting.com

[thombashi]

As a first step, I had added --type-hint-header option at sqlitebiter 0.24.0. sqlitebiter will extract type hints from header suffix if the option specified.

Example

CSV data: issue59.csv

a_text,b_integer,c_integer
1,1.1,"1.1"
2,1.2,"1.2"
3,1.3,"1.3"

Convert without --type-hint-header

$ sqlitebiter file issue59.csv

Converted SQLite schema:

'issue59' ("a_text" INTEGER, "b_integer" REAL, "c_integer" REAL);

Convert with --type-hint-header

$ sqlitebiter --type-hint-header file issue59.csv

Converted SQLite schema:

'issue59' ("a_text" TEXT, "b_integer" INTEGER, "c_integer" INTEGER);

[thombashi]

I had added --convert-config option to specify type hints for columns that related to #62. The option can be used only for url subcommand for now.