Post reply

How i can export data from sql server to text file with fixed length.

  • June 15, 2010 at 3:43 pm

    #157018

    Hi,Can anybody help me how i can transfer data from a sql sever view into a text file with fixed length,text file should be without any deliminator.

    Here is sql i used to transfer data into text file but manager said text file does not has data with fixed length and it is also having tabs.

    I never transfer data into text file with fixed length before.

    Please see the SQL which i used and cmdshell for data transfer:

    CREATE VIEW VIEW1 AS

    SELECT CAST(dbo.locate.num AS CHAR(40)) AS [Account Number], ' ' AS Filler1, ' ' AS Initial,

    CAST(dbo.address.owner1add1 AS CHAR(38)) AS Owner1add, CAST(dbo.address.owner1city AS CHAR(15)) AS City,

    CAST(dbo.address.owner1st AS CHAR(2)) AS State, CAST(dbo.address.owner1zip AS CHAR(9)) AS Zipcode, CAST(dbo.address.owner1ssn AS CHAR(9))

    AS [Social Security Number], ' ' AS F, ' ' AS P, ' ' AS SUBCODE, '0195231' AS [Company EmpID], '01' AS [Portfolio EmpID],

    ' ' AS Filler2,

    ' ' AS [Customer Text Data]

    FROM dbo.address RIGHT OUTER JOIN

    dbo.locate ON dbo.address.EmpID = dbo.locate.EmpID

    WHERE (CAST(dbo.locate.num AS CHAR(40)) IN ('0000270200676830', 'A08231999172730', '0000270200001120', '0000270200001138', 'A08241999171803',

    'A08241999171834', '0000270200001161', 'A08271999134818', 'A08271999140906', 'A08271999172509'))

    GO

    EXEC master..xp_cmdshell 'bcp "SELECT TOP 10 * FROM FIN.dbo.VIEW1" queryout c:\VIEW1.txt -c -T'

    Thanks

    Irfan

  • June 15, 2010 at 5:19 pm

    #1180740

    Try concatenating all the fields together into one.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

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