Skip to Content

CPI Groovy convert .csv to fixed width text file

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Jul 17, 2018 at 10:42 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 19, 2018 at 07:31 AM

    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
        }
    
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 28 at 07:36 AM

    Hi T. van Rooijen and Morten Wittrock ,

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 01 at 04:34 AM

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


    Cheers,
    Alex

    Add comment
    10|10000 characters needed characters exceeded