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).


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