none
Adding custom header to BCP Export

    Question

  • Hi all,

    I have recently created a fixed length file export by utilizing XP_CMDSHELL. We do not have SSIS as option here.

    The file is NOT supposed to contain the column headers, however the requirement is to include a header row which can take either string value "Budget" or "Prognos" depending on a parameter, "@budgettyp". This parameter is passed on to a procedure by a user interface. Basically a user makes a set of choices and one of the parameters, @budgettyp, will then take either value "Budget" or "Prognos".

    What I have done so far is to make a UNION ALL in the SELECT to create a header row. (Scroll down to the bottom part and check the bold part of query). I removed the FROM part and other parts as it is not necassary here. I have used the column "KTO" as placeholder for @budgettyp.

    Below kind of works. At least the XP_CMDSHELL creates the file. However it is not optimal since I am limited to the characters defined on the first column (KTO) which is defined as char(6). Since "Prognos" is seven characters long it will not work unless I truncate this string, which I do not want to do.  

    Can I somehow feed in a parameter (in this case @budgettyp") into below BCP without having to make the union in below SELECT? I believe there must be a nice way of just feeding in a top row with either static values, functions like getdate() or parameter values etc?

    DECLARE @filename VARCHAR(500)
    DECLARE @sql VARCHAR(8000)

    SET @filename = (SELECT 'Temp\test_' + CONVERT(VARCHAR,GETDATE(),112) +'.txt')
    SET @sql = 'bcp "[testexport]" out C:\'+ @filename + ' -char -T -t'

    EXEC master..xp_cmdshell @sql

    ----

    SELECT -- (I want this bold marked part removed as it is only here to create a top row in the file export.)
     CASE WHEN @budgettyp = 30510 THEN 'Budget' ELSE 'Prognos' END AS [Kto] 
     , null AS [Ans]
     , null AS [Motp]
     , null AS [V]
     , null AS [Upt]
    UNION ALL
    SELECT
       CAST(' '+n3.alias as char(6)) AS [Kto] -- Konto
     , CAST(n.alias AS CHAR(4))  AS [Ans]  -- Ansvar
     , CAST(n4.alias AS CHAR(6)) AS [Motp] -- Motpart
     , CAST(n2.alias AS CHAR(2)) AS [V]   -- Verksamhet
     , CAST(n5.alias AS CHAR(5)) AS [Upt]  -- Uppdragstyp
    Periodiseringsnyckel

    Regards
    Fredrik

    Monday, September 12, 2016 11:18 AM

Answers

  • There are 2 ways to do what you are trying to do.

    You can change your query to return everything as one string.

    SELECT 
     CAST(CASE WHEN @budgettyp = 30510 THEN 'Budget' ELSE 'Prognos' END AS VARCHAR(1024)) AS [Line]  
    UNION ALL
    SELECT 
      CAST(' '+n3.alias as char(6)) + CHAR(9) + -- Konto
      CAST(n.alias AS CHAR(4)) + CHAR(9) +  -- Ansvar
      CAST(n4.alias AS CHAR(6)) + CHAR(9) + -- Motpart
      CAST(n2.alias AS CHAR(2)) + CHAR(9) +   -- Verksamhet 
      CAST(n5.alias AS CHAR(5)) + CHAR(9)  -- Uppdragstyp

    The other option is, since you are already using cmdshell, is to write the header to a new file, then append the 2 files together at the end of the process.

    SET @filename = (SELECT 'Temp\test_' + CONVERT(VARCHAR,GETDATE(),112) +'.txt')
    SET @sql = 'bcp "[testexport]" out C:\'+ @filename + 'Data -char -T -t'
    
    EXEC master..xp_cmdshell @sql
    
    SET @sql = 'bcp "header" out  C:\'+ @filename + 'Header -char -T -t'
    EXEC master..xp_cmdshell @sql
    
    SET @sql = 'copy C:\'+ @filename + 'Header + C:\'+ @filename + 'Data C:\'+ @filename 
    EXEC master..xp_cmdshell @sql
    

    Monday, September 12, 2016 12:54 PM
    Moderator
  • 8OK, so this goes to show that I have still things to learn about BCP!

    -t without an argument gives you a fixed-length file it seems. (I was thinking that you would get the default tab separator.) This means that there is a very simple way out:

    SELECT     CASE WHEN @budgettyp = 30510 THEN 'Budget' ELSE 'Progno'END AS [Kto] 
        CASE WHEN @budgettyp = 30510 THEN '' ELSE 's' END AS [Ans] ,
      , null AS [Motp]
      , null AS [V]
      , null AS [Upt]
    UNION ALL
    SELECT/    CAST(' '+n3.alias as char(6)) AS [Kto] -- Konto
     , CAST(n.alias AS CHAR(4))  AS [Ans]  -- Ansvar
     , CAST(n4.alias AS CHAR(6)) AS [Motp] -- Motpart
     , CAST(n2.alias AS CHAR(2)) AS [V]   -- Verksamhet/
     , CAST(n5.alias AS CHAR(5)) AS [Upt]  -- Uppdragstyp


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 13, 2016 10:11 PM

All replies

  • I'm afraid that you may have taken BCP to the limits and at this point it may be better to consider a separate program in C#, PowerShell or whatever you fancy if you don't want to use SSIS.

    If you want to keep this with BCP, you will have to stick to kludges from now. For instance you could write the header to one file, run the BCP to one file, and then use COPY to concatenate the files.

    But you cannot tell BCP to have a six-byte field and then in one record in the file have a seven-byte value. BCP has no understanding of headers.

    Monday, September 12, 2016 12:44 PM
  • There are 2 ways to do what you are trying to do.

    You can change your query to return everything as one string.

    SELECT 
     CAST(CASE WHEN @budgettyp = 30510 THEN 'Budget' ELSE 'Prognos' END AS VARCHAR(1024)) AS [Line]  
    UNION ALL
    SELECT 
      CAST(' '+n3.alias as char(6)) + CHAR(9) + -- Konto
      CAST(n.alias AS CHAR(4)) + CHAR(9) +  -- Ansvar
      CAST(n4.alias AS CHAR(6)) + CHAR(9) + -- Motpart
      CAST(n2.alias AS CHAR(2)) + CHAR(9) +   -- Verksamhet 
      CAST(n5.alias AS CHAR(5)) + CHAR(9)  -- Uppdragstyp

    The other option is, since you are already using cmdshell, is to write the header to a new file, then append the 2 files together at the end of the process.

    SET @filename = (SELECT 'Temp\test_' + CONVERT(VARCHAR,GETDATE(),112) +'.txt')
    SET @sql = 'bcp "[testexport]" out C:\'+ @filename + 'Data -char -T -t'
    
    EXEC master..xp_cmdshell @sql
    
    SET @sql = 'bcp "header" out  C:\'+ @filename + 'Header -char -T -t'
    EXEC master..xp_cmdshell @sql
    
    SET @sql = 'copy C:\'+ @filename + 'Header + C:\'+ @filename + 'Data C:\'+ @filename 
    EXEC master..xp_cmdshell @sql
    

    Monday, September 12, 2016 12:54 PM
    Moderator
  • Thanks Erland! I suspected that was the case regarding the bytes.

    I have nothing against SSIS. In this case I simply cant use it.

    Tuesday, September 13, 2016 9:41 AM
  • Thanks Tom!

    Option 2 works fine. I havnt tried the first option yet. I suspect that will make my data end up in correct in the file. They client need each datapoint (e.g. "Konto".. etc) to end up in fixed positions within the file.

    For instance Konto must make up position 3 to 6, and Ansvar position 8-10 and so on.




    Tuesday, September 13, 2016 2:04 PM
  • 8OK, so this goes to show that I have still things to learn about BCP!

    -t without an argument gives you a fixed-length file it seems. (I was thinking that you would get the default tab separator.) This means that there is a very simple way out:

    SELECT     CASE WHEN @budgettyp = 30510 THEN 'Budget' ELSE 'Progno'END AS [Kto] 
        CASE WHEN @budgettyp = 30510 THEN '' ELSE 's' END AS [Ans] ,
      , null AS [Motp]
      , null AS [V]
      , null AS [Upt]
    UNION ALL
    SELECT/    CAST(' '+n3.alias as char(6)) AS [Kto] -- Konto
     , CAST(n.alias AS CHAR(4))  AS [Ans]  -- Ansvar
     , CAST(n4.alias AS CHAR(6)) AS [Motp] -- Motpart
     , CAST(n2.alias AS CHAR(2)) AS [V]   -- Verksamhet/
     , CAST(n5.alias AS CHAR(5)) AS [Upt]  -- Uppdragstyp


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 13, 2016 10:11 PM