|
Execute the following
Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the import of CSV and other delimited files into SQL Server database.
WARNING: xp_cmdshell & SQL Server security
-- List only the subdirectiories (subfolders) in the directory (folder)
CREATE TABLE #Folders (FolderName sysname NULL, ID INT identity(1,1));
DECLARE @ParentFolder nvarchar(256)='C:\"program files"';
DECLARE @Command nvarchar(512)= 'DIR '+@ParentFolder+' /AD /B';
INSERT #Folders (FolderName)
EXEC xp_cmdshell @Command;
DELETE #Folders WHERE FolderName is null;
SELECT * FROM #Folders;
------------
-- Alternative way of getting directory structure
DECLARE @RootFolder nvarchar(256)='C:\program files';
EXEC master.sys.xp_dirtree @RootFolder;
------------
-- Import comma delimited file into sql server quick SYNTAX - Comma Separated Values
-- Import data from csv flat file - load csv file into sql server using BULK INSERT
BULK INSERT CSVdata
FROM 'C:\data\export\sales2012.csv'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
------------
-- SQL Server import flat file into database temporary table
-- MSSQL bulk insert - import comma delimited file into sql
USE AdventureWorks2008;
CREATE TABLE #NotePad (
Line VARCHAR(512))
BULK INSERT #NotePad
FROM 'f:\doc\logins\websitex\login.txt'
WITH (ROWTERMINATOR = '\n')
SELECT *
FROM #NotePad
GO
------------
-- T-SQL import delimited flat file with BULK INSERT into database
-- MSSQL dynamic query - dynamic sql - import delimited file into sql
USE tempdb;
CREATE TABLE Product (
ProductName varchar(64),
Color varchar(16),
ListPrice smallmoney)
GO
/* Content of ProductExport.txt
ProductName|Color|ListPrice
HL Road Frame - Black, 58|Black|1431.5
HL Road Frame - Red, 58|Red|1431.5
Sport-100 Helmet, Red|Red|34.99
Sport-100 Helmet, Black|Black|34.99
Mountain Bike Socks, M|White|9.5
Mountain Bike Socks, L|White|9.5
Sport-100 Helmet, Blue|Blue|34.99
....
*/
DECLARE @SQL nvarchar(max), @PathFileName varchar(256)
SET @PathFileName = 'f:\data\import\csv\ProductImport.txt'
-- SQL string concatenation - string concat - string append
SET @SQL = 'BULK INSERT Product FROM '''+@PathFileName+
''' WITH (FIELDTERMINATOR = ''|'', FIRSTROW=2) '
PRINT @SQL -- test & debug
-- Dynamic SQL execution
EXEC sp_executesql @SQL
GO
SELECT TOP (5) * FROM Product ORDER BY NEWID()
GO
/* Results
ProductName Color ListPrice
Classic Vest, L Blue 63.50
Mountain-500 Silver, 52 Silver 564.99
Touring Front Wheel Black 218.01
HL Road Frame - Red, 58 Red 1431.50
LL Mountain Frame - Black, 42 Black 249.79
*/
-- Cleanup
DROP TABLE tempdb.dbo.Product
------------
------------
-- SQL Server T-SQL BCP command for data export/import to/from disk file
------------
/* Command prompt:
bcp AdventureWorks2008.Production.Product out c:\export\Prod1.csv -c -S YOURSRVR -T
*/
-- Windows authentication (-T) - exporting table
EXEC xp_cmdshell 'bcp AdventureWorks2008.Production.Product out c:\export\Prod1.csv -c -S YOURSRVR -T';
/*
NULL
Starting copy...
NULL
504 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 47 Average : (10723.40 rows per sec.)
NULL
*/
-- SQL Server authentication - exporting table
EXEC xp_cmdshell 'bcp AdventureWorks2008.Production.Product out c:\export\Prod1.csv -c -S YOURSRVR -Usa -PLondon007';
-- 504 rows copied.
-- BCP exporting query result
EXEC xp_cmdshell 'bcp "SELECT * FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0" queryout c:\export\Prod1.csv -c -S YOURSRVR -T';
-- 304 rows copied.
------------
------------
-- SQL Server import CSV data with openrowset - select into table create
------------
SELECT *, ColumnZ= convert(char(10),'')
INTO dbo.stageInvoice20120901
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=e:\data\xml\;Extensions=txt;','SELECT * FROM invdata2.txt')
------------
-- Import csv file with bcp - bcp command - bcp utility import / export
exec master.dbo.xp_cmdshell 'bcp tempdb.dbo.InventoryStage in
F:\data\bcpdemo\inventory20120205.txt -w -T -S"YOURSRVR\SQL2008"'
------------
-- SQL import file line by line using INSERT EXEC
-- Microsoft SQL Server T-SQL xp_cmdshell - extended system procedure
USE AdventureWorks2008;
CREATE TABLE #FlatFile (
Line VARCHAR(128))
INSERT #FlatFile
EXEC MASTER..xp_cmdshell 'type e:\data\text.txt'
SELECT *
FROM #FlatFile
-- Cleanup
DROP TABLE #FlatFile
GO
------------
-- MSSQL import csv file using BULK INSERT - sql import comma delimited file
USE AdventureWorks2008;
CREATE TABLE #FlatFile (
Line VARCHAR(128))
BULK INSERT #FlatFile
FROM 'F:\data\export\Department.txt'
SELECT *
FROM #FlatFile
GO
/* Partial results
Line
DepartmentID,Name,GroupName,ModifiedDate
1,Engineering,Research and Development,2008-02-19 15:38:58.470000000
2,Tool Design,Research and Development,2008-02-19 15:38:58.470000000
*/
-- Cleanup
DROP TABLE #FlatFile
GO
------------
-- SQL Server import csv file into database using BULK INSERT
-- CSV - Comma Separated Values
-- SQL Server .fmt (format) file is bcp format file
-- T-SQL dynamic SQL, dynamic query
DECLARE @DynamicSQL NVARCHAR(512)
DECLARE @csvFilePath VARCHAR(64),
@bcpFormatPath VARCHAR(64)
SET @csvFilePath = 'f:\data\feed\inventory20151023.txt'
SET @bcpFormatPath = 'f:\data\format\inventory.fmt'
SET @DynamicSQL = 'BULK INSERT [PartsInventory] FROM ''' + @csvFilePath + ''' WITH (formatfile = ''' + @bcpFormatPath + ''')'
PRINT @DynamicSQL -- test & debug
/*
BULK INSERT [PartsInventory] FROM 'e:\data\inventory20120201.txt'
WITH (formatfile = 'e:\data\inventory.fmt')
*/
EXEC SP_EXECUTESQL @DynamicSQL
GO
------------
-- SQL Server .fmt (bcp format) file content example
-- 8.0 refers to SQL Server version number (SQL Server 2000)
-- MSSQL .fmt (format) file is for Production.Product table
/*
8.0
25
1 SQLINT 0 4 "" 1 ProductID ""
2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 50 "" 3 ProductNumber SQL_Latin1_General_CP1_CI_AS
4 SQLBIT 0 1 "" 4 MakeFlag ""
5 SQLBIT 0 1 "" 5 FinishedGoodsFlag ""
6 SQLNCHAR 2 30 "" 6 Color SQL_Latin1_General_CP1_CI_AS
7 SQLSMALLINT 0 2 "" 7 SafetyStockLevel ""
8 SQLSMALLINT 0 2 "" 8 ReorderPoint ""
9 SQLMONEY 0 8 "" 9 StandardCost ""
10 SQLMONEY 0 8 "" 10 ListPrice ""
11 SQLNCHAR 2 10 "" 11 Size SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 2 6 "" 12 SizeUnitMeasureCode SQL_Latin1_General_CP1_CI_AS
13 SQLNCHAR 2 6 "" 13 WeightUnitMeasureCode SQL_Latin1_General_CP1_CI_AS
14 SQLDECIMAL 1 19 "" 14 Weight ""
15 SQLINT 0 4 "" 15 DaysToManufacture ""
16 SQLNCHAR 2 4 "" 16 ProductLine SQL_Latin1_General_CP1_CI_AS
17 SQLNCHAR 2 4 "" 17 Class SQL_Latin1_General_CP1_CI_AS
18 SQLNCHAR 2 4 "" 18 Style SQL_Latin1_General_CP1_CI_AS
19 SQLSMALLINT 1 2 "" 19 ProductSubcategoryID ""
20 SQLINT 1 4 "" 20 ProductModelID ""
21 SQLDATETIME 0 8 "" 21 SellStartDate ""
22 SQLDATETIME 1 8 "" 22 SellEndDate ""
23 SQLDATETIME 1 8 "" 23 DiscontinuedDate ""
24 SQLUNIQUEID 1 16 "" 24 rowguid ""
25 SQLDATETIME 0 8 "" 25 ModifiedDate ""
*/
------------
SSIS Import/Export Wizard video:
http://www.youtube.com/watch?v=9Wmdhnx1niU
Related articles:
SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
Import multiple Files to SQL Server using T-SQL
|
