Import Double Quotes Qualified CSV File With BULK INSERT

I found a quick way to import a double quoted column CSV file into SQL Server table with BULK INSERT without using FORMAT file.
The text file should include header double quoted as well.

 BULK INSERT  [test1].[dbo].ncoabycountynew
   FROM 'C:\temp\myfile_doublequotes4.txt'
   WITH
      (
         FIELDTERMINATOR ='","',
         ROWTERMINATOR ='"\n"',
		 FirstRow=2
      );


 

If the text file has no header, the code will change to the following with one extra double quote residue in the first cell of the first row.

 BULK INSERT  [test1].[dbo].ncoabycountynew
   FROM 'C:\temp\myfile_doublequotes4.txt'
   WITH
      (
         FIELDTERMINATOR ='","',
         ROWTERMINATOR ='"\n"',
		 FirstRow=1
      );


 

The BULK INSERT is loading data very fast. It took only 3 seconds to load a million rows (160 M size).

P.S.
There is a catch for this code: you need to get rid of an extra double quote at the end of the last column.

Happy Ending with BULK INSERT

After playing with bcp for some time, it is just very simple to use BULK INSERT to load data into a table very quickly.

-------fmt format
DECLARE @cmd0 VARCHAR(4000) 
set @cmd0 = 'BCP  [test1].[dbo].[ncoabycounty] format nul   -f  "C:\temp\ncoabycounty.fmt" -c -t\t -T -S'+ @@servername
exec master..xp_cmdshell @cmd0


DECLARE @cmd NVARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].[ncoabycounty] OUT  "C:\temp\ncoabycounty.txt" -f  "C:\temp\ncoabycounty.fmt" -c -T  -S'+ @@servername
exec master..xp_cmdshell @cmd


DECLARE @cmd3 NVARCHAR(4000) 
set @cmd3 = 'BCP  [test1].[dbo].[ncoabycounty] IN  "C:\temp\ncoabycounty.txt" -f  "C:\temp\ncoabycounty.fmt" -c -T  -S'+ @@servername
exec master..xp_cmdshell @cmd3



-----************************************
--Quicker
BULK INSERT [test1].[dbo].ncoabycounty FROM 'C:\temp\ncoabycounty.txt' WITH (FORMATFILE='C:\temp\ncoabycounty.fmt');

-----************************************
 

 --Or

 BULK INSERT  [test1].[dbo].ncoabycounty
   FROM 'C:\temp\ncoabycounty.txt'
   WITH 
      (
         FIELDTERMINATOR ='\t',
         ROWTERMINATOR ='\n'
      );




 

bcp Unicode Samples

-- -w
-- Performs the bulk copy operation using Unicode characters. This option does not prompt for each field; it uses nchar as the storage type, no prefixes,
-- \t (tab character) as the field separator, and \n (newline character) as the row terminator. -w is not compatible with -c.
 
 
--****************Export Using a stored procedure
 
-------fmt format
DECLARE @cmd0 VARCHAR(4000) 
set @cmd0 = 'BCP  [test1].[dbo].[Tbl_survey] format nul   -f  "C:\temp\Tbl_survey.fmt" -w   -t\t -r \n -T -S'+ @@servername
exec master..xp_cmdshell @cmd0
 
 DECLARE @SqlCmd NVARCHAR(4000) 
 DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP  "exec test1.dbo.usp_ExportData" queryout  "C:\temp\Tbl_surveynew.txt" -f  "C:\temp\Tbl_survey.fmt" -w  -T  -S'+ @@servername
 
