Rowcount a .txt file from SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Rowcount a .txt file from SQL Server

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2007-09-12 : 11:02:17

Hello experts,

I have a DTS job that imports files from an FTP server and loads them in SQL server. The job runs well. I have recently re-written the DTS to use SQL script to handle file renames, copy and deletes. These replace the work of some .bat file I was using. This part also works well.

The problem...

I have a list of text files: File1.txt, File2.txt, File3.txt etc

I need sql to get a list of the files and do a ROWCOUNT. Then I need to put that information into a table in SQL server. So far, I can get the filenames using:

INSERT INTO ImportLog
EXEC master.dbo.xp_cmdshell 'dir G:\FTPDownloads\'

But I cant get a rowcount!

Can anyone help please. This is driving me nuts.

Thanks

Bill

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-12 : 11:53:06

rowcount as in number of files
or
rowcount as in number of rows in each file?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

billsack
Starting Member

35 Posts

Posted - 2007-09-13 : 04:10:19

Rowcount the number of records in each file!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-13 : 05:31:42

lookup OPENROWSET in Books Online = BOL = Sql server help
you can open text files and query them with that

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-19 : 16:46:23

I did this a few years ago. I think I may still have the code at home. I'll try to find it and post it tomorrow.

Edit:

Here's a little something that will return a rowcount from xp_cmdshell.

C:\WINDOWS\system32>find /C /V "There should never be a stupid string like this in my file 6464646464646 booya!" results.txt

Find will return a count of all the records in results.txt that do not contain the specified string. Of course you might have that silly string in a file eventually, but somehow I doubt it will happen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources