CPI Groovy convert .csv to fixed width text file

[tomvanrooijen]

Hi,

I need a solution direction for converting a .csv file to fixed width file for an SFTP receiver.

I've looked around in groovy for this but I still have the problem that I am not fluent with this language. There are lots of options.

I guess I could read the .csv as a multiline and then substring & parse each line possibly with the use of padding.

Since the .csv is a conversion from a flat xml I could possibly also use xmlslurper to get all the different fields and then parse them.

Or maybe use a java library for this?

What would be a good strategy for getting this fixed width flatfile out of my CPI?

Thanks a lot!

Tom

[MortenWittrock · Accepted solution]

Hi Tom

You do indeed have quite a few options. If your CSV is simple, you can create the fixed width output directly in Groovy. In the following example, I expect the message body to contain a number of lines, each of which contains three fields separated by commas. I turn that into three columns of width 3, 5 and 2, respectively. Here's the code:

import com.sap.gateway.ip.core.customdev.util.Message

def Message processData(Message message) {
    def columnWidths = [0: 3, 1: 5, 2: 2]
    def sb = new StringBuilder()
    def body = message.getBody(java.lang.String)

    body.splitEachLine(',') { fields -> 
        fields.eachWithIndex() { field, index ->
            sb.append(field.padLeft(columnWidths[index]))
        }
        sb.append("\n") // <-- You may need a different line separator
    }

    message.setBody(sb.toString())
    return message
}

The columnWidths map contains the widths of the columns. The values are padded with spaces from the left.

This code won't truncate a value that is too big to fit in its column, so you need to either make sure that all values fit, or implement some sort of truncation.

Let me know how it works out.

Regards,

Morten

[AlexSampayo2]

Hi 7a519509aed84a2c9e6f627841825b5a
Here is my post:
https://answers.sap.com/questions/774929/groovy-flat-file-creation.html
Thanks for your help mate!

Cheers,
Alex

[AlexSampayo2]

Hi t.vanrooijen and 7a519509aed84a2c9e6f627841825b5a ,

I have a similar thing to do, but i need to separate each field with a comma. can you help me?
I applied your code and i get something like this:

         "31008994"KioskAccess             No
          "31010868"KioskAccess             No
          "31010877"KioskAccess             No
          "31007038"KioskAccess             No
          "31009159"KioskAccess             No
          "31009182"KioskAccess             No
.

i need to delete the final dot "." and add commas as well.

The result should be this:

"31008994",KioskAccess,No
"31010868",KioskAccess,No
"31010877",KioskAccess,No
"31007038",KioskAccess,No
"31009159",KioskAccess,No
"31009182",KioskAccess,No

I have a lot of code to gather and order the information, but my problem is at the end when i get everything and try to get it out of the groovy script.

I had a more complicated code using .ADD and the result was not so pretty.

Thanks for your help,

Cheers,
Alex

[MortenWittrock]

Hi Alex

Please post new questions separately, and remember to provide context and ideally some sample input and output.

Regards,

Morten

[tomvanrooijen]

Hi Morten,

Thank you very much, this works like a charm!

Very short code yet very readable.
The truncating I may need for the header fields if I need to supply them. I used the take method for this.

Again thanks very much.

Regards

Tom

body.splitEachLine(';') { fields -> 
        fields.eachWithIndex() { field, index ->
            sb.append(field.take(columnWidths[index]).padLeft(columnWidths[index]))
        }
        sb.append("\n") // <-- You may need a different line separator
    }

[MortenWittrock]

Hi Tom

Glad to hear it. Since my answer seems to have solved your problem, I would appreciate it, if you would accept the answer.

Regards,

Morten

[MortenWittrock]

Hello again Tom

Are you aware that you accepted your own answer, not mine? 🙂

Regards,

Morten

[tomvanrooijen]

Indeed...Sorry for that, I fixed it.

Regards,

Tom