Use BCP and a format file to export data to a text file

  • I would like to use BCP and a format file to export data to a text file in a fixed length format.

    Fields export. The length and starting and ending position.

    Last Name Length 20 Position 1-20

    First Name Length 15 Position 21-36

    Social Security Number Length 9 Position 37 46

    AddressLine1 Length 30 Position 47- 77

    AddressLine2 Length 30 Position 78-108

    Does anyone have an example of how I should setup my file format?

    Here is an example of my format file.

    1 SQLCHAR 0 1 20 ""    1 LastName SQL_Latin1_General_CP1_CI_AS

    2 SQLCHAR 0 21 36 "" 2 FirstName SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 37 46 "" 3 SSN SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 0 47 77 "" 4 EmployeeAddr1 SQL_Latin1_General_CP1_CI_AS

    5 SQLCHAR 0 78 108 "" 5 EmployeeAddr2 SQL_Latin1_General_CP1_CI_AS

     

     

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII.  What company did you say you work for?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII.  What company did you say you work for?

    Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would you do that?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Jeff Moden wrote:

    Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII.  What company did you say you work for?

    Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would you do that?

    I would tell the receiving party to go to hell.  Either it goes properly encrypted with "double salt" or it doesn't go.  Period.

    I would also report the party that won't be receiving the data even if they said they'd accept encrypted data because the reason they didn't want to accept it in the first place is likely going to be that they a servers full of such unencrypted data and they need  to be audited. 😉

    • This reply was modified 8 months ago by  Jeff Moden.
    • This reply was modified 8 months ago by  Jeff Moden.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams wrote:

    Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would you do that?

    For PII/PCI/PHI, At my earlier company, they used to deal with memberships around the world, when the company receives such requests, they have a policy in place that requires them to seek approval from higher-level management to send data un-encrypted. In some instances, the legal department may also be consulted, particularly when the request is from certain countries for certain data fields.

    =======================================================================

  • Jeff Moden wrote:

    Jeffrey Williams wrote:

    Jeff Moden wrote:

    Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII.  What company did you say you work for?

    Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would you do that?

    I would tell the receiving party to go to hell.  Either it goes properly encrypted with "double salt" or it doesn't go.  Period.

    I would also report the party that won't be receiving the data even if they said they'd accept encrypted data because the reason they didn't want to accept it in the first place is likely going to be that they a servers full of such unencrypted data and they need  to be audited. 😉

    You are making a lot of assumptions here that are incorrect.  There are many reasons for transferring data between organizations - where that data will contain PII/PCI/PHI data.  You don't have the choice of not sending the data - the only choice you have is making sure it is as secure as possible.

    The appropriate methodology would be to encrypt the file - not the individual fields in the file and/or use a secure method to transfer that data between organizations.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    You are making a lot of assumptions here that are incorrect.

    Nope.  Not making any assumptions here.  I know and stated exactly what I'd do.  People that do otherwise are part of the problem with data breaches and more.  The DILIGAF and "Not my job/concern" and "My boss said it's ok" attitude about data security has got to stop.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Emperor100 wrote:

    Jeffrey Williams wrote:

    Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would you do that?

    For PII/PCI/PHI, At my earlier company, they used to deal with memberships around the world, when the company receives such requests, they have a policy in place that requires them to seek approval from higher-level management to send data un-encrypted. In some instances, the legal department may also be consulted, particularly when the request is from certain countries for certain data fields.

    Things like this are a part of the problem because the bottom line is that unencrypted is sent to "memberships around the world".  Being a "member" should also require proper encryption of data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Besides the obvious SSN faux pas you have another problem. Do you realize that almost all of your length calculations are off by at least 1 character. 21 to 36 is 16 xharacters, not 15.  Most below that have the same problem.

    Just the ramblings of an old man.

Viewing 10 posts - 1 through 9 (of 9 total)

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