RFC_READ_TABLE in Excel trimming whitespace from field

[Former Member]

Hello

I am using RFC_READ_TABLE object in Excel (VBA) to extract SAP data from SNAP table. Everything is working but sometimes when there are white spaces at the end of a field in the database, it is trimmed off, is there a way around this?

Set oRFC = oR31.Add("RFC_READ_TABLE")

oRFC.exports("QUERY_TABLE") = "SNAP"

oRFC.exports("DELIMITER") = vbTab

Set orow = oRFC.tables("FIELDS")

orow.appendrow

orow(1, "FIELDNAME") = "FLIST"

orow.appendrow

orow(2, "FIELDNAME") = "FLIST02"

Set orow = oRFC.tables("OPTIONS").Rows.Add

orow.Value(1) = sOptions

Please help.

[Former Member · Accepted solution]

I have taken auto-formatting off in Excel and it is still trimming the whitespace off the end of fields and I am required to have thos e whitespaces to process the shortdumps from the SNAP table properly. Are there any known causes for this?

Also, how do I go about further checking the data coming into Excel?

Ken

[Former Member · Accepted solution]

I'm afraid I can't try what you're doing exactly, as I don't have access to a Windows machine, but have you tried running the RFC directly in SE37 with the same criteria you've listed above? When I try it in my system with table: SNAP, fields: FLIST, FLIST02, I clearly see whitespace in between fields.

Cheers,

David.

[Former Member · Accepted solution]

I have still not yet solved the problem but have progressed a little in terms of pinpointing the exact issue.

by using the RFC_READ_TABLE on the SNAP table with fields FLIST and FLIST02, any spaces on the end of FLIST will NOT be trimmed but any spaces on the end of FLIST02 WILL be trimmed. It seems it trims only the spaces from the last field read (if there is any spaces)

As RFC_READ_TABLE has a 512 byte limit, I am unable to read all 8 FLIST fields, in order to work around this I have divided the read to only get 2 fields at a time (flist and flist02, flist03 and flist04 ..etc) If I was able to read the whole line (8 fields) together, I don:t think I would run into this problem.

If you know of a workaround to properly read all 8 fields properly please let me know.

Regards

Ken

Edited by: Kensuke Nakagawa on May 16, 2011 6:19 AM

[Former Member · Accepted solution]

Hi!

A couple of suggestions from a happy amatuer that might work around the problem.

Maybe you can use the fact that only the second entry is affected by trimming, something like:

FLIST & FLIST02

FLIST02 & FLIST03

FLIST03 & FLIST 04

Each time replacing the previous with the new, like above you get FLIST & FLIST02 but then you call FLIST02 & FLIST03 and replace the previous FLIST02. This would increase it to 8 from 4 but it should work around the problem if i've understood it correctly (Guess you'll have to use a "dummy" call at the end and just discard it).

Second suggestion could be if someone knows a good RFC that doesn't return the string itself but instead a integer representation of how long the string is, then use both that RFC and RFC_READ_TABLE and compare the length. If it's not equal add the number of characters missing as whitespaces in the end.

[Former Member · Accepted solution]

Rather a quick and dirty workaround, and I really want to find a cleaner solution but it is working for now, thank you for your creativity! The performance has dropped as it has to do extra rounds at pulling data across sap/excel but the problem with whitespace trimming is gone!

thanks again!

[script_man]

Hi Kensuke,

It would be interesting to see how the data is read.

For example:

. . .
Set tblData = oRFC.Tables("DATA")
If oRFC.Call = True Then
    If tblData.RowCount > 0 Then
           sDATA = tblData(1, "WA")
          Range("B2").Select
          ActiveCell.FormulaR1C1 = rtrim(left(sDATA, InStr(1, sDATA, vbTab) - 1))
          . . .
    else
      msgbox "No Data for sOptions found ! ", vbinformation, "Note"
    end if
else
  MsgBox "ERROR CALLING SAP REMOTE FUNCTION CALL", vbinformation, "Note"
end if
. . .

Regards,

ScriptMan

[Former Member]

The data is being read with the whitespaces trimmed off scripman. As I am reading the SNAP table I need the whitespaces not trimmed so I can read the short dump errors from the table properly..

Are there any workarounds?

I could write my own zrfc_read_table in ABAP but that would mean I would have to transport it and maintain it on all of the SAP servers (over 30)

Is there an alternative way to read the SNAP table in Excel than RFC_READ_TABLE perhaps??

[Former Member]

I use RFC_READ_TABLE (from perl) and the whitespace does not get trimmed. Perhaps it's something in VBA or Excel that is trimming whitespace, and not the RFC_READ_TABLE function module that is doing it?

Cheers,

David.