Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

Tsql 2005 Procedure


I need a procedure that will go to a specified file path. Read the first line which has the today's date and the number of rows in the file after the pipe
example
20101130|100

Then I need to verify that the number of rows matches on the file match what the header says...
0
Leo Torres
Asked:
Leo Torres
  • 13
  • 10
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmmm.
this is a bit much for 1 single question on EE (EE is not a rent-a-coder site!)

you should split up the problem into specific issue you have.

* procedure parameter to pass file name
* read file
* split data
* use data

please clarify what the issue is that you have.
0
 
Leo TorresSQL DeveloperAuthor Commented:

So created a procedure that downloads a file from FTP site..

I was thinking about reading this file and putting the data in a temp table in the procedure. Then do my counts but not sure and verification..

I just need help with getting the file and being able to count the rows in the file. temp table was my first guess but not sure if that is the best way or if it can be done.. Once I complete the verification I will drop the table.

sorry if my questions was a bit vague..

Thank!!
0
 
Leo TorresSQL DeveloperAuthor Commented:

Maybe I should have phrased the question this way.. How can I create a table from a file... Given the file location.. Once I have table I can do my operations
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rmm2001Commented:
Use SSIS.

File source would be the file you downloaded (Flat file). Then add a Row Count component to get how many rows were in your file. Then read the file into your destination table. Or do a row sampling to get the data that you want. You could also use an oledb command to manually insert the data using whatever business rules you want to add into the insert.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

if file is fixed format then bulkinsert will do

check this link.

http://msdn.microsoft.com/en-us/library/ms188365.aspx
BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Actually I did try that.


it but everything in 1 row
gave me this

col1                       col2
20101129           3 Leo | 1 Leo | 2 Test | 3


this is what i in file
20101129 | 3
Leo | 1
Leo | 2
Test | 3

I will use bulk or bcp at this point I just want this to work..

Thanks for the help.. what am  doing wrong??

Here is what I tried
BULK insert dbo.testTable from 'E:\Vendor\Feeds\Tranzact\MatchOfferRates'
With
(
	Fieldterminator = '|',
	RowTerminator = '\n'

)

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
hi,

can you share your file now
0
 
Leo TorresSQL DeveloperAuthor Commented:
which file??
0
 
Bhavesh ShahLead AnalysistCommented:

The one you importing

E:\Vendor\Feeds\Tranzact\MatchOfferRates
0
 
Leo TorresSQL DeveloperAuthor Commented:
data file?

This is the datafile
This is what in the file..Its a dummy file I created

20101129 | 3
Leo | 1
Leo | 2
Test | 3
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

problem is your row terminator is not proper.
Just open file in wordpad and just save.

file should be attached format.
TEST.txt
0
 
Bhavesh ShahLead AnalysistCommented:
sorry this one
test.txt
0
 
Leo TorresSQL DeveloperAuthor Commented:

Think I know where your getting at I see you removed the last cartridge return but I used your exact file CTRL+A then pasted it to my file executed and gave me the same thing
sampleFile.txt
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

i just import your file with ur code.
its working...

your table contains 2 columns....right??

it should work....

which sql version you using
TEST.txt
0
 
Leo TorresSQL DeveloperAuthor Commented:
SQl 2005

Useing this to run Code

exec dbo.sp_DynamicTable 'E:\Vendor\Feeds\Tranzact\20101130.Sapient.TranzactMatchOfferRates'
/*
Created: Edmond Fox
Purpose: Creates Dynamic tables 
Edited By: Leo Torres
*/
Alter Procedure dbo.sp_DynamicTable(
@FilePath varchar(max),
@alertDistro varchar(255) = 'ltorres@sapient.com'--'msolares@sapient.com; embridgetrackrbc@sapient.com; embridgetracksupport@sapient.com'

)
AS

BeGIN TRY

declare @filename		varchar(max),
		@myStr			varchar(1500), 
		@countStr		varchar(100),
		@cmd			varchar(4000),
		@tableString	varchar(max),
		@tableName		varchar(max),
		@counter		int,
	 	@columnCount	int,
        @caughtError	varchar(8000),
		@sqlString      varchar(max)

set @tableName		= 'testTable'
Set @countStr		= '|'
set @tableString	= 'create table dbo.' + @tableName + ' ( '


Select @filename = Right(@FilePath,CharIndex('\',Reverse(@FilePath))-1)
Print @filename


truncate table Vendor_DataMart.dbo.fileFeeler
SET @cmd ='bcp Vendor_DataMart.dbo.fileFeeler in "' + @FilePath + '" 
			-f "E:\Vendor\bcpFormatFiles\fileFeeler.fmt" -m1 -T -F2 -L2'
Print @cmd
EXEC @myStr = MASTER.DBO.XP_CMDSHELL @cmd, no_output

select top 1 @myStr =  string from Vendor_DataMart.dbo.fileFeeler (nolock)


Select @columnCount = (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr)+1 

--loop
set @counter = 1
while (@counter <= @columnCount+1)
	begin
		set @tableString = @tableString + 'col' + cast(@counter as varchar(100)) + ' varchar(max), '
		set @counter = @counter + 1
	end

set @tableString = @tableString + ')'
set @tableString = replace(@tableString, ', )',' )')
print @tableString

IF OBJECT_ID('dbo.testTable') Is Not NULL
	Drop table dbo.testTable

exec(@tableString)

set @sqlString ='BULK insert dbo.testTable from '''+ @FilePath +'''
With
(
	Fieldterminator = ''|'',
	RowTerminator = ''\n''

)'
print @sqlString
exec (@sqlString)

END TRY 
BEGIN Catch
select  @caughtError = ERROR_MESSAGE()

	PRINT @caughtError
	EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'
END Catch 

PRINT 'End --- ' + convert(varchar(20), getDate())


Select * from dbo.testTable

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
hi,

i was trying to solve ur problem now but now need to understand.

and its 1.40 AM,so need to go.

sorry,I will be get back to you in morning.

meanwhile,other experts surely help u.

tc

bye
0
 
Bhavesh ShahLead AnalysistCommented:

Hi,

E:\Vendor\Feeds\Tranzact\20101130.Sapient.TranzactMatchOfferRates
is ur file name???

can you share this file....

btw can you try to upload my file.

create table with two columns and try to upload....

tell me that is uploading...??
0
 
Leo TorresSQL DeveloperAuthor Commented:
i already sent you the file its the test file I sent you...
0
 
Leo TorresSQL DeveloperAuthor Commented:
Think I know what the problem is will send to you once in the office
0
 
Leo TorresSQL DeveloperAuthor Commented:
did this using bcp much cleaner..

Thanks for the help!!


USE [Vendor_DataMart]
GO
/****** Object:  StoredProcedure [dbo].[sp_DynamicTable]    Script Date: 12/01/2010 14:05:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Created: Edmond Fox
Purpose: Creates Dynamic tables 
Edited By: Leo Torres
*/
ALTER Procedure [dbo].[sp_DynamicTable](
@FilePath varchar(max),
@alertDistro varchar(255) = 'embridgetracksupport@sapient.com'

)
AS

BeGIN TRY

Print 'Running ... Procedure.. sp_DynamicTable'
declare @filename		varchar(max),	@myStr			varchar(1500), 
		@countStr		varchar(100),	@cmd			varchar(4000),
		@tableString	varchar(max),	@tableName      varchar(255),
		@counter		int,			@columnCount	int,
        @caughtError	varchar(8000),	@sqlString      varchar(4000),
		@DummyString    varchar(max),	@formatFile		varchar(max)

truncate table CapitalOne_DataMart.dbo.fileFeeler
IF OBJECT_ID('dbo.DynamicTable') Is Not NULL
	Drop table dbo.DynamicTable
/****************** CREATE IMPORT TABLE ************************/
Set @tableName = 'DynamicTable'
Set @countStr		= '|'
set @tableString	= 'create table dbo.' + @tableName + ' ( '
Select @filename	= Right(@FilePath,CharIndex('\',Reverse(@FilePath))-1)
Print @filename

SET @cmd ='bcp Vendor_DataMart.dbo.fileFeeler in "' + 
				@filepath + '" -f "E:\Vendor\bcpFormatFiles\fileFeeler.fmt" -m1 -T -F2 -L2'

Print @cmd
EXEC MASTER.DBO.XP_CMDSHELL @cmd

--loop
set @counter		= 1
select top 1 @myStr =  string from Vendor_DataMart.dbo.fileFeeler (nolock)
Select @columnCount = (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr)+1 

while (@counter <= @columnCount)
	begin
		set @tableString = @tableString + 'col' + cast(@counter as varchar(100)) + ' varchar(max), '
		set @counter = @counter + 1
	end


set @tableString = @tableString + ')'
set @tableString = replace(@tableString, ', )',' )')


print @tableString

if @tableString <> 'create table dbo.DynamicTable ( )'
begin 
print 'Im inside Dynamic create'
	exec(@tableString)
print '****************** CREATE BCP FORMAT FILE ************************'
/****************** CREATE BCP FORMAT FILE ************************/
	set @formatFile	= @tablename + '.fmt'

	set @cmd = 'bcp Vendor.dbo.' + @tablename + ' format nul -c -f  E:\Vendor\bcpFormatFiles\' + @formatFile + ' -t^| -r \n -T'
	print @cmd
	EXEC MASTER.DBO.XP_CMDSHELL @cmd

Print '****************** IMPORT DATA ************************'
/****************** IMPORT DATA ************************/
	SET @sqlString ='bcp Vendor_DataMart.dbo.DynamicTable in "'+ @FilePath + '" -f "E:\Vendor\bcpFormatFiles\' + @formatFile + '" -m1 -T'

	Print @sqlString 
	EXEC @DummyString = MASTER.DBO.XP_CMDSHELL @sqlString
	print @DummyString
return 1
END

Else 
Begin
print 'Should not be here on good file'
	Select @caughtError = 'File Can not be processed not in correct Format'
		EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'

return 0
End

END try

BEGIN Catch

select  @caughtError = 'File needs to be pipe delimited, Process Failed due to file Format. Please verify'--ERROR_MESSAGE()

	PRINT @caughtError
	EXEC dbo.sp_SQLSMTPMail  
		@vcFrom='notifications@bridgetrack.com', 
		@vcTo = @alertdistro,
		@vcSubject='ALERT!!!! Failed Retrieving Tranzact File data-->sp_DynamicTable', 
		@vcBody=@caughtError,
		@vcBodyType='TextBody'
END Catch 



--exec dbo.sp_DynamicTable 'E:\CapitalOne\Feeds\20101117.Vendor.AnalysisRequest'

--Select * from dbo.DynamicTable

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
sorry buddy...

I'm not clear from your Question.
You asking me or telling me??

If u asking m then yeah bcp is also one of good way(But I didnt know how to use)
I'm using bulk insert as thats simple and plus xp_cmdshell is disabled at our end...
0
 
Leo TorresSQL DeveloperAuthor Commented:
Solution was not working on my end
0
 
Leo TorresSQL DeveloperAuthor Commented:
Sorry I ment to close this the last time..
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

not cleared.
if you need more assistance,please dont close now.
open the Question and we will try to help u
0
 
Leo TorresSQL DeveloperAuthor Commented:
no my proc did finally work.. I do appreciate the help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now