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.

In previous articles, I have explained how you can use SQL DataTool for other kind of errors. For example, how you can solve issues with line breaks, handle wrong column delimiters, issues with column lengths, and how you can best import files with different date types.

What kind of errors do decimals cause?

When importing decimals into a SQL Server database, decimal number can cause a number of errors. For example, the number of decimal places is defined wrong, or the decimal separator is defined wrong. These causes are all quite straightforward, but not that easy to solve with the normal wizards or tools that are available.

Other, more annoying causes are for example when the thousand and decimal separators are not used in a coherent way, or when the decimal separator matches the column separator (e.g. when importing plain text files).

Finally, you can have your own specific scenario, like for example you already want to convert a column to a numeric or decimal field during the import. When the data is already imported in a table, you might find it very cumbersome to change the datatype of a field to decimal.

How to import files with decimals?

So, now we have a file that causes errors due to one of the reasons above. How do we import this file using SQL DataTool?

Since SQL DataTool has an advanced error capturing functionality, we can just import the file as we always would using SQL DataTool. For this article, I have created a small plain text file that contains various different scenarios.

Test file import decimals
Test file import decimals

The decimal separator in this file, is the dot (.). The expected number of decimals that we will use during the import, is 3 (decimal precision). So now what let's see what happens when we want to import this file using the SQL Server import wizard.

Import decimals with SQL Server

In the SQL Server import wizard, I want to import a flat file source. Since the file I prepared only has one column, I only have to change that column to be of the type decimal, with a scale of 3 (since I want to import the file with the right settings).

When I import the file now with the above settings, I receive an error. This error says that the value could not be converted because of a potential loss of data. This is the first error I see. When I scroll down in the error message window. I see a message that tells me that the above message might give a better indication of what the error might be.

Import decimals SQL server error
Import decimals SQL server error

This error message does not tell me anything, and does not help me solving the import issue. Maybe even worse: no data has been imported at all into my SQL server database, leaving me puzzled how to continue my work doing the actual data analysis. And the file I prepared only had one table!

Import decimals with SQL DataTool

So, now I will import the file with the SQL DataTool. First of all, I need to define a data model. This is the same as in the SQL Server import wizard, only in SQL DataTool you can reuse the data model. As you can recall, the file that I prepared only has one column.

Data model decimal file
Data model decimal file

Next, I import the file using the Import data feature. First, the data model needs to be selected. This way, I am sure that the right data model is used, and therefore that the data is checked against the right data types. Next, I select the data file that I have prepared for this article. Finally, the text file settings need to be configured. Since I have imported the data before (see column conversion issues), I can select a saved import. This will limit the mistakes when importing data from a plain text file.

Import decimal file
Import decimal file

I click on 'Get data' and the data is imported to the SQL server database where the SQL DataTool is connected to. Not all lines will be imported, since I used some strange decimal combinations in the file I prepared. But these are recognized by the SQL DataTool and could not be imported.

Decimal import errors
Decimal import errors

Decimal error solution

SQL DataTool writes the erroneous lines in an error file. This file contains all lines, including the details of the error. These details contain which field could not be imported, the value and a short explanation. This will help you solving these issues and import (or append!) these lines to the already existing table in the SQL database.

Decimal import - error handling
Decimal import - error handling

Next thing I have to do is to solve the errors in the file, saving the file with a new name (remove '_error' from the filename!) and import the previously erroneous data the same way I did with the other file. Only this time I check the 'Append data' checkbox in the 'Text file settings' section.

Solving errors has now become a dream job. I now exactly know what causes the problems and I can solve them easily. Some errors I should solve in the data model that I have prepared in SQL DataTool (e.g. the has too many decimals error). Other errors will maybe require some talks with your client. Where do those strange values or decimal separator signs come from? This way you can not only help your client with your analytics and insights, but even help them with data issues that they might come accross. Now think of the added value of both of your insights!

Now: you have imported the decimals successful!

It is not at all hard to import plain text files into your SQL server database. Even when the files you want to import contain data that cannot be interpret by default wizards, SQL DataTool can help you finish your task. When importing data is a repetitive task, you can also use SQL DataTool for this. Without scripting and with understandable error handling!

 

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