Like many others, I recently had to import data from a CSV (comma-separated file) into SQL Server. It was a one-time request to clean up and consolidate data for reporting purposes. However, the process turned out to be more challenging than expected. In this article, I will discuss some of the frustrations and issues I encountered while working with CSVs in SQL Server.

Messy Data

When dealing with CSV data from external sources, it’s common to encounter messy and unstructured data. In my case, I received multiple CSV files with different formats and naming conventions. Some files had extra rows with irrelevant information, while others had fields with special characters. Here’s an example of a field inside one of the CSV files:

Username,ID,Datetime,Type,Status,Note,From,To,Amount (total),Amount (fee),Funding Source,Destination,Beginning Balance,Ending Balance,Statement Period
HerdofTwo,,,,,,,,,,,,$500.00,,,,,
,5555555555555555,2020-01-10T02:50:47,Payment,Complete,Stock show,Cowboy Joe,My Wife,+ $10.00,,,Venmo balance,,,,Venmo,,
,555555555555555,2020-01-16T14:12:08,Payment,Complete,"You're magic ?? (sorry, totally spaced it)",Random Person,My Wife,+ $50.00,,,Venmo balance,,,,Venmo,,

As you can see, the data is not in a structured format, and some fields contain unexpected characters. This makes it challenging to import the data into SQL Server without any issues.

Using SSMS Import Flat File Wizard

To import the CSV data into SQL Server, I initially decided to use the Import Flat File Wizard in SQL Server Management Studio (SSMS). This wizard allows you to select a file and create a table for the data. However, I quickly realized that the wizard was not suitable for importing multiple files efficiently. I had to repeat the process 12 times for each file, resulting in a clunky and time-consuming experience.

Exploring SSMS Import Data Wizard

After my initial attempt, I explored the Import Data Wizard in SSMS, hoping for a better solution. This wizard allows you to build an SSIS package and potentially automate the import process. However, I encountered several issues with this wizard as well. The user interface was not intuitive, and the default settings were not ideal. Additionally, the wizard failed to detect the correct data types for some fields, leading to truncation errors.

Trying ADS Azure Data Studio

Seeking an alternative solution, I discovered the SQL Server Import extension in ADS Azure Data Studio. This extension provided a quicker and more user-friendly experience compared to SSMS. The import wizard in ADS allowed me to preview the data, adjust data types, and easily import the CSV files. Although I encountered some errors initially, I was able to resolve them and successfully import the data.

Using PowerShell

During my frustration with the wizards, I received suggestions to try PowerShell for importing CSV data. I decided to give it a shot and used the Import-DbaCsv cmdlet from the dba tools module. This turned out to be the easiest and most efficient method. I could import the data with a simple command and even automate the process for multiple files. PowerShell provided more flexibility and control over the import process compared to the wizards in SSMS and ADS.

Conclusion

Importing CSV data into SQL Server can be a challenging task, especially when dealing with messy and unstructured data. While SQL Server Management Studio provides import wizards, they may not always be the most efficient or user-friendly options. In my experience, PowerShell, specifically the Import-DbaCsv cmdlet, proved to be the best solution for importing CSV data into SQL Server. It offered more control, flexibility, and automation capabilities.

As a SQL Server professional, it’s essential to explore different methods and tools to handle data import tasks effectively. By leveraging PowerShell and other tools, you can streamline the process and save time and effort in dealing with CSV data.