Here we go... this is just one way to skin this particular cat. If you end up with some really large files or files with line lengths longer than 8k bytes, there are some faster ways to do it all in T-SQL that will require either a CLR splitter or a bit of help from xp_CmdShell, but this method should work just fine for you.

1. To test this code, I used what you posted to create a "urso47Test01.txt" file in C:\Temp. I've attached that file to this post if anyone else wants to "play" with the code. That ZIP file contains everything you need for this example.

2. The code has a couple of dependencies that are very easily resolved. Just read the attached code to find out what those dependencies are. I've included those objects in the attachments, as well.

3. Other than that, just about anything else I say would be superfluous because I document just about everything you need to know in the code. Even the BCP format file is documented within. READ THAT DOCUMENTATION because you may need to make a change before you run the code. The documentation can stay within the file.

4. Last but not least, if you don't know what a "BCP Format File" is, Books Online (Press the {f1} key to get there) is your friend.

5. If you need help running this stored procedure over many files, please post back and we'll give you a leg up... especially since you're not using SSIS to do this.

6. I didn't take the time to export the data to an Excel file. That can't really be easily done from T-SQL unless you can use xp_CmdShell. Typically, its much easier to modify the stored proc to simply capture all of the data you want in a table and then tell Excel to import it or create a "pass through" query to load the data into the spreadsheet.