March 7, 2008 at 4:04 am
I have problem... We do weekly exports and emailing from our database to one of customers. The problem is that our customer needs text file to look like this:
id,vastausa,vastausb,pvm,tunnus,vastausc,vastausd
1,"gfdgfg","gfdgfg",,"","",""
2,"gfdgfg","gfdgfg",2002-01-10 11:03:39.463000000,"","",""
3,"gfdgfg","gfdgfg",2002-01-10 11:03:41.243000000,"","",""
4,"gfdgfdgfd","gfdgfdgfd",2002-01-10 11:42:57.743000000,"timtinen1","",""
Only thing we can get from sql text export is this:
"id","vastausa","vastausb","pvm","tunnus","vastausc","vastausd"
1,"gfdgfg","gfdgfg",,"","",""
2,"gfdgfg","gfdgfg",2002-01-10 11:03:39.463000000,"","",""
3,"gfdgfg","gfdgfg",2002-01-10 11:03:41.243000000,"","",""
4,"gfdgfdgfd","gfdgfdgfd",2002-01-10 11:42:57.743000000,"timtinen1","",""
As you see my problem is those double quetes on colum names at the first row of the file... Cutomer needs double quotes on data part of file, but not on colum field.
How do I export test file so that colum names are without double quotes, but datapart has double quotes as needed?
We use MSsqlserver 2000. By solving this we can make this task fully automated...
March 7, 2008 at 5:00 am
Not quite sure, but some programs call needed export format as Merge file. How do I export data as merge file from SQLserver?
March 10, 2008 at 1:37 am
Anyone has any helpo for me?
Do I have to do some sort of activex script to do this task or is there any way to do this easily...?
March 10, 2008 at 4:41 am
What are you using to do the export with? DTS?
--Jeff Moden
March 10, 2008 at 4:49 am
We are using normal "text file destination" to export data to text file.
March 10, 2008 at 4:51 am
...and with DTS paggage.. ..vith simple SMTP mail task with it...
March 10, 2008 at 5:36 am
Sorry... I know squat about DTS... not sure if activex will help here or not.
--Jeff Moden
March 10, 2008 at 6:01 am
hi ...
I have understand u r problem...
while exporting table data to text file ,
we get the Text Qualifier drop down list with options like double quote,single quote ,none in the wizard control.
if u select the 'none' option, then u get what u want exactly.
Thank you,
Ajay Varma
March 10, 2008 at 6:09 am
Ajay Varma (3/10/2008)
hi ...I have understand u r problem...
while exporting table data to text file ,
we get the Text Qualifier drop down list with options like double quote,single quote ,none in the wizard control.
if u select the 'none' option, then u get what u want exactly.
Thank you,
Ajay Varma
No... that will remove the double quotes from everything... OP wants to remove the double quotes only from the header of the file.
--Jeff Moden
March 10, 2008 at 6:14 am
Yeah... I only wan't to remove double quetes from header.
I tryed to look, search with google if there is some sort of DTS export task plugin or some, but was not abble to find any.... It would be the easyest for me. Not so familiar with activex scripts. 🙁
March 10, 2008 at 6:44 am
hi again,
k it will remove double quotes from everything...
but i don't know for only columns... sorry
can anyone hav the solution for this....
Thank you,
ajay varma.
March 10, 2008 at 6:50 am
Could be done in plain ol' T-SQL... but would require either a trip through xp_CmdShell or would need to be called externally in a batch file.
--Jeff Moden
March 10, 2008 at 10:55 am
Ok. I found litle vbscript (activex) script to the task. First I do simple DTS export then execute my activex and then mail the file to cutomer.
This is the activex (vbscript):
Function Main()
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("e:\test.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close
strFirstLine = "This is the new first line in the text file."
strNewContents = strFirstLine & vbCrLf & strContents
Set objFile = objFSO.OpenTextFile("e:\test.txt", ForWriting)
objFile.WriteLine strNewContents
objFile.Close
Main = DTSTaskExecResult_Success
end function
http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun05/hey0630.mspx
I just manually do the first line of file... 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply