August 8, 2005 at 10:35 am
I have been told to remove first five character in each row from the Text file and put spaces in those five character. It's a fixed length file. Some rows are 2000 in length and some are 1000.
I thought I can download the text file in SQL table and remove first five character in each row and then again upload to the text file. Then I realise that in DTS package text file has a limited length column. It cannot store more then 175 character.
Can somebody give the idea how to do that.
Appreciated your help.
August 8, 2005 at 11:11 am
A fixed-length file has ALL records the same length! Yours does not, so it is not.
If you've got less than 65,535 rows you can do it in Excel in 1 minute. Do you need to use SQL Server?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 8, 2005 at 12:17 pm
/*
||=======================================================
|| No warranties. Use at own risk. This is intended to
|| help; if you find it unhelpful, please discard...
||
|| The @filename variable is the name of whatever
|| file you want to process.
||
|| For the value of @filename, supply the full path
|| from the perspective of the SQL Server services owner
|| on your server...
||
|| As written, this script will not overwrite your
|| original file but will instead automatically create
|| a new output file. If the input file, e.g., is
|| 'C:\filename.txt', the output file will be named
|| 'C:\filename2.txt'.
||
|| As written, the script presumes that the login that
|| owns MSSQLSERVER and SQLSERVERAGENT has permission
|| to read and write from the designated folder...
||
|| As written, the script presumes that the executing
|| login has 'xp_cmdshell' privileges...
||=======================================================
*/
DECLARE @filename VARCHAR (255)
SELECT @filename = ''
/*
||=======================================================
|| Other local variables...
||=======================================================
*/
DECLARE @outfilename VARCHAR (255)
, @cmd VARCHAR (8000)
, @bcp VARCHAR (8000)
, @msg VARCHAR (400)
, @delim CHAR (1)
, @osretc INT
/*
||=======================================================
|| Initialization...
||=======================================================
*/
SELECT @outfilename = REVERSE (STUFF (REVERSE (@filename)
, CHARINDEX ('.', REVERSE (@filename)) + 1
, 0
, '2'))
, @msg = ''
, @delim = CHAR (2)
IF OBJECT_ID ('tempdb..holdfile') IS NOT NULL
--THEN
DROP TABLE tempdb..holdfile
--END IF
CREATE TABLE tempdb..holdfile (detail VARCHAR (2000) NULL)
SELECT @bcp = 'BCP tempdb..holdfile'
+ ' @@INOROUT@@ "@@FILENAME@@"'
+ ' -S' + @@SERVERNAME
+ ' -T' -- trusted connection, can substitute -Usa -P<pwd>
+ ' -c -t' + @delim -- character format
+ ' -b1000' -- batch size 1000 rows
/*
||=======================================================
|| Ready, action...
||=======================================================
*/
SELECT @cmd = REPLACE (REPLACE (@bcp
, '@@INOROUT@@', 'IN')
, '@@FILENAME@@', @filename)
PRINT @cmd
EXEC @osretc = master..xp_cmdshell @cmd
IF @osretc != 0
--THEN
BEGIN
SELECT @msg = ' BCP IN failed'
GOTO SQL_EXIT
END
--END IF
UPDATE tempdb..holdfile
SET detail = ' ' + RIGHT (detail, DATALENGTH (detail) - 5)
SELECT @cmd = REPLACE (REPLACE (@bcp
, '@@INOROUT@@', 'OUT')
, '@@FILENAME@@', @outfilename)
PRINT @cmd
EXEC @osretc = master..xp_cmdshell @cmd
IF @osretc != 0
--THEN
BEGIN
SELECT @msg = ' BCP OUT failed'
GOTO SQL_EXIT
END
--END IF
SQL_EXIT:
IF OBJECT_ID ('tempdb..holdfile') IS NOT NULL
--THEN
DROP TABLE tempdb..holdfile
--END IF
PRINT @msg
August 8, 2005 at 2:00 pm
Phil.. Yes, the text file has more then 65535.
Lee.. I will try to use your script and see if this goes well.
Thanks a lot , you guys are great.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply