Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

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

Avatar

Level 2

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.

1 Accepted Solution

Avatar

Correct answer by
Level 4

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!

View solution in original post

2 Replies

Avatar

Correct answer by
Level 4

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!

Avatar

Level 2
Thanks @Krishnanunni, Using Advanced expression i have tried and its a good approach, and JS code am gonna try.