Getting a row count from a text file using T-SQL.

Question

Getting a row count from a text file using T-SQL.

Timothy.Cloud

SSC-Addicted

Points: 472
More actions
September 30, 2009 at 8:07 am

#215845

Does anyone have an idea of how I can open a file using T-SQL and determine the number of rows in a text file?
Also, some of the rows are not records, they are headers, so I only want to count lines of a certain length.
Can this be done?
If not with T-SQL, how about SSIS?
Any help is appreciated.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 1

In T-SQL, you'd have to import the data into a table.

In SSIS, there might be a way, but you'd have to read the text file and check each row for the length. I'll post a note.

Are you sure that length tells you if it's data or not?

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 2

There may be a more elegant way of doing this, but you could create a temporary staging table and do the following:

SET NOCOUNT OFF

INSERT mydb.dbo.foo

FROM 'C:\foo.txt'

WITH (

FIELDTERMINATOR = '|'

)

SELECT @@ROWCOUNT AS FileRowCount

GO

Then set logic to either actually do the insert into a permanent table or back out and truncate the staging table based on business logic, etc. You'll want to play with the BULK INSERT options as far as headers, etc. My example assumes no header row and pipe-delimited columns.

MJM

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Mark Marinovic (9/30/2009)
There may be a more elegant way of doing this, but you could create a temporary staging table and do the following:
SET NOCOUNT OFF
INSERT mydb.dbo.foo
FROM 'C:\foo.txt'
WITH (
FIELDTERMINATOR = '|'
)
SELECT @@ROWCOUNT AS FileRowCount
GO
Then set logic to either actually do the insert into a permanent table or back out and truncate the staging table based on business logic, etc. You'll want to play with the BULK INSERT options as far as headers, etc. My example assumes no header row and pipe-delimited columns.
MJM

I think you left out the word BULK in that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 4

Mark Marinovic

SSCrazy

Points: 2472

October 1, 2009 at 8:24 am

#1060469

Yes I did - thanks for catching that.

MJM

Timothy.Cloud SSC-Addicted Points: 472 More actions · Answer 5

The only problem is that I really need to be able to audit which lines of the text file are actual data vs. headers/footers, etc.

So I'm considering using Python.

Evidently there is a way to have Python post that information to the database using ODBC.

Has anyone worked with manipulating text files with Python before?