Pass data to excel file using dataset

Former Member · ‎11-20-2007

Hi,

I want to save the data in background excel file using datasets.

Can u give me an example how i can do this.

Thanks & Regards

Santhosh

Former Member · ‎11-20-2007

hI

Check the below example program :

parameters: d1 type localfile default

'/usr/sap/TST/SYS/Test.txt'.

data: begin of itab occurs 0,

field1(20) type c,

field2(20) type c,

field3(20) type c,

end of itab.

data: str type string.

constants: con_tab type x value '09'.

if you have a newer version, then you can use this

instead.

*constants:

con_tab type c value

cl_abap_char_utilities=>HORIZONTAL_TAB.

start-of-selection.

itab-field1 = 'ABC'.

itab-field2 = 'DEF'.

itab-field3 = 'GHI'.

append itab.

itab-field1 = '123'.

itab-field2 = '456'.

itab-field3 = '789'.

append itab.

open dataset d1 for output in text mode.

loop at itab.

translate itab using ' # '.

concatenate itab-field1 itab-field2 itab-field2

into str

separated by con_tab.

translate str using ' # '.

transfer str to d1.

endloop.

close dataset d1.

ASSIGN IT IN BACKGROUND

REWARD IF USEFULL

former_member468732 · ‎08-10-2010

hi thanks

its working

after data download if we save/send file in excel format then data in separated by column .

Former Member · ‎11-20-2007

<b>If report is running into background than you can only save your file to Application server. So, to download file on application server you need to use the OPEN DATASET, TRANSFER, CLOSE DATASET syntax.

Once file is createed to server, you can make one small program which can read the file and stores it to the presentation server.</b>

Former Member · ‎11-20-2007

1. I don't think there is any directy way,

to rad .XLS file

into internal table

on the application server.

2. The reason is :

a) .XLS is a Microsoft technology

b) the application server OS may not be microsoft os,

it may be UNIX, AIX etc.

c) Converter tehnology is not there,

and possibly SAP does not support it.

3. FILE_READ_AND_CONVERT_SAP_DATA

This FM,

can read files from applicatin server

in TXT and other formats.

4. BUT, in case of .XLS file,

it EXPECTS the file to be on the presentation server only.

(bcos Microsoft OS be there on presentation server)

5. IN SAP,

people don't work directly with .xls file

while uploading data.

We need to work with TEXT Files

which are TAB DELIMITED or CSV files etc.

Former Member · ‎11-20-2007

<b>Download in Background in Excel Format</b>

Download from background is possible, if you could setup the environment

1. create a custom table first

Table : Y001

Displayed fields: 4 of 4 Fixed columns:

MANDT BNAME Y_SITE Y_PATH

010 <userid> <site> cd <novell_path>

2. rewrite ws_download to z_download (light modification required, see attachment)

3. ask your basis team to make a copy of command FTP and CHMOD to ZFTP and ZCHMOD resp., make the setting according to your environment.

1 *----

2 * Changed By :

3 * Changed On :

4 * Changed : NOVELL directory path based on SAP login id.

5 *----

6 TABLES: Y001.

7

8 CONSTANTS: C_PATH(14) VALUE '/home/ftpuser/'.

9

10 DATA: BEGIN OF C_TAB,

11 X(1) TYPE X VALUE '09',

12 END OF C_TAB.

13

14 DATA: BUFFER(8000),

15 FIELDNAME_OFFSET TYPE I,

16 * FULLPATH(128),

17 FULLPATH LIKE SXPGCOLIST-PARAMETERS,

18 * CMDFULLPATH(128),

19 CMDFULLPATH LIKE SXPGCOLIST-PARAMETERS,

20 CMD(40),

21 IBTCXPM LIKE BTCXPM OCCURS 0.

22

23 FUNCTION Z_DOWNLOAD.

24 *"----

25 ""Local interface:

26 *" IMPORTING

27 *" VALUE(FILENAME)

28 *" VALUE(LOCATION)

29 *" TABLES

30 *" DATA_TAB

31 *" FIELDNAMES OPTIONAL

32 *"----

33

34 DATA: WS_LINE TYPE I.

35

36 FIELD-SYMBOLS: <F>.

37

