Text Export Formatting

[cbayles]

I'm trying to create an report in CR 8.5 that exports invoices from our SQL database into a comma-delimited text file. The report consists of an invoice header group followed by the invoice detail line items. I managed to get the report looking good in crystal but when I export it to character separated file using , as the separator and nothing as the delimiter, the invoice line items end up on the same line as the header info.

Export should look like this:

H,COMPANY NAME,123456,PO#1234,12/15/03,64.50
L,100,EA,AA BATTERY,.32,32.00
L,50,EA,D BATTERY,.65,32.50

Unfortunately this is how it comes out:

H,COMPANY NAME,123456,PO#1234,12/15/03,64.50,L,100,EA,AA BATTERY,.32,32.00,L,50,EA,D BATTERY,.65,32.50

How can I fix this formatting so our customer can import this file without any problems?

 

[MattReed]

Do you know what application they are trying to import into?

What is the purpose of the import?

The only reason I ask is becuase there may be a better way to approach this problem other than a text file import.

 

[cbayles]

Im not sure what application they're importing it into. We were given a specification that we have to follow.(Government customer)
The purpose of the import is to prevent their AP department from having to process paper invoices.

 

[MattReed]

This will work...I hope!

Make two formulas...one called Header and another called footer for example.

Make the Header formula exactly like the header line you want in your export...

{Field1} & "," & {Field2} etc....

Do the same thing for your detail line.

Now, Group by the key for your invoice header table.
Insert the header formula into the Group Header section. Insert the detail into your detail section.

Export it as a text file (not CSV,) and it should be correct.

hth,
Matt

 

[cbayles]

Worked like a charm! Thanks for your help!

-Chris