none
writing data into flat file in one single line

    Question

  • Hello,

    I am trying to write data into flat file in single line, see for example

    I/p

    Dan Lucas 1

    Tom Mudy 1

    output should be like this

    Dan Lucas 1 Tom Mudy 1

    we have a hole bunch of records so custom code not work here, thanks in advance.

    Thursday, November 07, 2013 2:30 PM

Answers

  • When you said you have i/p 

    Dan Lucas  1

    Tom Mudy  1

    Does it mean that they are coming from two different sources ? and you want to join them with second column 

    If that is the case then you can resolve it via Lookup to make a join between these two sources and get a unique row per join. After that, you can use derived column transformation to join those column and create a new column.

    Ex: NewDerivedColumn = Column1Table1 + " " + Column2Table1 + " " + Column1Table2 + " " + Column2Table2

    and then output this NewDerivedColumn to your o/p flat file.

    Hope this will help

    Saturday, November 09, 2013 8:13 PM

All replies

  • Hi,

    How would you separate one record from the other? Some special character?

    Or would you be using fixed length records?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, November 07, 2013 2:48 PM
  • Are you looking for the below query,

    DECLARE @TEMP TABLE([VALUE] NVARCHAR(30))
    INSERT INTO @TEMP VALUES('MAZ')
    INSERT INTO @TEMP VALUES('HON')
    INSERT INTO @TEMP VALUES('FOR')
    INSERT INTO @TEMP VALUES('JEEP')

    SELECT Stuff((SELECT  ' ' + [VALUE] FROM   @TEMP FOR xml path('')) , 1, 1, '') [Value]

    In order to export the above refer the below link.

    http://social.msdn.microsoft.com/forums/sqlserver/en-US/40b0334e-5c44-403d-9e9f-6cdf67a8f3ff/export-to-csv-file-using-tsql-from-ssms


    Regards, RSingh


    • Edited by RSingh() Saturday, November 09, 2013 9:58 AM
    Thursday, November 07, 2013 2:49 PM
  • What is your row delimiter?

    The only reason flat files appear to have 'lines' is because the row delimiter is typically a newline character.

    Thursday, November 07, 2013 2:52 PM
  • thanks for your reply, this is close but I am loosing special characters in this case
    Friday, November 08, 2013 5:12 PM
  • Can you explain more about "loosing special character" ?

    Regards, RSingh

    Saturday, November 09, 2013 10:00 AM
  • When you said you have i/p 

    Dan Lucas  1

    Tom Mudy  1

    Does it mean that they are coming from two different sources ? and you want to join them with second column 

    If that is the case then you can resolve it via Lookup to make a join between these two sources and get a unique row per join. After that, you can use derived column transformation to join those column and create a new column.

    Ex: NewDerivedColumn = Column1Table1 + " " + Column2Table1 + " " + Column1Table2 + " " + Column2Table2

    and then output this NewDerivedColumn to your o/p flat file.

    Hope this will help

    Saturday, November 09, 2013 8:13 PM