Need help with file output

[Malchik]

Hi,

I have a SP that takes the content of a table and export it in a text file using FSO. I noticed that the variable is trimmed by the WRITELINE method. For example if I have a value ABC followed by 5 spaces, the spaces are dropped in the file. The only way to keep the spaces is to add a bogus character at the end of each dataline... which is not convinient. I copied the code below. Does anyone has an idea how to workaround that problem? or maybe I missed something. Thanks (ps i do not want to use DTS)

CODE:

DECLARE @Path AS VARCHAR(100)
DECLARE @FileSystem AS INT
DECLARE @FileHandle AS INT
DECLARE @FileName AS VARCHAR(150)
DECLARE @RetCode AS INT
DECLARE @Text AS VARCHAR(200)

-- SET THE DESTINATION PATH (HAS TO BE LOCAL TO THE SQL SERVER) AND FILE NAME
SET @FileName = 'C:\temp\Test.txt'

-- CREATE AN FSO OBJECT
EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT

-- OPEN (CREATE) THE FILE AND CREATE A FILEHANDLE
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @FileName, 2, 1

-- SAVE THE DATA IN THE CSV FILE
SET @RS = CURSOR FOR SELECT exp_Data FROM tmpExport ORDER BY exp_ID
OPEN @RS
FETCH NEXT FROM @RS INTO @Text
WHILE @@FETCH_STATUS = 0

BEGIN
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WRITELINE' , NULL , @Text
FETCH NEXT FROM @RS INTO @Text
END

CLOSE @RS
DEALLOCATE @RS

-- CLOSE THE CSV FILE
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close'

-- DESTROY THE FILEHANDLE
EXEC sp_OADestroy @filehandle

-- DESTROY THE FSO OBJECT
EXEC sp_OADestroy @FileSystem

Mal'chik

 

[foxdev]

Two ideas:

1) You are defining @Text as varchar; try char(200) instead, since varchar by definition is variable-length.

2) I note in your code that you are referring to the file as a CSV; what about forcing quotes around the @Text value on output.

Try #1 first, though.

--------------

http://www.liquidsql.com

SQLS metasearch