Comma Separated Values (CSV) is incredibly a common format to pull data from your database as well as some data sources that don’t have relational structure. So, it’s not curious that all database engine possesses its own tools to export data from CSV files. However, what about casting types when importing data from CSV files into database? Definitely, types mapping can’t be made easy simply because unlike databases CSV format doesn’t store any info about kinds of columns. The simplest option would be to transform every column of CSV file into text in the destination database. However after such conversion the resulting data needs a great deal of manual post-processing work.
To enable export from CSV files in many simple way, Intelligent Converters created export tools that create smart type mapping algorithm for the best common databases: CSV to MySQL, CSV to Oracle and CSV to MS SQL converters. The primary concept of this algorithm is always to collect info about actual types of each CSV field being converted then refine column types by the end of conversion process. Automatically all values from CSV files are converted as text. But after analyzing all data, Intelligent Converters tools will cast columns to numbers or dates when it’s possible. The converter is attempting to translate every field as number or date using both Windows API functions as well as its own patterns. When a column has failed to be handled as date or number, the converter won’t make an effort to cast it again in next records to obtain better functionality in the conversion procedure.
Let’s examine how smart type mapping operates on example. Imagine that source CSV file contains date values in a column. While migrating CSV data to database the conversion tool endeavors to translate every field in the column as date. The converter distinguish datetime (like ’04/08/1977 13:14:15′), “pure” date (like ’11/20/2009′) and “pure” time types (like ’17:05:21′), so each column is going to be casted in the most suitable type by the end of conversion process. In case the column contains both “pure” date and “pure” time values, this software will convert it into string to prevent loss of data.
Smart type mapping algorithm saves lots of time and enables automate export data from MS Excel spreadsheets and Comma Separated Values (CSV) files. If you require more handling of the types mapping, Intelligent Converters software offers table structure customization feature. It’s obtainable by double click the related table name in “Selected tables” set of the conversion wizard. This selection enables it to improve name and type of any column or exclude particular columns from conversion.
Check out the website of Intelligent Converters to learn a lot more about their products and services: https://www.convert-in.com