38 CHECK NOT FILENAME IS INITIAL.

39

40 CONCATENATE C_PATH FILENAME INTO FULLPATH.

41 OPEN DATASET FULLPATH IN TEXT MODE FOR OUTPUT.

42

43 DESCRIBE TABLE FIELDNAMES LINES WS_LINE.

44 IF WS_LINE NE 0.

45 PERFORM FIELDNAMES_2_BUFFER TABLES FIELDNAMES CHANGING BUFFER.

46 FIELDNAME_OFFSET = STRLEN( BUFFER ).

47 TRANSFER BUFFER TO FULLPATH LENGTH FIELDNAME_OFFSET.

48 ENDIF.

49

50 LOOP AT DATA_TAB.

51 CLEAR BUFFER.

52 CLEAR FIELDNAME_OFFSET.

53 DO.

54 ASSIGN COMPONENT SY-INDEX OF STRUCTURE DATA_TAB TO <F>.

55 IF SY-SUBRC NE 0. EXIT. ENDIF.

56 WRITE <F> TO BUFFER+FIELDNAME_OFFSET.

57 CONDENSE BUFFER.

58 FIELDNAME_OFFSET = STRLEN( BUFFER ).

59 WRITE C_TAB TO BUFFER+FIELDNAME_OFFSET(1).

60 ADD 1 TO FIELDNAME_OFFSET.

61 ENDDO.

62 TRANSFER BUFFER TO FULLPATH LENGTH FIELDNAME_OFFSET.

63 ENDLOOP.

64

65 CLOSE DATASET FULLPATH.

66

67 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

68 EXPORTING

69 COMMANDNAME = 'ZCHMOD'

70 ADDITIONAL_PARAMETERS = FULLPATH

71 TABLES

72 EXEC_PROTOCOL = IBTCXPM

73 EXCEPTIONS

74 NO_PERMISSION = 1

75 COMMAND_NOT_FOUND = 2

76 PARAMETERS_TOO_LONG = 3

77 SECURITY_RISK = 4

78 WRONG_CHECK_CALL_INTERFACE = 5

79 PROGRAM_START_ERROR = 6

80 PROGRAM_TERMINATION_ERROR = 7

81 X_ERROR = 8

82 PARAMETER_EXPECTED = 9

83 TOO_MANY_PARAMETERS = 10

84 ILLEGAL_COMMAND = 11

85 WRONG_ASYNCHRONOUS_PARAMETERS = 12

86 CANT_ENQ_TBTCO_ENTRY = 13

87 JOBCOUNT_GENERATION_ERROR = 14

88 OTHERS = 15.

89

90 CONCATENATE C_PATH FILENAME '_cmd' INTO CMDFULLPATH.

91 OPEN DATASET CMDFULLPATH IN TEXT MODE FOR OUTPUT.

92 CASE LOCATION. "location A, B, C, D on a network

93 WHEN 'A '.

94

95 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

96 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

97 WHEN 'B '.

98 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

99 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

100 when 'C '.

101 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

102 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

103 when 'D '.

104 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

105 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

106 WHEN OTHERS.

107 ENDCASE.

108

109

110

111

112 *start>

113 CLEAR Y001.

114 SELECT SINGLE Y_PATH INTO Y001-Y_PATH

115 FROM Y001 WHERE BNAME = SY-UNAME

116 AND Y_SITE = LOCATION.

117 TRANSFER Y001-Y_PATH TO CMDFULLPATH.

118 *<end

119 CONCATENATE 'lcd' C_PATH INTO CMD SEPARATED BY SPACE.

120 TRANSFER CMD TO CMDFULLPATH.

121 CLEAR CMD.

122 CONCATENATE 'put' FILENAME INTO CMD SEPARATED BY SPACE.

123 TRANSFER CMD TO CMDFULLPATH.

124 TRANSFER 'bye' TO CMDFULLPATH.

125 CLOSE DATASET CMDFULLPATH.

126

127 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

128 EXPORTING

129 COMMANDNAME = 'ZCHMOD'

130 ADDITIONAL_PARAMETERS = CMDFULLPATH

131 TABLES

132 EXEC_PROTOCOL = IBTCXPM

133 EXCEPTIONS

