csvsql: agate-sql: Add option to set varchar length to data's maximum length, or to a user-provided length

[StudentForever]

Hi

Not sure if I am not passing the right flags, I noticed that the column sizes in the create table query default to max e.g. varchar (max) . Is there an option so that column sizes are inferred based on the actual data.

 cat test.csv
tag,tag2,Date,col1,col2,col3,col4,col5
DFAN8086,sss,12/24/2012,0.9454054,0.391277,0.696738598,0.963471,0.422531
FDG491BT1088,wew,12/24/2012,0.922601887,0.3125817,0.991514,0.740261,0.3096907
GEW00328745,tere,12/24/2012,0.341763918,0.587643,0.834997,0.611671,0.3686074
GWW02993182,qewqeqw,12/24/2012,0.078535992,0.497771,0.394849,0.4730659,0.4074645


csvsql -i mssql test.csv
CREATE TABLE test (
        tag VARCHAR(max) NOT NULL,
        tag2 VARCHAR(max) NOT NULL,
        [Date] DATE NOT NULL,
        col1 DECIMAL(38, 9) NOT NULL,
        col2 DECIMAL(38, 7) NOT NULL,
        col3 DECIMAL(38, 9) NOT NULL,
        col4 DECIMAL(38, 7) NOT NULL,
        col5 DECIMAL(38, 7) NOT NULL
);

[jpmckinney]

Different databases have different default behavior in agate-sql (which csvsql uses) - it seems mssql defaults to max. We can add a flag to csvsql to calculate the max length from the data. Some users don't want this, because they may add new data whose fields are longer, or they may not want csvsql to do any inference on the data (which can be slow for large files).

[StudentForever]

@jpmckinney Thanks for reviewing the request.

regards
kiran

[Freewilly3d]

I'm interested in something like this for Oracle also. Another option would be to set a default for varchar on the command line like varchar2(1000).

Thanks

[chriszrc]

I really thought csvsql used to do this already, at least for postgres. Finding max lengths for values in multiple columns and setting reasonable varchar limits is a pain, would really this to be automated

[jpmckinney]

Why do you need limits when using Postgres? The current behavior with Postgres is to set no limit.

[jpmckinney]

Postgres describes no performance concern, so the only reason I can imagine is database-level data validation - though validation is often better handled in code. https://www.postgresql.org/docs/current/datatype-character.html
See also https://stackoverflow.com/a/4849030

[chriszrc]

@jpmckinney yes, it's for data validation, it's saved me more than once from erroneous data being inserted in wrong/mismatched columns

[acosonic]

Hi, any progress on this? I am struggling with csvsql creating smaller fields than needed, instead of just using varchar at full length...

[jpmckinney]

@acosonic What database are you using? Its behavior differs across databases. FWIW, most people here are discussing csvsql creating fields that are longer (maximum length) than needed – in MSSQL, PostgreSQL and also sounds like Oracle.

[acosonic]

@acosonic What database are you using? Its behavior differs across databases. FWIW, most people here are discussing csvsql creating fields that are longer (maximum length) than needed – in MSSQL, PostgreSQL and also sounds like Oracle.

Mysql, it works well, on most csv files.

[jpmckinney]

@acosonic Ok, for your use case, I've added --min-col-len and --col-len-multiplier, which already existed in agate-sql, but weren't exposed in CSV Kit 60fa3cf. I leave the issue open to constraint the maximum length (per original discussion).