Importing quote-escaped CSV into SQL Server via Import Data wizard into all nvarchar columns

Question

I have a table of CSV data like such:

                 a   |   b   |   c   |   d   |   f    
1:                12     Dave   Larry $1234.0  FALSE
2:              324.0     Bob    Gray $24.012   TRUE
3:              2000     John    Stan  $204.0  
4:              9000    Stace    Jill       -  FALSE
5:              850.0    Till             $30   TRUE

A field such as a user's comments would include commas, so these are escaped via single- or double-quotes. Excel opens these just fine and can be used to cleanse or manipulate the data before importing.

The easiest thing for me from a migration standpoint was to just get the data into the SQL Server as varchars first, then use SQL to manipulate the data into its target destination format.

I did run into the following problems:

1) Trying to import the CSV can cause issues. SQL Server Management Studio's import expects a strictly formatted CSV, meaning something like a comments column or numbers formatted as currency in text could cause imports to fail.

2) When saving the CSV as XLS, SQL Server Management Studio still seems to try and be "smart" about how it interprets the data, regardless of however it was formatted. Sometimes, data cannot be converted to nvarchar or varchar even if you desire that, because the import utility already assumes the data is numeric. Tab-delimited can end up not working as well, especially for something like user comments.

What is an error-free method of importing CSV to SQL Server, making all columns varchar or nvarchar?

Unknown Unknown · Accepted Answer · 2017-05-16T18:15:41

One solution was to use the Data -> Text to Columns, delimited, and then not selecting any delimiters. However, Excel only lets you do this one column at a time. However, this XLS reads into SQL Server just fine, as all nvarchars.

Revising this solution further, you can create the following macro, saving it into the PERSONAL.XLSB so that it is available in all future worksheets. By mapping this macro to a key combination, you can select the a cell, and then the macro will select the column and then run the text to columns function for you:

Sub ColumnToNVarChar()
'
' ColumnToNVarChar Macro
' Convert a column in Excel to a format that SQL Server Management Studio's import process will interpret as nvarchar.
'
' Keyboard Shortcut: Ctrl+d
'
    ActiveCell.EntireColumn.Select
    Selection.TextToColumns Destination:=ActiveCell.EntireColumn, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
End Sub

You then save this as an XLS file and SQL Server Management Studio's "Import Data" process will treat every column as an nvarchar. Usually nvarchar(255).

Importing quote-escaped CSV into SQL Server via Import Data wizard into all nvarchar columns

1 Answers