Publish to pipe delimited flat file is possible with CA TDM? If yes please help me with the steps? Appreciate your help.

[PratheeshJoseph2017]

Publish to pipe delimited flat file(|) is possible with CA Data Maker (TDM tool)? If yes please help me with the steps? Appreciate your help.

[Les Dover]

Hi Pratheesh,

 

Did you look here? 

Mask Data in Fixed-Width or Delimited Files

https://docops.ca.com/ca-test-data-manager/4-2/en/provisioning-test-data/mask-production-data/mask-stored-data/mask-data-stored-in-flat-files

 

Mask Data in Fixed-Width or Delimited Files

Fast Data Masker lets you mask the data in fixed-width and delimited files. The delimited file type includes character-, comma-, and tab-separated files.

Understand Fixed-Width and Delimited File Formats

Fast Data Masker expects these file types to follow a specific format. If a file does not adhere to the required format, Fast Data Masker cannot process it for masking. 

Delimited File

An example of a delimited file that contains the sample data is as follows. This file is a character-separated file, which includes the pipe character (|) as a separator. This file includes three rows of data.

Data File (Delimited)

1|James|Lynn|39 2|John|Smith|50 3|Mary|Jane|30

This data file follows the structure that is defined in the associated definition file. An example of a definition file that is related to the delimited data file is as follows. The first row of the definition file gives general details about the file. The DELIM parameter in the definition file defines that the pipe character (|) is used as a separator in the data file. The file also defines the column names: ID, FIRSTNAME, LASTNAME, and AGE, which correspond to the data included in the data file. For example, the first row in the data file is related to the data definition file as ID=1, FIRSTNAME=James, LASTNAME=Lynn, AGE=39. Other rows also follow the same representation. The definition file does not define any header and trailer values. 

Definition File (Delimited)

HEADER=N,TRAILER=N,DELIM=|,DATEQUOTED=,CHARQUOTED=,NUMQUOTED= ID FIRSTNAME LASTNAME AGE

The following list includes information about the parameter names, description, and values used in the first row:

• HEADER
  Specifies the header record.
  Values:
  • Y, N, or a number
  • Y==1 and N==0
• TRAILER
  Specifies the trailer record.
  Values:
  • Y, N, or a number
  • Y==1 and N==0
• DELIM
  Specifies whether fields are delimited. If yes, specify the value.
  Values:
  • 'fixed', 'FIXED' – fixed width – no delimiters
  • 'comma', '<COMMA>' – comma delimited
  • 'tab', '<TAB>' – tab delimited
  • Any character (for example, pipe)
• DATEQUOTED
  Specifies whether dates are quoted.
  Values:
  • Y or N
• CHARQUOTED
  Specifies whether strings are quoted.
  Values:
  • Y or N
• NUMQUOTED
  Specifies whether numerics are quoted.
  Values:
  • Y or N

I have pointed you to FDM as that maybe your only option.

Cheers!
Les.

[PratheeshJoseph2017]

Thanks Les for the reply. This is helpful from the Masking front. But I am specifically looking GT Data Maker Solution for Flat File Generation (Publish to pipe delimited Flat File). I couldn't find any options in Data Maker to publish the result to a pipe delimited flat file.

[Anil Kumar Appukuttan]

Pratheesh,

 

For a pipe delimited file, you can create a definition file with the parameters mentioned below on line 1 followed by the column names.

 

HEADER=N,TRAILER=N,DELIM=|
ID
FIRSTNAME
LASTNAME
AGE

 

Then you can register this template in Datamaker through the option "Delimited File from G-T text file". Data can be imported to the data pool by right clicking the same and selecting the "Import External File Data". Finally select the "Publish to file (FD)" option to get the corresponding output.

 

I have attachéd the sample template you can use.

 

Thanks,

Anil

Attachment(s)

Sample_Template.txt.zip   200 B 1 version

[PratheeshJoseph2017]

Thanks Anil,

This is helpful. I am trying to publish from an Excel to Flat File and the out put FF should be pipe delimited. Is there a way you can think of for this ?

 

 

Thanks

Pratheesh

[Anil Kumar Appukuttan]

Pratheesh,

 

You can publish to a csv and then maybe have a poist publish action to replace the delimiter from comma to pipe.

 

If you always require pipe as the delimiter, then you can change the system settings though control panel to set the "List Separator" as pipe instead of comma which is the default. This will get reflected in all applications as well. eg: if you save a csv from excel the separator will be pipe once you make this change.

 

