Importing CSV to SQL Server: Solutions for Character Encoding Issues

Discover how to import CSV files into SQL Server with different encoding formats using `BULK INSERT` and PowerShell. Learn to handle UTF-8 and Windows-1251 encodings effectively.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Importing CSV to SQL Server using bulkcopy

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Importing CSV to SQL Server: Solutions for Character Encoding Issues

Importing CSV files into SQL Server can be a daunting task, especially when dealing with different text encodings like UTF-8 and Windows-1251. When the standard methods fail, it’s crucial to know how to adjust your import commands to handle special characters and ensure data integrity.

In this post, we’ll explore how to effectively import CSV files into SQL Server while addressing common encoding problems. We'll provide a solution using both PowerShell and T-SQL, so you can choose the method that best suits your needs.

The Problem

You have CSV files that you want to import into SQL Server, but you've encountered encoding issues. Your current code works well for standard Latin encoding but struggles with files saved in UTF-8 and Windows-1251 formats. Specifically, the text may not display correctly, leading to data loss or corruption.

Sample CSV Data:

[[See Video to Reveal this Text or Code Snippet]]

Suggested Solutions

1. Using PowerShell with Encoding Adjustments

The initial PowerShell script provided begins with a basic structure to read and import CSV files. To address the encoding issue, we need to modify how the script reads the CSV file.

Here’s how you can enhance the PowerShell code:

Adjusting the StreamReader to specify the correct encoding.

[[See Video to Reveal this Text or Code Snippet]]

This change ensures that the reader decodes the CSV file properly according to its specific encoding, allowing characters such as Cyrillic text to be interpreted correctly.

2. Using T-SQL with BULK INSERT

If you're open to a more concise solution, T-SQL provides a powerful command for importing CSV data with character encoding support through the BULK INSERT statement. Below is the streamlined T-SQL approach:

[[See Video to Reveal this Text or Code Snippet]]

Key Points about the T-SQL Approach:

CODEPAGE = '65001': This setting ensures that SQL Server correctly interprets UTF-8 encoded files.

product_nm NVARCHAR(100): This data type allows for the storage of Unicode characters, making it suitable for non-Latin text.

Example Output

After executing the BULK INSERT, you can run a SELECT query to verify the data integrity:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Importing CSV files into SQL Server need not be a headache, even when facing encoding challenges. By leveraging PowerShell with appropriate encoding settings or utilizing T-SQL's BULK INSERT, you can successfully manage and import your data without loss or corruption.

Feel free to explore both the PowerShell and T-SQL methods to determine which approach fits your workflow best. Happy importing!