Comma Separated Values (CSV) is an incredibly 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 their own tools to export data from CSV files. However, what about casting types when importing data from CSV files into a 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 the 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 ways, 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 the actual types of each CSV field being converted and then refine column types by the end of the 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 a number or date using both Windows API functions as well as its own patterns. When a column has failed to be handled as a date or number, the converter won’t make an effort to cast it again in the next records to obtain better functionality in the conversion procedure.
Let’s examine how smart type mapping operates an example. Imagine that source CSV file contains date values in a column. While migrating CSV data to the database the conversion tool endeavors to translate every field in the column as a date. The converter distinguishes 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 the conversion process. In case the column contains both “pure” date and “pure” time values, this software will convert it into a string to prevent loss of data.
Smart type mapping algorithm saves lots of time and enables automated 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 features. It’s obtainable by double click the related table name in the “Selected tables” set of the conversion wizard. This selection enables it to improve the 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