set @SqlCmd = 'exec master..xp_cmdshell ''' + @cmd1 +''''
  
print @SqlCmd
exec sp_executesql @SqlCmd
 
---****************************
 
 
 
--$$$$$$$Export table bcp no header
 
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].Tbl_survey format nul -f "C:\temp\Tbl_survey.xml" -x -w -t\t  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
 
 
DECLARE @cmd7 NVARCHAR(4000) 
set @cmd7 = 'BCP  [test1].[dbo].[Tbl_survey] OUT  "C:\temp\Tbl_survey2new2.txt" -f  "C:\temp\Tbl_survey.xml" -w -T  -S'+ @@servername
exec master..xp_cmdshell @cmd7
 
----$$$$$$$$$
 
 
 
 
--#########ExportTable with Header  Unicode
DECLARE @sql AS VARCHAR(4000)
DECLARE @cmdAll VARCHAR(4000) 
;with mycte as (select TABLE_CATALOG, table_name, COLUMN_NAME , [ORDINAL_POSITION] from [INFORMATION_SCHEMA].[COLUMNS]
where table_name='Tbl_survey' and TABLE_CATALOG='test1')
   
SELECT @sql='" Select ' + stuff( (SELECT ', '+ Quotename(COLUMN_NAME,'''') +' as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
+  ' UNION ALL Select ' + stuff( (SELECT ', CAST('+ Quotename(COLUMN_NAME,']') +' as varchar(500) ) as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
 + '  FROM ' + 'test1' + '..'+ 'Tbl_survey'  + ' " '
FROM mycte m1
  
set @cmdAll = 'bcp.exe ' +  @sql +  ' queryout "C:\temp\Tbl_survey2new2Header.tx" -f  "C:\temp\Tbl_survey.xml" -T -S'+ @@servername
 
exec xp_cmdShell @cmdAll
 
--#########



-----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^BULK INSERT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
 BULK INSERT  [test1].[dbo].Tbl_survey
   FROM 'C:\temp\Tbl_survey2new2.txt'
   WITH
      (
         FIELDTERMINATOR ='\t',
         ROWTERMINATOR ='\n',
		 FirstRow=1
		 ,DATAFILETYPE= 'widechar'
      );



	  
-----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^BULK INSERT ^^^^^^^^^^^^^^^^^^^^^^^^^^^




 

bcp Delimiter Samples

--Double quotes and comma ","   --Option 1 and append " to row delimter with -r \^"\n
DECLARE @cmd5 VARCHAR(4000) 
set @cmd5 = 'BCP  [test1].[dbo].[bcpView] format nul   -f  "C:\temp\myFormatFile2.xml" -x -c -t\^",\^" -r \^"\n -T -S'+ @@servername
exec master..xp_cmdshell @cmd5


--Double quotes and comma ","   --Option 2
DECLARE @cmd5 VARCHAR(4000) 
set @cmd5 = 'BCP  [test1].[dbo].[bcpView] format nul   -f  "C:\temp\myFormatFile2.xml" -x -c -t""",""" -r\^"\n -T -S'+ @@servername
exec master..xp_cmdshell @cmd5


--pipe--Option 1
DECLARE @cmd5 VARCHAR(4000) 
set @cmd5 = 'BCP  [test1].[dbo].[bcpView] format nul   -f  "C:\temp\myFormatFile2.xml" -x -c -t\^|  -T -S'+ @@servername
exec master..xp_cmdshell @cmd5


--pipe --Option 2
DECLARE @cmd5 VARCHAR(4000) 
set @cmd5 = 'BCP  [test1].[dbo].[bcpView] format nul   -f  "C:\temp\myFormatFile2.xml" -x -c -t"|"  -T -S'+ @@servername
exec master..xp_cmdshell @cmd5



 

bcp Export/Import wihtout FORMAT File

If we can control the format to export data to a file, we can use the same format to import the data file without using format file. Fomat file is useful to work with files that are not formatted as we expected.
We can define our own delimiters inside double quotes to make them safe to use.

--Column delimiter with arbitrary [||]
--Row delimiter with arbitrary ` plus regular CRLF

--We don't need  a format file with our own delimiters

DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP [test1].[dbo].[myFiles] OUT  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t"[||]" -r "`"\n -F 1 -S'+ @@servername  
exec master..xp_cmdshell @cmd1

DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].[myFilesNew] in  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t"[||]" -r"`"\n -F 1 -S'+ @@servername
exec master..xp_cmdshell @cmd2


  
--Column delimiter with arbitrary ","  douvle quotes and comma   the file will miss the leading  quote but it will import OK with the same delimiters
--Row delimiter with arbitrary ` plus regular CRLF

DECLARE @cmd11 NVARCHAR(4000) 
set @cmd11 = 'BCP [test1].[dbo].[myFiles] OUT  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t\^",\^" -r "`"\n -F 1 -S'+ @@servername  
exec master..xp_cmdshell @cmd11

DECLARE @cmd21 NVARCHAR(4000) 
set @cmd21 = 'BCP [test1].[dbo].[myFilesNew] in  "C:\temp\myexport1.csv" -e c:\temp\errorfile.txt -c -T -t\^",\^" -r"`"\n -F 1 -S'+ @@servername
exec master..xp_cmdshell @cmd21


--truncate table [test1].[dbo].[myFilesNew]



---Reference: Lowell's
---http://www.sqlservercentral.com/Forums/Topic1315301-146-1.aspx

 

Export to Excel (.xlsx) with OPENDATASOURCE

  INSERT INTO OPENDATASOURCE (
  --SELECT * FROM OPENDATASOURCE(
  'Microsoft.ACE.OLEDB.12.0',
  'Data Source=c:\temp\myBook2.xlsx;Extended Properties=Excel 12.0')...[Sheet1$] (accountnumber, firstname, lastname, street1, street2, city, state)
    SELECT  accountnumber, firstname, lastname, street1, street2, city, state    FROM [dbo].[bcpView]


	--SELECT * FROM OPENDATASOURCE(
 -- 'Microsoft.ACE.OLEDB.12.0',
 -- 'Data Source=c:\temp\myBook2.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
 --http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

 

Export to Excel (.xlsx) File with OPENROWSET

--Create an Excel file \myBook2.xlsx with default Sheet1
--Past column names from the select statement to the first row in Excel

INSERT INTO 
OPENROWSET (      
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=c:\temp\myBook2.xlsx;HDR=YES', 
  'SELECT accountnumber, firstname, lastname, street1, street2, city, state FROM [Sheet1$]')
  SELECT  accountnumber, firstname, lastname, street1, street2, city, state    FROM [dbo].[bcpView]



---One more sample

 
if object_id('[aNewTable20150729]') IS nULL

Begin
CREATE TABLE [dbo].[aNewTable20150729](
	[accountnumber] [nvarchar](255) NULL,
	[COl1] [nvarchar](2550) NULL
) 
End

Insert into aNewTable20150729

SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
DBQ=c:\temp\test8.xlsx','SELECT * FROM [Sheet1$]') 

select *, len(COL1)  from aNewTable20150729

truncate table aNewTable20150729


 

Run bcp as Stored Procedure

Create Proc [dbo].[Sp__Use_bcp]
(
  @databaseName     varchar(30)='test1'
 ,@tableName     varchar(30)='table1'
 ,@filePath   varchar(80)='c:\temp\thisfile.txt'

 )
                                      
as
Set NoCount On
declare @cmd varchar(2000)
Begin
  --pipe delimited
      set @cmd = 'bcp.exe ' +  @databaseName + '.dbo.' + @tableName +  ' out '  +   @filePath + ' -c -t\^| -T -S'+ @@servername
   
      exec xp_cmdShell @cmd
 
End






ALTER Proc [dbo].[Sp__Use_bcp]
(
  @databaseName     varchar(30)='test1'
 ,@tableName     varchar(30)='table1'
 ,@filePath   varchar(80)='c:\temp\thisfile.txt'

 )
                                      
as
Set NoCount On
declare @cmd varchar(2000)
Begin
  --pipe delimited

DECLARE @sql AS VARCHAR(4000)

;with mycte as (select TABLE_CATALOG, table_name, COLUMN_NAME , [ORDINAL_POSITION] from [INFORMATION_SCHEMA].[COLUMNS]
where table_name=@tableName and TABLE_CATALOG=@databaseName)
 
SELECT @sql='" Select ' + stuff( (SELECT ', '+ Quotename(COLUMN_NAME,'''') +' as '+ COLUMN_NAME
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
+  ' UNION ALL Select ' + stuff( (SELECT ', CAST('+ Quotename(COLUMN_NAME,']') +' as varchar(500) ) as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
 + '  FROM ' + @databaseName + '..'+ @tableName  + ' " '
FROM mycte m1


      set @cmd = 'bcp.exe ' +  @sql +  ' queryout '  +   @filePath + ' -c -t\^| -T -S'+ @@servername
   
      exec xp_cmdShell @cmd
 
  End


 

How to Remove Double Quotation Marks while Importing a CSV File

From SSMS (2014) >>Right click on your database name >>Import and Export Wizard >>click Next>> From the General screen, Choos Flat File Source as Data Source and Browse to the text file (double quotes CSV file)
and In Format section: Delimited ; Text qualifier– type ” (a double quote ) . The other values leaves as default >> click Next>> click Next>>
Choose a SQL Server Native client as a destination and choose a database and table to import the text file >>click Next>>click Next>> Finish.

The key part is to use a ” as Text qualifier for the import.

Email Attachment with Exported CSV File from a Select and Remove Double Quotes in the File with Powershell

Import-Module -Name 'SQLPS' -DisableNameChecking  
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'

$svrname = "MC047012\MSSQL2014"
 $db ='test1'

 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

 

$myPath0 = "$env:temp\myfile$($dt)0.txt"
$myPath = "$env:temp\myfile$($dt).txt"
 
 
$q = @"
select * from  
dbo.table1
"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -Path $myPath0 
gc $myPath0 | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii


 
#Start-Sleep -s 10
 
#SMTP Relay Server
$SMTPServer = "info.company.com"
$From = "no_reply@company.com"
$To = "jli@comany.com"
$Subject = "This is Subject"
$Body = "This is Body Text"
 
$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, 25) 
$SMTPClient.EnableSsl = $false 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("no_reply", "my$Secret"); 
 
 
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($myPath)
$Msg.attachments.add($Attachment)
$SMTPClient.Send($Msg)