Solved

Tsql 2005 Procedure

Posted on 2010-11-30
25
402 Views
Last Modified: 2012-06-27

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
Comment
Question by:Leo Torres
  • 13
  • 10
  • +1
25 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility

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
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility

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

Expert Comment

by:rmm2001
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
hi,

can you share your file now
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
which file??
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

The one you importing

E:\Vendor\Feeds\Tranzact\MatchOfferRates
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
sorry this one
test.txt
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Author Comment

by:Leo Torres
Comment Utility

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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

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
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
i already sent you the file its the test file I sent you...
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Think I know what the problem is will send to you once in the office
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
Comment Utility
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
 
LVL 8

Author Closing Comment

by:Leo Torres
Comment Utility
Solution was not working on my end
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Sorry I ment to close this the last time..
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
hi,

not cleared.
if you need more assistance,please dont close now.
open the Question and we will try to help u
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
no my proc did finally work.. I do appreciate the help!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now