Broken Lines in the raw file

[Scheruku]

I have a pipe delimited file where the data is broken down to next rows. The number of columns are 10, and the pipe count is 9.

Below the broken data -

JE_HEADER_ID INT|DESCRIPTION NVARCHAR(1000)|JE_LINE_NUM INT|CODE_COMBINATION_ID INT|ACCOUNTED_DR NUMERIC(30,4)|ACCOUNTED_CR NUMERIC(30,4)|ENTERED_DR NUMERIC(30,4)|ENTERED_CR NUMERIC(30,4)|CREATED_BY INT|STATUS
32775865|Journal Import Created|14962|8769964||32.33||32.33|1141|P
32775865|A6714467
|14966|8769964||36.27||36.27|1141|P
32775865|Journal Import Created|14967|8769964||37.01||37.01|1141|P
32775865|A6198486
|14972|8769964||42.04||42.04|1141|P
32775865|Journal Import Created|14969|
8769964||38.42||38.42|1141|
P

My final output should be as below -

32775865|Journal Import Created|14962|8769964||32.33||32.33|1141|P
32775865|A6714467|14966|8769964||36.27||36.27|1141|P
32775865|Journal Import Created|14967|8769964||37.01||37.01|1141|P
32775865|A6198486|14972|8769964||42.04||42.04|1141|P
32775865|Journal Import Created|14969|8769964||38.42||38.42|1141|P

Best Regards,

Cheruku

[vishwa_0308]

Hi @Scheruku,

I have worked on your query and seems it will fulfill your requirement. Please try this attached workflow.

Best,

Vishwa

[jdunkerley79]

I suggest the following approach:

- Count the | in each record

- Create a running total of pipes and assign each string to a record

- Summarise by these record to make the concatenated string

Sample attached

[Scheruku]

Thanks much for the help. The solution was working excellently but we noticed there were blank lines between the lines because of the output not coming correctly. This could be some special characters. Could you please suggest a solution for removing those blank spaces between the lines, and the using your solution, please.

32684362|Bro|3|8272651|715||715||51905|P
32684363|month of August 2017

Vendor |1|8898305|96||96||51905|P

32684363|month of August 2017

Vendor GX|2|8898306|96||96||51905|P

32684363| Line|4|1386564||1256||1256|51905|P
32684364| EXPS|1|8970293|315||315||51905|P

[jdunkerley79 · Accepted solution]

The raw data sample doesn't line up with the records you had an issue with. Could you send the ones in the image? 

If you want to DM me the raw file happy to take a look

[Scheruku]

Thank you so much for the help. I have changed the length of field length while import, and it's working fine.

Great Solution!

[dhruba1994]

Hi,

This is indeed a great solution. I have just one question here. What should be judgement to choose the divisor? Like, here you have chosen 9 in the floor function.