How to Import multiple csv/text files into SQL Server DB

Importing data into SQL Server DB from CSV/Text Files

This Tutorial uses SQL Server 2012 and CSV file that contains data to be import.

• Step 1: Create File Paths And Import csv File

  - Create folder "C:\ImportingFilesToDb".
  - Create Subfolder 1 =>"C:\ImportingFilesToDb\NewFiles" : The csv file will be kept in this location.
  - Create Subfolder 2 =>"C:\ImportingFilesToDb\Archive" : The csv file will be archived in this location after being processed.
  - Download the CSV File and move it into subfolder 1

• Step 2: Create SQL Tables and the stored Procedure

  - Create Table 1 => price_index : Holds imported csv data.
  CREATE TABLE [dbo].[price_index]( [series_reference] [varchar](100) NULL, [period] [varchar](100) NULL, [data_value] [varchar](100) NULL, [status] [varchar](100) NULL, [units] [varchar](100) NULL, [subject_] [varchar](100) NULL, [group_] [varchar](100) NULL, [series_title_1] [varchar](100) NULL, [series_title_2] [varchar](100) NULL, [series_title_3] [varchar](100) NULL, [Series_title_4] [varchar](100) NULL, [series_title_5] [varchar](100) NULL ) ON [PRIMARY]
  Download Script Create price_index table
  - Create Table 2 => files_logtable: Keepsrecords of processed file names.
  CREATE TABLE [dbo].[files_logtable]( [id] [int] IDENTITY(1,1) NOT NULL, [Query] [varchar](1000) NULL, [Importeddate] [datetime] NULL ) ON [PRIMARY]
  Download Script Create files_logtable
  
  - Create Store Procedure; The procedure contains code to read and import data from CSV files into SQL server.
  Download Script Create import_files procedure
  

Step 3: Execute Stored Procedure

Execute the Procedure as below.

Note: The stored procedure uses xp_cmdshell to import files from CSV. However, this is turned off by default and the error below will appear in the Management studio output.

Run commands below to enable xp_cmdshell on SQL Server.
       

          

 

Run Stored Procedure again:
Exec import_files 'C:\ImportingFilesToDb\NewFiles\', '*.csv', 'price_index'

The stored procedure accepts 3 parameters, the first parameter is the path where files to be imported are, the second parameter i.e *.csv refers to all CSV files and the third parameter refers to the database table where data is to be imported.

See final output below.

 by : Joseph Lusoma