How to import decimals in SQL Server database tables?

I know what you are going through. I have experienced it. You receive some files from clients to show them your data analytics skills. Since you can not use the database of the client, you have to transfer the data between environments. You want to start with the interesting work as soon as possible, and show the results to the client. However, while importing the files, errors are popping up.

In this article I will show you how you can use SQL DataTool to get around the errors caused by all kind of scenarios while importing files containing decimals. I will describe how you can configure SQL DataTool so you will not experience the frustration caused by the decimals. In the second part of this article, you will learn how to get the erroneous records into your database.

Import World Cities Database

When you are working in data analysis or data science, it will not take a long time before you want to add more information to your data. You can get data from all kinds of sources, like systems in your company, but also from the internet. Say, for example, you want to add to your data the location of cities. Since a lot of visualization tooling, like Microsoft PowerBI, or Tableau, uses the latitude and altitude data, it is useful to add this to your data. However, most systems within companies do not store that kind of data. In this article, we explain how you can easily use the SQL DataTool to import data from the World cities database to your SQL Server database. 

Column length conversion issues

The import data wizard of SQL Server Management Studio is a useful import tool for SQL server databases. But when you get errors while importing data, you will experience the limitations of this functionality. Data conversion errors are one of the most common and time consuming frustrations of data processing. And one of the hardest to find a solution to. In our day-to-day work, we lost a lot of time to solve this kind of errors. Therefore we developed the SQL DataTool.

In this article we will show how the SQL DataTool can help you with data load errors related to string conversion issues due to text lengths and data model problems. When using the import data wizard, the data conversion errors caused by column length are hard to solve. The first part of this article, we will give an example. In the second part of this article, we will show you how the SQL DataTool can help you with this data conversion error.

Wrong column delimiter

Column delimiters in plain text files can cause annoying problems. Solving such a problem will take you a lot of time. And this is time that you rather spend on doing some real work, or basically anything else that you like to spend your time on.

In this blog post, I will explain which root causes there are for the column delimiter issue. At the last part of this article, I will also offer you the solution. This solution involves using the SQL DataTool.

In a different article, I went through giving the solution of unexpected line breaks in flat files. This might also interest you when you run into the issue of a wrong column delimiter.

SQL database import errors: line breaks

When importing huge plain text files or comma separated files, different errors may occur while using the SQL Server import wizard. One of such errors is line breaks in the source files.

SQL Server import and export wizard expects a line break before a new record begins. However, when importing plain text that contains (larger) text columns, you might walk into a record that contains an unexpected line break in the text column. The larger the number of records to be imported, the bigger the chance that a record causes an error.

Page 1 of 2

 

Please contact us when you have questions, suggestions or remarks about our product or services.