Post reply

4 Excel Columns into one

  • September 9, 2003 at 10:02 am

    #176705

    Using DTS I am trying to bring 4 columns from Excel: Address1, Address2, Address3, Address4

    Into a single field Address.

    I would like each column on a new line when I view the field in Access.

    I tried an SQL query like following in DTS:

    SELECT Address1 + CHAR(10) + Address2 + CHAR(10) + Address3 + CHAR(10) + Address4 AS Address FROM ['Converted Data$']

    But get the message 'Undefined function 'CHAR' in expression.

  • September 9, 2003 at 10:08 am

    #473256

    I think that you want to setup the transform as anm ActiveX task and then use the CHR() function in the VB Script.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • September 9, 2003 at 11:13 am

    #473271

    Thanks, do you have a simple example to get me started?

  • September 9, 2003 at 2:15 pm

    #473306

    Start with a simple DTS from your XLS file to Table.

    In the DTS select the 4 columns from the source file and the one col in the destination table, create

    Create an active x script in the Transformation Tab. The script should go something like this:

    DTSDestination("TargetAddress")=DTSSource("Col001") &vbcrlf& DTSSource("Col002") &vbcrlf& DTSSource("Col003") &vbcrlf& DTSSource("Col004")

    Hope it helps

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply