Text Qualifiers

• PARogers

  SSC Enthusiast

  Points: 134

  More actions

  January 29, 2003 at 8:26 am

  #357693

  I am creating a text file by exporting data via DTS from 2 tables using a UNION statement to produce a Header ("H") and a Detail ("N") record. The Header record has a value in all fields, while for the detail record, the last 2 fields are blank. The application that will import the file requires Text Qualifers i.e. "Any Text", but only if there is a value in the field. Does anyone know how to conditionally set text qualifiers or any other approach? A smaple of the data is pasted below.

  "H" 1 "004638" "RP144" 212.83 "ABE001" "SP1816"

  "N" 1 "9-9-999" "453000" 212.83

• philcart

  SSC-Forever

  Points: 47794

  More actions

  January 29, 2003 at 3:45 pm

  #447541

  You could probably try an ActiveXScript in the transformation to build the output row prior to writing it to the file.

  Something like,

  
  
  Function Main()
  
  
  
  Const sQt = """" ' single quote character
  
  Dim sLne ' output line
  
  
  
  ' using vbTab as seperator
  
  sLne = sQt & DTSSource("Col1") & sQt & vbTab
  
  sLne = sLne & DTSSource("Col2") & vbTab
  
  sLne = sLne & sQt & DTSSource("Col3") & sQt & vbTab
  
  sLne = sLne & sQt & DTSSource("Col4") & sQt & vbTab
  
  sLne = sLne & DTSSource("Col5") & vbTab
  
  
  
  
  
  If IsNull(DTSSource("Col6")) = False Then
  
  sLne = sLne & sQt & DTSSource("Col3") & sQt & vbTab
  
  sLne = sLne & sQt & DTSSource("Col4") & sQt
  
  End If
  
  
  
  DTSDestination("Col1") = sLne
  
  
  
  Main = DTSTransformStat_OK
  
  End Function
  
  

  If that doesn't work, I'd build the line in the SQL statement. eg:

  SELECT '"' + Field1 + etc...

  FROM Table1

  UNION

  SELECT '"' + Field1 + etc...

  FROM Table2

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface