August 19, 2010 at 3:14 am
Hello,
I got set of txt files in a folder I should create a stored procedure which reads the first line of each file in the folder and validate them.
If the first line (header) follow the rules applied in the validation to move the file to PASS folder if not to move them to REJECT folder
Can some one help me in coding of the procedure
August 19, 2010 at 6:10 am
Hello,
You can check the article How to read text file using t-sql xp_cmdshell command for reading text files within SQL Server.
Again xp_cmdshell can be used for moving files between file folders.
I hope that helps,
August 19, 2010 at 7:01 am
Thanks for reply. But I got read a set of files from the folder but not a single file
August 19, 2010 at 7:14 am
You can get the list of all files using the sql code at List Directory Files using T-SQL xp_cmdShell Stored Procedure
This code again uses the xp_cmdshell.
Then you can make a cursor and read first lines of each file.
August 19, 2010 at 7:31 am
Can you help me in providing any T-sql code for it.
I got a set of txt files. Each file contain different header(Firstline of the file)
for ex:
"Title""Forename""Surname""Full""Job Title""Department""Organisation""Address 1" - textfile1
"Title""Forename""Surname""Full Name""Job Title""Department""Organisation""Address 1" - textfile2
"Title""Forename" "Sur""Full Name""Job Title""Department""Organisation""Address 1" - textfile3
and so on say like 50 files
My task is to validate which files contain the header as
Title ForenameSurname FullName JobTitle DepartmentOrganisation Address1
and move that file in to pass folder and the rest of the files to reject folder
August 19, 2010 at 8:09 am
Is there a reason you can't do this in SSIS?
August 19, 2010 at 8:25 am
Here is a script that is working on my test environment
First I get the list of txt files in a specific directory using code demonstrated at List Directory Files using T-SQL xp_cmdShell Stored Procedure
Then I used this select statement for the cursor code. Please refer to Sample SQL Cursor and T-SQL Cursor Example Code
Later within the cursor, I used the t-sql code which I derived from How to read a text file using xp_cmdshell
DECLARE @name nvarchar(1000)
DECLARE file_cursor CURSOR FAST_FORWARD FOR
WITH CTE AS (
SELECT
id,
SUBSTRING(line,1,17) [date],
SUBSTRING(line,18,19) sizeordir,
SUBSTRING(line,37,100) name
FROM FileList
WHERE id > (
SELECT MIN(id) FROM FileList WHERE line LIKE '%<DIR>%..%'
) AND id < (SELECT MAX(id) - 2 FROM FileList)
)
SELECT name
FROM (
SELECT
id,
[date],
isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
name
FROM cte
) t
WHERE isFile = 1 and name like '%.txt'
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------
set nocount on
create table #file_contents (
line_number int identity,
line_contents nvarchar(4000)
)
declare @file_contents nvarchar(4000)
declare @new_line char(2)
set @new_line = char(13) + char(10)
declare @fullpath nvarchar(1000)
set @fullpath = N'C:\SQLDatabases\' + @name
declare @cmd nvarchar(1000)
set @cmd = N'type ' + @fullpath
insert #file_contents
exec master.dbo.xp_cmdshell @cmd
select top 1 @name, * from #file_contents
drop table #file_contents
set nocount off
---------------------
FETCH NEXT FROM file_cursor INTO @name
END
CLOSE file_cursor
DEALLOCATE file_cursor
I hope this helps you solving your problem.
August 19, 2010 at 8:25 am
No thers isn't any actually. I am new to sql and ssis.
I migrated the data from these flat files in to another flat file destination using SSIS, but no idea where to perform the validation and how to direct the flat files to multiple flat file destination.
August 19, 2010 at 8:28 am
Thanks for the script eralper, let me see if I can work this out with the given script
August 19, 2010 at 8:33 am
thisisdeepthy (8/19/2010)
No thers isn't any actually. I am new to sql and ssis.I migrated the data from these flat files in to another flat file destination using SSIS, but no idea where to perform the validation and how to direct the flat files to multiple flat file destination.
Well, I'd have a conditional split on the original files, with those that have the correct format going to the "pass" folder.
August 19, 2010 at 9:12 am
Script working gr8 but where should I perform the validation?
My task is to validate which files contain the header as
Title Forename Surname FullName JobTitle Department Organisation Address1
and move that file in to pass folder and the rest of the files to reject folder
August 19, 2010 at 11:23 am
Deepthy, write a CLR procedure (essentially a C# code) to perform the "unpack and validate file" logic and call it in your T-SQL procedure..i cant think of a way to do this thro any other T-SQLs
August 19, 2010 at 11:23 pm
My task is to validate which files contain the header as
Title Forename Surname FullName JobTitle Department Organisation Address1
and move that file in to pass folder and the rest of the files to reject folder
Hello,
After fetching the first row of each file, you can check if it is equal to a text variable which stores "Title Forename Surname FullName JobTitle Department Organisation Address1"
If equals you can say it validates otherwise validation fails.
August 20, 2010 at 3:22 am
cAN YOU PLEASE ALSO ADD THE SCRIPT WITH THIS TEXT VALIDATION IN THE CURSOR ? AND ALSO SLIGHT CHANGES IN THE REQ, NEED TO MOVE THE VALID FILES TO THE DB TABLE AND THE REJECTED TO LEAVE IN THE SAME FOLDER
August 20, 2010 at 4:11 am
Or
I need to have a code that will go into the txt files and see if the first row being a long string is exactly the same to 'Title Forename Surname FullName JobTitle Department Organisation Address1"
This a bit urgent, can you please respond imm
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply