Counting Records from Text Files

Question

Counting Records from Text Files

robstp

Old Hand

Points: 384
More actions
December 10, 2003 at 10:57 am

#177371

I am importing multiple text files into a single table. I need to create a report that identifies the number of records from each file. How can I do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

if the rows are delimited by CRLF all you have to do is COUNT the number of CRLF in the file and loop through all of them.

This is Fairly easy done with a script

HTH

* Noel

philcart SSC-Forever Points: 47796 More actions · Answer 2

Do you need the count before or after you import the data? If you need it after the import you can use an ActiveXScript task to return the RowsComplete value from the Datapump task.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

noeld SSC Guru Points: 96590 More actions · Answer 3

I am 100% with Phill here you didn't specify if you need the count before or after and I believe for after Phill's a very good choice

* Noel

robstp Old Hand Points: 384 More actions · Answer 4

Thanks guys

I do need it after the load. I will try your suggestions.

StormNorm SSCommitted Points: 1654 More actions · Answer 5

Here is a little VB Script that will do the job. You can put it into an ActiveX task in your DTS package. It writes the count to a file, but you could do anything you like with it.

quote:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Count number of lines in file without loading whole file into memory...
strFileToWrite = "c:\MyCountOutput.txt"
strFileToRead = "\\MyServer\MyShare\MyFileToRead.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = CreateObject("Scripting.FileSystemObject")
Set tf = fso.OpenTextFile(strFileToRead)
x = 0 'Set counter
'Count the lines...
i = 1: iP = 0
Do While tf.AtEndOfStream <> True
sMain = tf.ReadLine
x = x + 1
Loop
tf.Close
set tf = nothing
set fso = nothing
'Write the count to a text file...
'2 = Open for writing (overwrite old file); TRUE means create the file if does not exist
set tf2 = fso2.OpenTextFile(strFileToWrite, 2, TRUE)
tf2.Write x
tf2.Close
set tf2 = nothing
set fso2 = nothing
Main = DTSTaskExecResult_Success
End Function