comma-delimited record with " into itab
Hi,
I've a list of records containing comma-delimited data:
e.g. "20032110","100.23","58 main street", "0"
I have another internal table which has all the fields to match the record:
e.g. data: begin of itab2 occurs 0,
indate(,
amt01(10),
addr(20),
eof,
end of itab2.
My question is: what is the easiest way to read in the comma-delimited file into itab2 (without the ")? Thks.
The easiest way is not to output them with double quotes in the first place. Have you thought of that option ?
Sam I am
Have a look at this:
/forums/viewtopic.php?t=80189
_________________
Gehe am 30. 6. 2004 mit 53 in den Vorruhestand - Hat jemand 'ne bessere Idee?
Hans suggestion will correctly split the file, but that's only half the battle.
They also need to remove the (") double quote characters.
My suggestion still stands, but, if you really must read this kind of file in then I would suggest that you use the REPLACE command to replace all double-quotation marks with a NULL. That effectively should remove them.
Sam I am.
Sam I am: Have to take the file as it comes, I'm afraid!
Hans: Thanks. I'm already using REPLACE (rather than TRANSLATE) to remove the quotes. SPLIT, however, appends each field as a new record in the itab.
Removing the quotes is not a problem - that's the easy part!
I'm trying to move each field in the input file records into its own field in the itab.
Can I ask where your source file is located ?
For example, if it does not reside on the APP server then you can use WS_UPLOAD/DOWNLOAD to read your file directly into an itab. You'll need to ensure that each field of the itab is two characters bigger than it needs to be, to accomodate for the " quote characters. Loop through each record and REPLACE the quote (") characters and save the record.
But that assumes that you are using WS_UPLOAD/DOWNLOAD (I can't remember which one is which).
Failing that, use the split command placing the results into a temp table. Loop through the temp table and within the loop have a CASE statement against SY-INDEX to set each field according to the record. This should be constant. Then save the record and continue onto the next source record.
I hope that makes sense.
If the file is held on the APP server then just OPEN the source file, READ record, replace quote characters, save into itab, and repeat until EOF.
I'm sure that you've thought of most of these already. Let us know the source area though because you may get a few better suggestions.
Dr Sidewalk.
_________________
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it". David King
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
This might be a bit simplistic. It's home time and I'm tired.
data: field1(100) value ' "20032110","100.23","58 main street", "0" '.
* variables used as dumping area
data: f01(20),
f02(20),
f03(20),
f04(20),
f05(20),
f06(20),
f07(20),
f08(20),
f09(20),
f10(20),
f11(20),
f12(20),
f13(20),
f14(20),
f15(20).
data: begin of itab occurs 0,
date(,
value(20),
addr(20),
number(1),
end of itab.
split field1 at '"' into
f01 itab-date f03 itab-value f05 itab-addr f07 itab-number f09 f10 f11
f12 f13 f14 f15.
append itab.
*************
EDIT: This assumes the quotations are consistent, of course.
The reason why this file has quotes around strings is that it's a properly formatted CSV file that quotes strings because the strings themselves may contain commas as valid data. However, they have gone over the top a bit by quoting numbers.
If you use SPLIT AT '"' into Itab, every odd numbered record should be blank. Using field symbols you can transfer the data something along these lines:
Do.
Assign <field-symbol> to component sy-index of structure my_struc.
If sy-subrc = 0.
Compute Record_No = ( 2 * sy-index ) - 1.
Read Table Split_Tab index Record_No.
MOve Split_Tab-field to <field-Symbol>.
Else.
Exit.
EndDo.
Append my_Struc to My_tab.
_________________
Regards
R
Abap KC
SFMDR
Hehe, Excel will always get you! Try to have negative numbers and strings that contain commas or quotes inside. Even more fun can give localized versions of Excel, if you work, say, in US, and your happy users are in Germany.
My ugly way (for US-like format):
* Int. table to keep PC file records
DATA: BEGIN OF IT_SSHEET OCCURS 0,
F1(40) TYPE C, F2(40) TYPE C,
.......
END OF IT_SSHEET.
* Declare type for table with CSV-text line 1024 chars max (can change)
TYPES: TYPE_RAW_CSV(1024) TYPE C OCCURS 0.
* SKIP................
*---------------------------------------------------------------------*
* FORM UPLOAD_CSV_SPREADSHEET *
*---------------------------------------------------------------------*
* Reads CSV file from PC *
*---------------------------------------------------------------------*
* --> PI_SSHEET [in] Spreadsheet *
* --> PI_FNAME [in] File name *
* --> PO_ERRORFLAG [out] Error flag, 'X' if error *
* --> PO_ERRORMSG [out] Error message *
*---------------------------------------------------------------------*
FORM UPLOAD_CSV_SPREADSHEET
TABLES
PI_SSHEET
USING
PI_FNAME
CHANGING
PO_ERRORFLAG
PO_ERRORMSG.
CONSTANTS:
C_ASCII_FILE(3) TYPE C VALUE 'ASC'.
DATA: IT_RAW TYPE TYPE_RAW_CSV WITH HEADER LINE.
CALL FUNCTION 'WS_UPLOAD'
EXPORTING
FILENAME = PI_FNAME
FILETYPE = C_ASCII_FILE
TABLES
DATA_TAB = IT_RAW
EXCEPTIONS
CONVERSION_ERROR = 1
FILE_OPEN_ERROR = 2
FILE_READ_ERROR = 3
INVALID_TABLE_WIDTH = 4
INVALID_TYPE = 5
NO_BATCH = 6
UNKNOWN_ERROR = 7
OTHERS = 8.
IF SY-SUBRC = 0.
PERFORM PARSE_RAW_CSV
TABLES IT_RAW PI_SSHEET.
ELSE.
PO_ERRORFLAG = C_X.
PO_ERRORMSG = TEXT-ERF.
ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
* FORM PARSE_RAW_CSV *
*---------------------------------------------------------------------*
* Parses CSV file, converting raw text to columns *
*---------------------------------------------------------------------*
* --> PI_RAW [in] Spreadsheet in raw text format *
* --> PO_SSHEET [out] Parsed spreadsheet *
*---------------------------------------------------------------------*
FORM PARSE_RAW_CSV
TABLES
PI_RAW TYPE TYPE_RAW_CSV
PO_SSHEET.
CONSTANTS:
C_QUOTE(1) TYPE C VALUE '"',
C_COMMA(1) TYPE C VALUE ','.
DATA:
M_COLUMN TYPE I VALUE 0,
M_STR LIKE LINE OF PI_RAW,
M_LEN TYPE I,
M_QUOTEFLAG(1) TYPE C,
M_SPACEFLAG(1) TYPE C,
M_CELL(40) TYPE C,
M_END_OF_SHEET_LINE(1) TYPE C.
FIELD-SYMBOLS:
<FS_LOCAL>.
REFRESH PO_SSHEET.
CLEAR PO_SSHEET.
LOOP AT PI_RAW.
M_STR = PI_RAW.
M_LEN = STRLEN( M_STR ).
M_COLUMN = 1.
CLEAR M_END_OF_SHEET_LINE.
* if there are columns to extract from and place to put them
WHILE M_LEN > 0 AND M_END_OF_SHEET_LINE IS INITIAL.
SHIFT M_STR LEFT DELETING LEADING SPACE.
M_LEN = STRLEN( M_STR ).
IF M_LEN > 0.
CLEAR: M_QUOTEFLAG, M_SPACEFLAG, M_CELL.
IF M_STR(1) = C_QUOTE.
M_QUOTEFLAG = C_X.
SHIFT M_STR LEFT BY 1 PLACES.
M_LEN = M_LEN - 1.
ENDIF.
* extract next column, following loop can exit before m_len = 0 !
WHILE M_LEN > 0.
* check for closing comma
IF M_STR(1) = C_COMMA AND M_QUOTEFLAG IS INITIAL.
SHIFT M_STR LEFT BY 1 PLACES.
M_LEN = M_LEN - 1.
EXIT.
ENDIF.
* check for in-cell or closing quote
IF M_STR(1) = C_QUOTE.
IF M_LEN > 1. "can check for in-cell
IF M_STR+1(1) = C_QUOTE. "it's in-cell quote
IF M_SPACEFLAG = C_X.
CONCATENATE M_CELL C_QUOTE INTO M_CELL
SEPARATED BY SPACE.
CLEAR M_SPACEFLAG.
ELSE.
CONCATENATE M_CELL C_QUOTE INTO M_CELL.
ENDIF.
SHIFT M_STR LEFT BY 2 PLACES.
M_LEN = M_LEN - 2.
CONTINUE.
ENDIF.
ENDIF. "if it's not in-cell then it's closing quote
CLEAR M_QUOTEFLAG.
SHIFT M_STR LEFT BY 1 PLACES.
M_LEN = M_LEN - 1.
IF M_LEN > 0. "if it was not last cell, comma should be next
IF M_STR(1) <> C_COMMA. "it should be... but let's check
EXIT.
ENDIF.
ENDIF.
CONTINUE.
ENDIF. "quote check
* if none of above, it's just a next symbol in a cell
IF M_STR(1) = SPACE. "concat deletes spaces!
M_SPACEFLAG = C_X.
ELSE.
IF M_SPACEFLAG = C_X.
CONCATENATE M_CELL M_STR(1) INTO M_CELL
SEPARATED BY SPACE.
CLEAR M_SPACEFLAG.
ELSE.
CONCATENATE M_CELL M_STR(1) INTO M_CELL.
ENDIF.
ENDIF.
SHIFT M_STR LEFT BY 1 PLACES.
M_LEN = M_LEN - 1.
ENDWHILE. "extract next cell
ASSIGN COMPONENT M_COLUMN OF STRUCTURE PO_SSHEET TO <FS_LOCAL>.
IF SY-SUBRC = 0.
<FS_LOCAL> = M_CELL.
M_COLUMN = M_COLUMN + 1.
ELSE.
M_END_OF_SHEET_LINE = C_X.
ENDIF.
ENDIF. "there's something to extract
ENDWHILE.
APPEND PO_SSHEET.
CLEAR PO_SSHEET.
ENDLOOP.
ENDFORM.