Determining the optimal Column Widths from a .csv File

Question

I want to import a .CSV file into a MySQL Table. I would like to determine the optimal column widths to use based on the actual data contained in the CSV file. Are there any scripts or utilities to accomplish this?

Answer 1

Trying to guess appropriate column lengths is best avoided if possible - if you can get the source of the CSV to specify the expected format in more detail (column X will contain no more than Y characters) then try that.

I would suggest initially going for long variable length types - this may be less efficient than more constrained types but will ensure that you do not experence errors in later imports if some fields grow larger than expected (or worse silently lose data because mysql silently truncates something). Under MSSQL text fields would want to be nvarchar(max) or varchar(max) in this case, unless they need to be part of an index in which case they'll need to be more constrained (no index can be over more than 900 bytes of data under MSSQL). Unless you can get more garantees of data format from the source, this is the safest way, though of course you can use a little guesswork (a field containing a national insurance number shouldn't ever be longer than 11 characters for instance, though be careful still as a field may be incorrectly named nad/or overloaded to store things other than what its name suggests).

If you really have no choice but to guess and using larger types feels too inefficient to you then to help the guestimating process import the file(s) you have into a table where all fields are maximum length variable text types, then perform some rudementry analysis such as SELECT MAX(LEN(<field1>)), MAX(LEN(<field2>)) FROM <table> to get indications to base your guesswork on, and for non-string fields you can use more complex pattern matching to determine expected ranges (i.e. field X is always a data in ISO format, field Y is a number between -500 and 45,123 with up to two decimal places, and so forth).

With regard to "optimal": while it may be inefficient to use larger variable-length types where a short fixed length will do, efficient and optimal are not neccesarily the same: being efficient will be little benefit if you expereience errors or lose data due to accidentally setting the type too restrictively.

Answer 2

PROCEDURE ANALYSE() can help you

You can try a bit complex way following these steps:

• load your file.csv into a generic table "myTable" where every field is a varchar
• run the select * from myTable PROCEDURE ANALYSE()

Than analyze the output in the Optimal_fieldtype and evaluate the suggested column type

Here a sample of the output

mysql select * from myTable PROCEDURE ANALYSE()\G
--------------------------- 1. row ---------------------------
             Field_name: myTable.fieldA
              Min_value: sample sample
              Max_value: omit
             Min_length: 4
             Max_length: 126
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 49.3771
                    Std: NULL
      Optimal_fieldtype: VARCHAR(126) NOT NULL
--------------------------- 2. row ---------------------------
             Field_name: myTable.fieldB
              Min_value: 1
              Max_value: 22043
             Min_length: 1
             Max_length: 5
       Empties_or_zeros: 7888
                  Nulls: 0
Avg_value_or_avg_length: 27.6984
                    Std: 168.4809
      Optimal_fieldtype: SMALLINT(5) UNSIGNED NOT NULL

Alter your table according the output suggestion and your requirements.

Answer 3

A tool useful for import/export/other-utilities data to MySQL/MS-SQL databases

http://www.heidisql.com/

Hope this helps