Went through same process, I found tha Navicat was the fastest and easiest way to get data in the right data type, and then be able to save that import template to automate future imports.
It was the ONLY tool that did everything easily out of the box. Good to see some other suggestions here.
There is also an old coldfusion tmt_massimo_csv.cfc (coldfusion/lucee) script that tops all of these, and lucee/cf also has spreadsheet import and export out of the box which were also much better than any of the below tools. That script was able to read more DIRTY csv files than any other tool I've used in 20 years of web dev, and combined with the blistering speed of modern cf engines like lucee, murders large imports (I was doing 20-30 million rows). I still use Lucee (open source/free modern cf engine) and find it one of the best integration tools available on the market today... imho sits beside python, but with more features, cf can read native dot net and native java and uses a very expensive java-dotnet-bridge for fraction of it's cost. anyway, this is another goto for csv because of this script and the speed that cf engines like lucee can run (compiling to java bytecode)
The tools I found were Rubbish:
- Microsofts (all)
- Apex SQL
- any of the other top 5-10 suggestions on this site and others.
Tools that were passable: