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              ""
 

*/

------------