July 15, 2002 at 6:42 pm
I have some comma separated text data qualified with vertical bar (char 124) instead of usual quotes or single quotes. How can use DTS to import this data into SQL server since wizard doesn't offer that as a qualifier?
The reason I had to get data with vertical bar is some of the character fields have embedded single and double quotes.
I have SQL 7 sp2. Some of the fields in the text file is numeric and integer, so doesn't have vertical bars around.
Thanks a lot!
July 15, 2002 at 6:48 pm
Not sure if the delimiters are intrinsic or they just built a UI for the common ones. Quickest way to tell is to save it out as VB code, then look for where it gets used. If you can find it you can change it! If that fails, you could parse it out yourself in DTS (maybe do a replace operation) or pre-process it even.
Andy
July 15, 2002 at 7:07 pm
Andy, how can I save DTS as VB file in SQL 7?
Could you also spell out "replace operation" for me?
Thanks
July 15, 2002 at 8:45 pm
To save a DTS package open the package in EM DTS Designer, then choose files Save As and you will have an option to save to VB file.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 16, 2002 at 1:29 am
I havn't got a SQL server 7 installation to test but the qualifier is configurable in SQL2K:-
Add a dynamic properties task.
Open it's properties and click Add.
In the text file connection drill down to >OLE DB Properties>Text Qualifier.
Double click on Value, specifiy the source as a constant and put | in the text field.
Regards,
Andy Jones
.
July 16, 2002 at 4:18 am
Hi,
you could also try bcp with the -t parameter you could specify the field terminator.
regards,
Klaas-Jan
July 16, 2002 at 4:37 am
-t is the field terminator not the text qualifier.
Regards,
Andy Jones
.
July 16, 2002 at 6:16 am
You can't save it directly to VB code in SQL7, there is a tool that comes on the CD that will do it, name escapes me at the moment.
Andy
July 16, 2002 at 10:33 am
Andyj93: SQL 7 doesn't have dynamic properties task.
Andy W: I was able to find vb code to script DTS. I found the delimiter oConnection.ConnectionProperties("Text Qualifier") = """". So now I can change it to "||", but what do I do now? How can I crete DTS from VB code?
July 16, 2002 at 10:49 am
Been a while since I worked on SQL7. I think it works about the same as 2000. Basically once you get everything into the object, you can call .save or .execute methods of the package. You need to load the code into VB, set a reference to the DTS library, look for the .execute, change it to .save. I think there are a couple methods, you're looking for the one that will persist in SQL itself.
Andy
July 16, 2002 at 12:36 pm
Thanks Andy.
It's goPackage.SaveToSQLServer "(local)", "sa", ""
'goPackage.Execute
'to execute a package instead of saving it, comment out the save line above and uncomment the execute line
July 22, 2002 at 7:55 am
use dts - use the wizard - under the tab that says " specify column delimiter" choose the last (far right radio button) and enter a vertical bar into the text box.
You can then do this on export and choose any character you wish -- I frequently use a dollar symbol for address data - the great thing about this is that any character or sequence can be used on import or export.
July 22, 2002 at 10:37 am
How could I miss that? Since it wasn't listed, I just simply trusted the interface. I wasted so much time on VB, but learned a couple of things.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply