# 'Separating' a 'long' record CSV file **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Natural, Natural-on-Mainframes\ **Created:** [March 15, 2006, 10:24am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665 "2006-03-15T10:24:21Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Dave\_Bowering](https://avatars.discourse-cdn.com/v4/letter/d/6a8cbe/32.png) [@Dave\_Bowering](https://techcommunity.softwareag.com/u/Dave_Bowering)\ **Post date:** [March 15, 2006, 10:24am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/1 "2006-03-15T10:24:21Z")
I need to read and ‘separate’ a CSV format file into 55 fields. My problem is that the length of each record in the file can be up to 858 characters in length. When I try something like the following, there is no problem. 1 #CSV-INPUT-REC (A253) . . READ WORK FILE RECORD #CSV-INPUT-REC . . SEPARATE #CSV-INPUT-REC INTO #A #B etc. However NAT 3.1.5 on Z/OS does not allow the variable to be defined any larger and SEPARATE does not appear to work with group fields or arrays. For obvious reasons I would prefer to use the SEPARATE statement rather than a manual process. Does anyone have any suggestions? Thanks in advance. ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [March 15, 2006, 12:58pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/2 "2006-03-15T12:58:05Z")
Dave, if the CSV file is simple, i.e. strings not surrounded by double quotes, a combination of manual and automatic separation is possible. Obviously you already have defined the whole record into portions of A253 (or similar) fields. Separate each of these portions into its fields. Then combine the last field of one portion with the first field of the prior portion and you will get what you want. You have to catch the special cases that the last character of a portion is the delimiter character or blank. If the CSV is complex, i.e. strings may be sourrounded by double quotes, this is more complex. It makes no difference if you are in NAT3 or NAT4. You have to delete the double quotes and to keep delimiters within double quotes. So you have to transfer byte by byte. As long as SAG does not add a TYPE CSV [[WITH] [DELIMITER] delim] to the DEFINE WORK FILE, this is still a manual doing. ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [March 15, 2006, 1:32pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/3 "2006-03-15T13:32:16Z")
> [@](#): > > if the CSV file is simple, i.e. strings not surrounded by double quotes, a combination of manual and automatic separation is possible. Obviously you already have defined the whole record into portions of A253 (or similar) fields. Separate each of these portions into its fields. I guess you have to do it completely manually. Because with your logic, you don’t recognize if the last field of a portion is complete or not. A suitalbe solution would be to redefine each portion with (A1/1:253). Otherwise you are forced to work with a separate-loop and a remainder. And this is not very fast, I guess… ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [March 15, 2006, 2:27pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/4 "2006-03-15T14:27:18Z")
> [@](#): > > However NAT 3.1.5 on Z/OS does not allow the variable to be defined any larger By the way: Does the following definition work? ```auto define data local 01 #a1_array (A1/1:1000) end-define ``` ---
**Author:** ![Finn\_the\_Dane](https://avatars.discourse-cdn.com/v4/letter/f/c57346/32.png) [@Finn\_the\_Dane](https://techcommunity.softwareag.com/u/Finn_the_Dane)\ **Post date:** [March 15, 2006, 2:53pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/5 "2006-03-15T14:53:44Z")
Jep ! in nat315 you can define arrays up to 32Kb Finn DEFINE DATA LOCAL 01 ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [March 15, 2006, 6:00pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/6 "2006-03-15T18:00:35Z")
> [@Matthias](#): > > Because with your logic, you don’t recognize if the last field of a portion is complete or not. > [@I](#): > > Then combine the last (should be “first”) field of one portion with the first (should be “last”) field of the prior portion and you will get what you want Ok, I swapped “first” and “last”. > [@Matthias](#): > > A suitalbe solution would be to redefine each portion with (A1/1:253) The statement in question was SEPARATE and SEPARATE does not work on arrays! > [@Dave Bowering](#): > > For obvious reasons I would prefer to use the SEPARATE statement rather than a manual process. ---
**Author:** ![Wei](https://avatars.discourse-cdn.com/v4/letter/w/90ced4/32.png) [@Wei](https://techcommunity.softwareag.com/u/Wei)\ **Post date:** [March 15, 2006, 8:18pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/7 "2006-03-15T20:18:37Z")
Will this Code Work? DEFINE DATA LOCAL 1 C\_ANZ-FIELDS(I2) CONST \<100\> /\* Max number of Fieles expected 1 #CSV-FIELD(A50/C\_ANZ-FIELDS) /\* Max length of a Field is 50 Byte! 1 #INFILE(A100/10) /\* 1000 Bytes to read 1 #CSV-INDEX(I2) /\* Pointer to the current CSV-Field 1 #WORK-INDEX(I2) /\* Pointer to the #infile(_) 1 #A250(A250) /_ Workfield 1 I(I2) /\* Workinteger END-DEFINE READ WORK 1 #INFILE(\*) PERFORM S-SEPARATE2CSV FOR I = 1 TO 50 IF #CSV-FIELD(I) NE ´ ´ DISPLAY I #CSV-FIELD(I) END-IF END-FOR END-WORK * * * DEFINE S-SEPARATE2CSV /\* * * * RESET #WORK-INDEX #CSV-INDEX #A250 REPEAT EXAMINE #A250 FOR ´;´ GIVING NUMBER I /\* Count delimiters IF I \< 2 AND #WORK-INDEX \< 10 /\* only one or less ADD 1 TO #WORK-INDEX /\* take next #infile COMPRESS #A250 #INFILE(#WORK-INDEX) TO #A250 LEAVING NO SPACE END-IF ADD 1 TO #CSV-INDEX SEPARATE #A250 INTO #CSV-FIELD(#CSV-INDEX) REMAINDER #A250 WITH DELIMITER ´;´ WHILE #A250 NE ´ ´ END-REPEAT \* END-SUBROUTINE \* END ---
**Author:** ![Steve\_Robinson](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/steve_robinson/32/107_2.png) [@Steve\_Robinson](https://techcommunity.softwareag.com/u/Steve_Robinson)\ **Post date:** [March 16, 2006, 12:32am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/8 "2006-03-16T00:32:53Z")
Hi Dave; Am busy today so do not have the time to play with the code javascript:emoticon(‘😢’) Crying or Very sad 😢 However, perhaps someone else has the time to play today. READ WORK FILE RECORD #GROUP where 1 #GROUP 2 #a (a250) 2 #b (a250) 2 #C (a250) 2 #d (a250) Now do a separate #a into #array1(_) giving number #num1 separate #B into #array2(_) giving number #num2 separate #c into #array3(_) giving number #num3 separate #d into #array4(_) giving number #num4 Now consider the “interface” between #array1 and #array2. two possibilities 1. either an “exact split” between #a and #b (delimiter at #a position 250 or #b position 1) 2. not If 1) - you are done there If 2) - you must “concatenate” #a (#num1) and #b (1) continue for the other two interfaces. steve ---
**Author:** ![Dave\_Bowering](https://avatars.discourse-cdn.com/v4/letter/d/6a8cbe/32.png) [@Dave\_Bowering](https://techcommunity.softwareag.com/u/Dave_Bowering)\ **Post date:** [March 16, 2006, 4:12am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/9 "2006-03-16T04:12:12Z")
Thanks everyone for your contributions. It seems the best solution as is possible given the scenario. I assume that none of these workarounds are necessary when using NAT4? ---
**Author:** ![Dave\_Bowering](https://avatars.discourse-cdn.com/v4/letter/d/6a8cbe/32.png) [@Dave\_Bowering](https://techcommunity.softwareag.com/u/Dave_Bowering)\ **Post date:** [March 16, 2006, 6:24am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/10 "2006-03-16T06:24:58Z")
For your information I have requested that the file be provided to me with fixed length fields rather than CSV format. I have discovered that it is possible that " characters are present in the CSV file which complicates things more than I would like. The processing becomes more complex when attempting to rejoin the fields that may have been inadvertently separated (E.g. fields containing embedded blanks) and also when separating the array variables into the actual variables and I really want to keep it as simple as possible. Example The following simulation of what may occur when joining the last occurrence of the first array with the first occurrence of the second array. DEFINE DATA LOCAL 1 #A(A50/2) 1 #B(A50/2) 1 #C(A50) END-DEFINE \* #A(1) := ‘BLAH BLAH BLAH’ #A(2) := ‘JOH’ #B(1) := ‘N SMITH’ \* COMPRESS #A(2) #B(1) INTO #C WRITE #C END Without checking the original field how can you know whether the second field contained ‘JOHN SMITH’ or ‘JOH N SMITH’ or ‘JOH N SMITH’? No doubt it can be done but the whole thing is becoming quite messy. Anyway the providers of the CSV file have agreed to change it to a fixed length file with fixed length fields so I dont have to worry about it, but I guess someone might need to one day. Thanks Dave ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [March 16, 2006, 12:58pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/11 "2006-03-16T12:58:03Z")
> [@Dave Bowering](#): > > I assume that none of these workarounds are necessary when using NAT4? affirmative 😉 ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [March 16, 2006, 1:28pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/12 "2006-03-16T13:28:48Z")
Hello Wolfgang Wei ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [March 16, 2006, 2:17pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/13 "2006-03-16T14:17:24Z")
Hi guys, When you have finished this thread, could you please post the working code sample you’ve agreed on to the Natural Open Source forum so we can find it in the future? Thanks, Steve ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [March 16, 2006, 9:45pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/14 "2006-03-16T21:45:17Z")
> [@Wolfgang Winter](#): > > > [@Dave Bowering](#): > > > > I assume that none of these workarounds are necessary when using NAT4? > > affirmative 😉 Hi Wolfgang, I don’t agree. Even with Nat4 you can’t separate CSV files, where text fields are sourrounded by double quotes (and text fields may contain the delimiter character). And Dave has got such a file: > [@Dave Bowering](#): > > I have discovered that it is possible that " characters are present in the CSV file which complicates things more than I would like. But I agree if you only have CSV-like files, where fields are separated by the delimiter character, text fields are not surrounded by double quotes and text fields do not contain the delimiter character. ---
**Author:** ![Jerome\_LeBlanc](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/jerome_leblanc/32/111_2.png) [@Jerome\_LeBlanc](https://techcommunity.softwareag.com/u/Jerome_LeBlanc)\ **Post date:** [March 17, 2006, 12:45am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/15 "2006-03-17T00:45:58Z")
[quote="Wilfried B ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [March 17, 2006, 1:05am UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/16 "2006-03-17T01:05:15Z")
[quote="Wilfried B ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [March 17, 2006, 3:07pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/17 "2006-03-17T15:07:48Z")
> [@Wolfgang Winter](#): > > correctly produces No! Just try: ```auto ... INIT<'ABCDEF;"quoted;string";LMNOP'> ... ``` ---
**Author:** ![Matthias1](https://avatars.discourse-cdn.com/v4/letter/m/9fc348/32.png) [@Matthias1](https://techcommunity.softwareag.com/u/Matthias1)\ **Post date:** [March 17, 2006, 4:56pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/18 "2006-03-17T16:56:03Z")
@Jerome LeBlanc ```auto EXAMINE #CSV-FIELD FOR '""' REPLACE WITH '~' ``` From my point of view this code can cause problems. Imagine a field only containing a double-quote. In CSV this would be: ```auto field1;"""";field2 ``` So I think it’s better to write a proper parser without using SEPARATE. For example: ```auto define data local * 01 #max_length (I4) const <100> 01 #delimiter (A1) const <';'> 01 #quote (A1) const <'"'> * 01 #csvline (A100) init <'"""test""";;"test;test";"""";"test""test";'> 01 redefine #csvline 02 #csv1 (A1/1:#max_length) * 01 #i4 (I4) * 01 #a (A100) 01 redefine #a 02 #a1 (A1/1:#max_length) * 01 #a_index (I4) * 01 #fields (A100/1:#max_length) 01 #field_index (I4) * 01 #field_open (L) 01 #last_delimiter_pos (I4) 01 #skip_next_quote (L) * end-define * for #i4 = 1 to #max_length * decide on first value of #csv1(#i4) * value #delimiter if #field_open add 1 to #a_index move #csv1(#i4) to #a1(#a_index) else move #i4 to #last_delimiter_pos add 1 to #field_index move #a to #fields(#field_index) reset #a #a_index end-if * value #quote if #skip_next_quote reset #skip_next_quote else if #last_delimiter_pos = #i4 - 1 move true to #field_open else if #i4 = #max_length or #csv1(#i4 + 1) ne #quote move false to #field_open else move true to #skip_next_quote add 1 to #a_index move #csv1(#i4) to #a1(#a_index) end-if end-if end-if * none value add 1 to #a_index move #csv1(#i4) to #a1(#a_index) * end-decide * end-for * for #i4 = 1 to #field_index write #i4 #fields(#i4) (AL=50) end-for * end ``` ---
**Author:** ![Wilfried\_B](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/wilfried_b/32/83_2.png) [@Wilfried\_B](https://techcommunity.softwareag.com/u/Wilfried_B)\ **Post date:** [March 17, 2006, 5:32pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/19 "2006-03-17T17:32:00Z")
> [@Wolfgang Winter](#): > > correctly produces > > > [@](#): > > > > 1 ABCDEF > > 2 “quoted string” > > 3 LMNOP > > 4 2: it is not correct, it should read > [@](#): > > 2 quoted string (without the quotes) after CSV parsing. ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [March 17, 2006, 8:43pm UTC](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665/20 "2006-03-17T20:43:17Z")
[quote="Wilfried B [Next page](https://techcommunity.softwareag.com/t/separating-a-long-record-csv-file/60665.md?page=2)