Thanks,

Anil

[PratheeshJoseph2017]

Thanks Anil. This really helps..

[Ruchi Singh]

Hi Anil,

Could you help me with the steps to add a post publish action to replace the delimiter from comma to pipe after publishing file to csv.

Thanks,
Ruchi

[Scott Schmitz]

Hi Ruchi,  as this is an old thread, please allow me to assist:
--
IMPORTANT NOTES:    

If HOST action is not enabled on your system, refer to this documentation.
If you have changed the default output location for the publish, then replace the default path with your updated path.
Change the hardcoded pipe script & output dir in my example to whatever meets your requirements.
Change the hardcoded PERSONS.CSV to the name of the output .csv file created by your generator

--

Navigate to your Generator in TDM Portal
Click the Actions button
Click the Create Publish Action button
Action name: CSV-TO-PIPE
Action type: HOST
Set as type: Post
command:
C:\temp\tdmcmty\ruchi-to-pipe\csv-to-pipe.bat ~PUBJOBID~

The .bat file will look like:

powershell -executionpolicy bypass -File .\csv-to-pipe.ps1 %1

contents of Powershell script:

$jobid=$args[0]
echo "jobid : $jobid"
$fullpath="C:\ProgramData\CA\CA Test Data Manager Portal\Jobs\Job_" + $jobid + "\PERSONS.CSV"
echo "fullpath : $fullpath"

$reader = New-Object IO.StreamReader $fullpath
$writer = New-Object IO.StreamWriter 'C:\temp\tdmcmty\ruchi-to-pipe\pipe.txt'

while ($reader.Peek() -ge 0) {
$line = $reader.ReadLine().Replace(',', '|')
$writer.WriteLine($line)
}

$reader.Close(); $reader.Dispose()
$writer.Close(); $writer.Dispose()

[Scott Schmitz]

Thought I posted earlier but can't see it in the thread(?)

Please see below for instructions
- If HOST action is not configured, please reference the documentation here.
- If the default location for publishes has been changed in your implementation, update the Jobs path.
- Replace PERSONS.CSV with the appropriate .csv created by your Generator
- Update the location of the .bat and .ps1 files per your directory structure on the TDM Portal windows machine

---

Navigate to your Generator in TDM Portal
Click the Actions button
Click the Create Publish Action button
Action name: CSV-TO-PIPE
Action type: HOST
Set as type: Post
command:
C:\temp\tdmcmty\ruchi-to-pipe\csv-to-pipe.bat ~PUBJOBID~

The .bat file will look like:

powershell -executionpolicy bypass -File .\csv-to-pipe.ps1 %1

contents of Powershell script:

$jobid=$args[0]
echo "jobid : $jobid"
$fullpath="C:\ProgramData\CA\CA Test Data Manager Portal\Jobs\Job_" + $jobid + "\PERSONS.CSV"
echo "fullpath : $fullpath"

$reader = New-Object IO.StreamReader $fullpath
$writer = New-Object IO.StreamWriter 'C:\temp\tdmcmty\ruchi-to-pipe\pipe.txt'

while ($reader.Peek() -ge 0) {
$line = $reader.ReadLine().Replace(',', '|')
$writer.WriteLine($line)
}

$reader.Close(); $reader.Dispose()
$writer.Close(); $writer.Dispose()

[Ruchi Singh]

Hi Scott,

 

Thanks for your reply.

 

Sorry couldn't reply to you earlier as I was on family emergency medical leave.

 

I will work with this solution and update if it works fine for me.

 

Thanks and Regards,

Ruchi

 

 

PUBLIC

[Ruchi Singh]

Hi Scott, Team,

 

I tried the solution given below but it didn't work, need to know a few more things in detail.

 

Can we connect on support call for discussion.

 

Regards,

Ruchi

 

 

PUBLIC

[Legacy User]

Prathessh,

 

The same concept that allows the masking should allow generation of pipe delimited files. When you register an object, one of the options is to register it as a GTExcel file. This is a file that defines the filed layout of the flat file. It allows for a header and footer and multiple record types. I couldn't find the details of this in the documentation so I will try to locate my template file and upload it here with a more detailed explanation. Maybe someone else in the community has a template that they could share. I know this is possible. The one thing I am not sure about is whether or not this can be accomplished with variable width fields. I'll look into that and get back to you.

 

Thanks,

Brian

[PratheeshJoseph2017]

Thanks Brian.