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, or you cannot use the production environment databases for performing analytics. Now you have to transfer the data between environments or databases. 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 issues while importing files containing decimals. You will learn how to configure SQL DataTool so you will not experience the frustration caused by the decimal issues. 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 root causes mentioned before. So now how do we import this file using SQL DataTool?

Since SQL DataTool has an advanced error capturing functionality, you can 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 decimal scenarios. These scenarios will result in errors when using the SQL Server import wizard. The result of these errros in the SQL Server import wizard is that you are unable to import such a file.

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). Now let us 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 with the above settings, I will get 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. The screenshot below shows the error message from the SQL Server import wizard.

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 with very few records and one column (okay, I prepared the file for errors)!

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. Therefore, I will only need to define one field in the SQL DataTool data model definition. The screenshot below shows the data model for this file.

I will import the file using the Import data feature. First, the data model (the system and version) needs to be selected. This way, I am sure that the right data model is used, and therefore that the data is validated against the right data types.

Next, I select the data file that I have prepared for this article. Since the source file is a plain text (.txt) file, I need to select the 'Source type' Text Files (.txt; .csv). Then I browse to the location where I stored the text file, and select the file. 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.

The 'Text file settings' section contain the specifics of a plain text file or a CSV file. You can define the column separator, text identifier (if there is one), the decimal and thousand separator. Furthermore, you can define the date format (allthough SQL DataTool can determine the data format itself), whether you want the data to be appended and whether the text file has rows before the header row. The last option is useful when I receive outputs of a report from specific systems that have e.g. the selection criteria on top of the file.

Last I need to select the 'Table name' in the 'Text file settings' section. This way, the data will be appended to the right table (when 'Append data' is checked) or the right table will be created.

The actual import starts by clicking on 'Get data'. The data is imported to the SQL server database where the SQL DataTool is connected to. In the scenaro that I have prepared, not all lines will be imported. Since I added some strange decimal combinations in the file I prepared. But these are recognized by the SQL DataTool and will be skipped duringimport. When the import has been finished, a pop up will show up like the screenshot below.

Decimal error solution

SQL DataTool writes the erroneous lines in an error file, which is named exactly like the importfile, with the postfix '_error'. 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.

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 almost 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 may 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 interpreted 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!