SQL Server Performance Forum – Threads Archive

Pulling a text file into a table and keeping order

I need to change text inside a text file. The order needs to stay the same. The file name won’t be known until I pull a directory list. Attempt #1: I have tried to pull the file in with BCP into a table with two columns. The table has a clustered index on the first column and is an identity. The second column is a char(1000). I figured that this would keep the order of the file and it would be easy to output back out with a selective bcp. I couldn’t get the bcp to ignore the identity column. Attempt #2: I tried to just setup a single text column and load the whole file into a single row. It would load each line into one row. I tried to remove the row delimiter, but it gave me the error ‘Incorrect host-column number found in BCP format-file’. I assume that it just thought that I was giving it a blank for a delimiter instead. Any other ideas? This has to be fully automated and I would like to have it all in TSQL with minimal cmdshell. I tried to do the BULK INSERT command, but you can’t have a variable as the filename with that command. Thanks in advance for any responses to this post. Live to Throw
Throw to Live

You can use BULK INSERT with a variable if you build up a string and then executing it. Eg: DECLARE @MyFile varchar(100)
SET @MyFile = ‘C:Test.txt’
DECLARE @cmd varchar(2000) SET @Cmd = ‘BULK INSERT MyTable FROM ”’[email protected]+”’WITH (TABLOCK, MAXERRORS=0)’
EXEC (@cmd)
Does that help you?

EXEC (@cmd) I will make a mental note of that command. That will be very useful and help me to avoid shelling out do dos. Live to Throw
Throw to Live

… and if you’re using with user database context use EXEC MASTER..XP_CMDSHELL [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

I would like to minimalize cmdshell usage. Live to Throw
Throw to Live

I remembered how I did it before. I used a format file for the bcp. I new that I had ran into this problem before. Here’s how I fixed it: 1. Create table with identity column and char(1000) column
2. put clustered index on identity column, order ascending
3. bcp in with format file only specifying the char(1000) column
4. the delimiter for the column must be
This works fine and I can change any line of the file with and update command. Thanks for any responses this post generated.
Live to Throw
Throw to Live

]]>