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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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
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.
PROCEDURE ANALYSE() can help you
You can try a bit complex way following these steps:
Than analyze the output in the
Here a sample of the output
Alter your table according the output suggestion and your requirements.