Trailer Record

[Dom]

I need to create a trailer record that needs to be added to the end of the file and all data needs to be in one single line. I will need to hardcode a few fields and include the total record count. I need help

[MarqueeCrew · Accepted solution]

Hi Dom!

Help is here.  A quick approach would look like:

1.  Write your output as normal (I assume a CSV file).

2.  If you need to comute trailer data, write that to another .txt file (FLAT).

3. You can read both of these files with "csv", but change the delimiter to \0 (NULL).

4. You can union the data together (keeping the order of the records so that file #2 is the trailer).

5. Output the data together to a FLAT file.

This will allow you to append a trailer (or a header too if needed).  If you would like to get some more guidance on the topic, please let me know and I can help you further.

Thanks,

Mark

[Dom]

Thank you, Mark! How do you add the current date to the file and record
count?

Example:

ZZZXXXXXXXXXXBIO2015121600005687

trailer_id

Field identifying to determine this is the trailer record.

character

1

3

3

*P*

Hardcode to "ZZZ"

vendor_code

Unique code that is assigned to a vendor

character

4

13

10

*P*

Hardcode to ??? (not sure yet on vendor code for this feed) This will need
to be provided W

vendor_type

Type of data a vendor is sending

character

14

16

3

*P*

Hardcode to "BIO"

batch_date

Date and time the file was created

numeric

17

24

8

*P*

In the format CCYYMMDD

total_record_cnt

The total number of records contained within the file, excluding the
trailer record

numeric

25

32

8

*P*

Right justified, padded with zeros

[MarqueeCrew · Accepted solution]

Dom,

you can concatenate string variables with a "+" sign.  You will need to summarize and count the number of records before you get here, but here is a code snippet of the more challenging features of your request:

DateTimeFormat(DateTimeNow(),"%Y%m%d") + right(PadLeft(ToString([Records]),10,"0"),10)

If we want to look at the current date, you can use the function:  DateTimeNow()

That gets you the date and the time.

The format desired is YYYYMMDD, so I use the DateTimeFormat() function and set the format to %Y%m%d to get to 20151221.

Then I use a function to get the record count with leading zeros and take the rightmost result of 10 digits.

I hope that this helps,

Mark