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

Question

Post reply

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

Randy Powell-473969

Old Hand

Points: 343
More actions
June 26, 2023 at 11:35 pm

#4220198

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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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 1 year, 4 months ago by Jeff Moden.
This reply was modified 1 year, 4 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)

Emperor100 SSCrazy Points: 2173 More actions · Answer 5

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.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

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)

wbailey Grasshopper Points: 13 More actions · Answer 9

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.