134 NO_PERMISSION = 1

135 COMMAND_NOT_FOUND = 2

136 PARAMETERS_TOO_LONG = 3

137 SECURITY_RISK = 4

138 WRONG_CHECK_CALL_INTERFACE = 5

139 PROGRAM_START_ERROR = 6

140 PROGRAM_TERMINATION_ERROR = 7

141 X_ERROR = 8

142 PARAMETER_EXPECTED = 9

143 TOO_MANY_PARAMETERS = 10

144 ILLEGAL_COMMAND = 11

145 WRONG_ASYNCHRONOUS_PARAMETERS = 12

146 CANT_ENQ_TBTCO_ENTRY = 13

147 JOBCOUNT_GENERATION_ERROR = 14

148 OTHERS = 15.

149

150 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

151 EXPORTING

152 COMMANDNAME = 'ZFTP'

153 ** commandname = 'ZFTP'

154 ADDITIONAL_PARAMETERS = CMDFULLPATH

155 TABLES

156 EXEC_PROTOCOL = IBTCXPM

157 EXCEPTIONS

158 NO_PERMISSION = 1

159 COMMAND_NOT_FOUND = 2

160 PARAMETERS_TOO_LONG = 3

161 SECURITY_RISK = 4

162 WRONG_CHECK_CALL_INTERFACE = 5

163 PROGRAM_START_ERROR = 6

164 PROGRAM_TERMINATION_ERROR = 7

165 X_ERROR = 8

166 PARAMETER_EXPECTED = 9

167 TOO_MANY_PARAMETERS = 10

168 ILLEGAL_COMMAND = 11

169 WRONG_ASYNCHRONOUS_PARAMETERS = 12

170 CANT_ENQ_TBTCO_ENTRY = 13

171 JOBCOUNT_GENERATION_ERROR = 14

172 OTHERS = 15.

173

174 ENDFUNCTION.

175

176 ----

177 * FORM FIELDNAMES_2_BUFFER *

178 ----

179 * ........ *

180 ----

181 * --> FIELDNAMES *

182 * --> BUFFER *

183 ----

184 FORM FIELDNAMES_2_BUFFER TABLES FIELDNAMES CHANGING BUFFER.

185 CLEAR BUFFER.

186 CLEAR FIELDNAME_OFFSET.

187 LOOP AT FIELDNAMES.

188 WRITE FIELDNAMES TO BUFFER+FIELDNAME_OFFSET.

189 CONDENSE BUFFER.

190 FIELDNAME_OFFSET = STRLEN( BUFFER ).

191 WRITE C_TAB TO BUFFER+FIELDNAME_OFFSET(1).

192 ADD 1 TO FIELDNAME_OFFSET.

193 ENDLOOP.

194 FIELDNAME_OFFSET = FIELDNAME_OFFSET - 1.

195 IF FIELDNAME_OFFSET >= 0.

196 WRITE SPACE TO BUFFER+FIELDNAME_OFFSET(1).

197 ENDIF.

198 ENDFORM

Former Member · ‎11-20-2007

Hi,

Do like this

OPEN DATASET filename1 FOR OUTPUT IN TEXT MODE.
IF SY-SUBRC = 0.
 LOOP AT <b>itab1</b> into <b>wa_itab</b>.
  TRANSFER <b>wa_itab</b> TO filename1.
  IF SY-SUBRC <> 0.
   EXIT.
  ENDIF.
 ENDLOOP.
ENDIF.

Check this link to download to excel

Reward Points if this helps,

Satish

Former Member · ‎11-20-2007

I want to pass the data to the excel file from an internal table which have so many columns.

Thanks & Regards

Santhosh

Former Member · ‎11-20-2007

Hi,

If you want to save a excel file in a background process you have to save your file to the aplication server.

In aplication server you can´t use the usual FM to convert data to XLS because those FMs use the SAP GUI. So you have to save you data with OPEN DATASET, writting a file that Excel can understand. I sugest that you take a look at these links:

/people/sergio.ferrari2/blog/2006/06/12/spreadsheet-integration--which-approach-for-which-sap-technology

/people/sergio.ferrari2/blog/2006/06/11/downloading-data-into-excel-with-format-options-from-sap-web-applications

Regards