Fixed-length format file generation in ACC from a worktable?

Avatar

Avatar
Level 1
Hari_07
Level 1

Likes

0 likes

Total Posts

5 posts

Correct reply

0 solutions
View profile

Avatar
Level 1
Hari_07
Level 1

Likes

0 likes

Total Posts

5 posts

Correct reply

0 solutions
View profile
Hari_07
Level 1

01-07-2021

Hi, 

Context: "Cost allocation and charge back mechanism" on using no of events or no of Lan id's.

Req: Generate a fixed-file length format file and transfer via SFTP.

Fixed-length file: Fixed-length format files use ordinal positions, which are offsets to identify the location of fields in the record. 

 

Following is a sample data record:

20011228YF2001122814313425 Forest St Marlborough MA017525083828200600

 

The record is defined by the following column layout:

  • Columns 1 - 8: Date format YYYYMMDD. Null when value is '99991231'.
  • Column 9: Boolean Y/N. Null when value is space (' ').
  • Column 10: Boolean T/F. Null when value is space (' ').
  • Columns 11 - 24: Time stamp format YYYYMMDDHHMMSS. Null when value is '99991231000000'.
  • Columns 25 - 39: Character address. Null when value is all spaces.
  • Columns 40 - 52: Character city. Null when value is '****NULL*****'.
  • Columns 53 - 54: Character state. Null when value is '##'.
  • Columns 55 - 59: Number postal code. Null when value is all zeros.
  • Columns 60 - 68: Character phone. Null when value is all zeros.
  • Columns 69 - 72: Number(3,2). For example, 600 is 6.00. Never null.
  • Column 73: Newline end of record.

I have tried using data extraction activity, but not helpful and struck there, requesting all the experts to provide a solution on this if it can be done in Adobe campaign classic.

Accepted Solutions (0)

Answers (1)

Answers (1)

Avatar

Avatar
Affirm 3
Level 2
Krishnanunni
Level 2

Likes

5 likes

Total Posts

21 posts

Correct reply

2 solutions
Top badges earned
Affirm 3
Boost 5
Contributor
Shape 1
Boost 3
View profile

Avatar
Affirm 3
Level 2
Krishnanunni
Level 2

Likes

5 likes

Total Posts

21 posts

Correct reply

2 solutions
Top badges earned
Affirm 3
Boost 5
Contributor
Shape 1
Boost 3
View profile
Krishnanunni
Level 2

05-07-2021

Hi @Hari_07 ,

If all you need to do is combine several columns to one, then I suppose you can do that in an enrichment or inside data extraction itself. The advanced expression editor provides a list of functions that you can use to check the data. To append the results to a single column, you may convert all data to string (use ToString() function) and use '+' to concatenate.

Krishnanunni_0-1625486145571.png

 

 

If you feel the functions inside advanced expression editor is not enough or the combined column becoming too messy, you may use a JS code similar to below. This will fetch the contents from the inbound transition (you can specify the table and query condition in queryDef itself by entering the schema name manually and adding where clause in queryDef). You may specify the columns needed inside the select tag. Then you can iterate each row and join the content to a single string as you desire. So all your null checks can be done inside the loop itself. The final string can be added to an array or written to a file directly using the writeln() function of the File class.

 

 

var schemaName = vars.targetSchema.substr(vars.targetSchema.indexOf(":") + 1);
// This is to fetch content from temp schema to JS
var query = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select">
<select>
<node expr="[target/@phone]"/>
<node expr="[target/location/@city]"/>
</select>
</queryDef>
);

var resultSet = query.ExecuteQuery();
var file = new File("filepath");
file.open("w", File.CODEPAGE_UTF8);
for each (var row in resultSet.*) {
// all conditions goes here
logInfo(row.target.location.@city)
// write to file using file.writeln(Combined string)
}

 

 

Hope it helps!