海量数据如何收集
这个看似很简单的问题
我就想当然的以为上传后再字符串分割就可以了 真正做起来 可让我牺牲了很多脑细胞
遇到以下瓶颈
1.上传文件不支持超过256字符的 这个和调试无关 调试程序过程中看不到后续字符
是正常现象,但是sap自带的excel上传函数确实没有办法上传
2.对于上传过来的字符 分割问题
这个地方也遇到相当多的麻烦 首先是我用的+(9)这种方式 发现有乱码。后来用中文字符判断也不可以。
以下是两个问题的解决办法,也是参考了其他资料,非常感谢!
【问题1】不支持超过256字符解决办法:
解决办法需要做改动 在参考网上的文档又经过对空格的处理(¥) 能够完成需求要求的
这种对单元格的读取 能够解决大部分的excel读取问题
start-of-selection.
data rcode.
call function 'WS_QUERY'
exporting
filename = p_file
query = 'FE'
importing
return = rcode
exceptions
inv_query = 01
no_batch = 02.
if rcode = 0.
message '请检查文件是否存在并保持文件关闭' type 'I'.
flag = 2.
endif.
check flag = 0.
perform zalsm_excel_to_internal_table
tables
gt_upload
using
p_file
1
1
* 9
* 5000.
25
1000.
*if sy-subrc <> 0.
*write:/ '请检查文件是否存在并保持文件关闭'.
*flag = 2.
*endif.
data number type
i.
number = 1.
data ban type i.
loop at gt_upload.
ban = number mod 25.
number = number + 1.
if ban = 1.
itab-idnum = gt_upload-value.
endif.
if ban = 3.
itab-day = gt_upload-value(4).
itab-day+4(2) = gt_upload-value+5(2) .
itab-day+6(2) = gt_upload-value+8(2) .
endif.
if ban = 2.
itab-name = gt_upload-value.
endif.
if ban = 4.
itab-yykpnd_009 = gt_upload-value.
endif.
if ban = 4. itab-yykpnd_009 = gt_upload-value. endif.
if ban = 5. itab-yykpzq_010 = gt_upload-value. endif.
if ban = 24.
itab-string1 = gt_upload-value.
endif.
if ban = 0.
itab-string2 = gt_upload-value.
append itab.
endif.
* WRITE: GT_UPLOAD-VALUE+256(20).
endloop.
delete itab index 1.
form zalsm_excel_to_internal_table
tables
intern like gt_upload[]
using
value(filename) like rlgrap-filename
value(i_begin_col) type i
value(i_begin_row) type i
value(i_end_col) type i
value(i_end_row) type i.
data: excel_tab type ty_t_sender.
data: ld_separator type c.
data: application type ole2_object,
workbook type ole2_object,
range type ole2_object,
worksheet type ole2_object.
data: h_cell type ole2_object,
h_cell1 type ole2_object.
data:
ld_rc type i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
* check parameters IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
if i_begin_col > i_end_col. raise inconsistent_parameters. endif.
* Get TAB-sign for separation of fields
class cl_abap_char_utilities definition load.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
* open file in Excel
if application-header = space or application-handle = -1.
create object application 'Excel.Application'.
m_message.
endif.
call method of application 'Workbooks' = workbook.
m_message.
call method of workbook 'Open' exporting #1 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
get property of application 'ACTIVESHEET' = worksheet.
m_message.
* mark whole spread sheet
call method of worksheet 'Cells' = h_cell
exporting #1 = i_begin_row #2 = i_begin_col.
m_message.
call method of worksheet 'Cells' = h_cell1
exporting #1 = i_end_row #2 = i_end_col.
m_message.
call method of worksheet 'RANGE' = range
exporting #1 = h_cell #2 = h_cell1.
m_message.
call method of range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
call method of range 'COPY'.
m_message.
* read clipboard into ABAP
call method cl_gui_frontend_services=>clipboard_import
importing
data = excel_tab
exceptions
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
others = 4
.
if sy-subrc <> 0.
message a037(alsmex).
endif.
perform separated_to_intern_convert tables excel_tab intern
using ld_separator.
* clear clipboard
refresh excel_tab.
call method cl_gui_frontend_services=>clipboard_export
importing
data = excel_tab
changing
rc = ld_rc
exceptions
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
others = 4
.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
call method of application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
free object h_cell. m_message.
free object h_cell1. m_message.
free object range. m_message.
free object worksheet. m_message.
free object workbook. m_message.
free object application. m_message.
* <<<<< End of change note 575877
endform. "zalsm_excel_to_internal_table
form separated_to_intern_convert tables i_tab type ty_t_sender
i_intern type ty_t_itab
using i_separator type c.
data: l_sic_tabix like sy-tabix,
l_sic_col type kcd_ex_col.
data: l_fdpos like sy-fdpos.
refresh i_intern.
loop at i_tab.
l_sic_tabix = sy-tabix.
l_sic_col = 0.
while i_tab ca i_separator.
l_fdpos = sy-fdpos.
l_sic_col = l_sic_col + 1.
perform line_to_cell_separat tables i_intern
using i_tab l_sic_tabix l_sic_col
i_separator l_fdpos.
endwhile.
* IF i_tab <> space.-----------¥¥---------------------------------------------------------------
clear i_intern.
i_intern-row = l_sic_tabix.
i_intern-col = l_sic_col + 1.
i_intern-value = i_tab.
append i_intern.
* ENDIF.
endloop.
endform. " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
form line_to_cell_separat tables i_intern type ty_t_itab
using i_line
i_row like sy-tabix
ch_cell_col type kcd_ex_col
i_separator type c
i_fdpos like sy-fdpos.
data: l_string type ty_s_senderline.
data l_sic_int type i.
clear i_intern.
l_sic_int = i_fdpos.
i_intern-row = i_row.
l_string = i_line.
i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
if ( i_separator = ';' or i_separator = ',' ) and
l_string(1) = gc_esc.
perform line_to_cell_esc_sep using l_string
l_sic_int
i_separator
i_intern-value.
else.
if l_sic_int > 0."------------------------------------------------------------------
i_intern-value = i_line(l_sic_int).
else."--------------------------------------
i_intern-value = ''.
endif."-----------------------------------------
endif.
* IF l_sic_int > 0.-----$$-------------------
append i_intern.
* ENDIF.----------------------------------
l_sic_int = l_sic_int + 1.
i_line = i_line+l_sic_int.
endform. "line_to_cell_separat
*---------------------------------------------------------------------*
form line_to_cell_esc_sep using i_string
i_sic_int type i
i_separator type c
i_intern_value type ty_d_itabvalue.
data: l_int type i,
l_cell_end(2).
field-symbols: <l_cell>.
l_cell_end = gc_esc.
l_cell_end+1 = i_separator .
if i_string cs gc_esc.
i_string = i_string+1.
if i_string cs l_cell_end.
l_int = sy-fdpos.
assign i_string(l_int) to <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 2.
i_sic_int = l_int.
i_string = i_string+l_int.
elseif i_string cs gc_esc.
* letzte Celle
l_int = sy-fdpos.
assign i_string(l_int) to <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 1.
i_sic_int = l_int.
i_string = i_string+l_int.
l_int = strlen( i_string ).
if l_int > 0 . message x001(kx) . endif.
else.
message x001(kx) . "was ist mit csv-Format
endif.
endif.
endform. "line_to_cell_esc_sep
【问题2】字符 分割问题解决过程:
首先是我用的+(9)这种方式 发现有乱码,
后来参考网上的http://blogold.chinaunix.net/u2/86821/article_117052.html
1,在Non-Unicode系统中,一个汉字占2个单位长度,可以用CHARLEN的返回值判断是否汉字。
DATA: len TYPE i,
str TYPE c LENGTH 20.
str = 'Hello哈哈'.
len = CHARLEN( str )."1,代表英文
len = CHARLEN( str+4 )."1,代表英文
len = CHARLEN( str+5 )."2,代表汉字
len = CHARLEN( str+6 )."2,代表汉字
2,在Unicode系统中,汉字与英文一样占1个单位长……
这个依然没有解决问题
本意是想在某个取出来是汉字时候 -1再取 后来发现总是出问题 因为你无法判断是汉字的前半部分还是后半部分 也许本来不是乱码的 让你-1 倒成了乱码了 总之这个方法放弃
后来又找到的方法
用 function 'STRING_SPLIT_AT_POSITION' 解决
perform convert tables it using itab-string1 .
perform convert tables it1 using itab-string2.
form convert tables it structure it using str .
data: len type i.
data newpos type i.
data d type i.
d = 1500.
data text1 type c length 1500.
data text2 type c length 1500.
data position type i .
position = 98.
* position = 60.
data lens type i.
data flag type i.
flag = 0.
do 10 times.
lens = strlen( str ).
* WRITE lens.
*if lens < position .
*flag = 1.
*position = lens - 1.
*endif.
if lens >= position .
call function 'STRING_SPLIT_AT_POSITION'
exporting
string = str
pos = position
importing
string1 = text1
string2 = text2
pos_new = newpos
exceptions
string1_too_small = 1
string2_too_small = 2
pos_not_valid = 3
others = 4.
it-itline = text1.
append it.
clear text1.
str = text2.
clear text2.
else.
it-itline = str.
append it.
exit.
endif.
*if flag = 1.
*exit.
*endif.
enddo.
loop at it.
* WRITE / it.
endloop.
endform. "convert
具体程序见d盘的文件记录 经典