DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to read notepad text file into a table?

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 

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE