Counting Lines in a File

Can I calculate the number of rows in a SQL Distributed Management Objects (SQL-DMO) bulk copy data file without importing the data?

I don't know a way to use SQL-DMO to calculate the number of rows in a data file. But all you need to do is count the number of lines in a file; if you know how many lines are in the file, you know how many rows you're trying to import. If you're using SQL-DMO, you're probably coding in Visual Basic (VB), and VB provides many ways to count the number of lines in a file. I'm not a VB guru and this isn't a VB column, so I won't explore those techniques here.

However, you can use xp_cmdshell from a T-SQL batch to count the number of lines in a file. Say you have a file called C:temphowbigami.txt that contains three lines. If you apply the script that Listing 1 shows, you get the following result set:

XPLineOut---------- C:TEMPHOWBIGAMI.TXT: 33

The script in Listing 1 uses the Windows Find command to count the number of lines in a file that contain the string nothingcontainsthisstring. You need to pick a string that the file can't contain so that the number of lines the Find command returns is the total number of lines in the file. Place the result set of the Find command in a local temporary table, then parse the result set to obtain the number of lines in the file. This solution isn't pretty, but it works. And you might discover that a creative use of xp_cmdshell will come in handy for you one day.

Of course, there's always more than one way to skin a cat, and SQL Server Magazine contributing editor Itzik Ben-Gan has suggested a more elegant solution. You can also achieve the result you want by using a linked server that uses the Microsoft Jet OLE DB Provider to access a text file. The code in Listing 2 shows how to configure a linked sever to point to a text file and issue queries against it.