Schedule importing flat files with different names into SQL server 2014

Question

As I am a beginner in SQL Server and my scripting is not very polished yet. I need suggestions on the below issue.

I receive files from a remote server to my machine (around 700/day) as follows :

ABCD.100.1601310200
ABCD.101.1601310210
ABCD.102.1601310215

Naming Convention: Here the first part 'ABCD' remains the same, middle part is a sequence id which is in incremental order for every file. The last part is time stamp.

File structure The file does not have any specific extension but can be opened with notepad/excel. Therefore can be called as flat file. Each files consist of 95 columns and 20000 rows fixed with some garbage value on top 4 and bottom 4 rows of column 1.

Now, I need to make a database in SQL server where I can import data from these flat files using a scheduler. Suggestion needed.

You can schedule three seperate jobs to import each file... Would that work for you? — TT., Jan 31 '16 at 10:54
On daily basis i would have around 700+ files each with different file name (incremental in number). For now, I am copying these files (with windows batch) to one text file, and from their I am importing it to database. But I am searching for a built in solution in SQL server. — Ayaz, Jan 31 '16 at 11:02
*"700+ files"* > That is important information that should be included in your question! — TT., Jan 31 '16 at 11:05
Also where are those files located? In a single folder containing no other files? Are the number of files fixed or can new files be arbitrarily added? Do they all share the same structure? That is important information to add to your question. Without exact specifics it is hard to answer your question. — TT., Jan 31 '16 at 11:07
This information should be in your question. Edit your question using the `edit` link underneath your question and add that information in your question. Specifics of your question should not be in the comments section, people should not have to delve through comments to see what the specifics are. Also, you didn't specify whether the number of files is arbitrary (any number of files sharing a certain pattern in the name of the files). — TT., Jan 31 '16 at 11:24
Did you find a way to do this? Did my answer help you in any way? — TT., Feb 02 '16 at 13:46

TT. · Answer 1 · 2016-10-29T11:29:17.627

There are probably other ways of doing this, but this is one way:

Create a format file for your tables. You only need to create it once. Use this file in the import script in step 2.
Create an import script based on OPENROWSET(BULK '<file_name>', FORMATFILE='<format_file>'
Schedule the script from step 2 in SQL Server to run against the database you want the data imported in

Create the format file

This creates a format file to be used in the next step. The following script creates a format file in C:\Temp\imp.fmt based on an existing table (replace TEST_TT with the database you are importing to). This creates such a format file with a , as field seperator. If the files have tab as seperator, remove the -t, switch.

DECLARE @cmd VARCHAR(8000);
SET @cmd='BCP TEST_TT.dbo.[ABCD.100.1601310200] format nul -f "C:\Temp\imp.fmt" -c -t, -T -S ' + (SELECT @@SERVERNAME);
EXEC master..xp_cmdshell @cmd;

Before executing this you will to reconfigure SQL Server to allow the xp_cmdshell stored procedure. You only need to do this once.

EXEC sp_configure 'show advanced options', 1 
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1 
GO
RECONFIGURE
GO

import script

This script assumes:

The files need to be imported to separate tables
The files are located in C:\Temp
The format file is C:\Temp\imp.fmt (generated in the previous step)

SET NOCOUNT ON;
DECLARE @store_path VARCHAR(256)='C:\Temp';
DECLARE @files TABLE(fn NVARCHAR(256));
DECLARE @list_cmd VARCHAR(256)='DIR ' + @store_path + '\ABCD.* /B';
INSERT INTO @files EXEC master..xp_cmdshell @list_cmd;
DECLARE @fullcmd NVARCHAR(MAX);
SET @fullcmd=(
    SELECT
        'IF OBJECT_ID('''+QUOTENAME(fn)+''',''U'') IS NOT NULL DROP TABLE '+QUOTENAME(fn)+';'+
        'SELECT * INTO '+QUOTENAME(fn)+' '+
        'FROM OPENROWSET(BULK '''+@store_path+'\'+fn+''',FORMATFILE=''C:\Temp\imp.fmt'') AS tt;'
    FROM 
        @files 
    WHERE 
        fn IS NOT NULL
    FOR XML PATH('')
);
EXEC sp_executesql @fullcmd;

Schedule importing flat files with different names into SQL server 2014

1 Answers1