Answered

Extract Before/Upto Character in Text file

(0) Share

Report

Posted on by AaronC

The goal is to capture each item in this text file and add row to a dataverse table. I'm thinking I would need to capture everything from W2* until the next W2* and then parse.
HEADER INFORMATION- shows up once. This is like the flight info- Delta flight 005, from Atlanta to London- Heathrow, carrying 307 passengers, 12 crew members, 176,413 pounds weight at take-off- blah blah

#UPT UP 4628SWACO002412202049RMXXCORP/
GS*IC*UP*CORP*20241220*2049*10176*X*008030
ST*418*101760001
BAX*855638*L*139*20241220*1849*OFFERING*855638
W1*CORP

RAILCAR INFORMATION- Repeats for as many railcars are on the train. This would be where each individuals passenger information is included.

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

Question is how can I extract the railcar information for each?

edi769437563.txt

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

Nived_Nambiar 18,136 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @CU07011500-0

Just to confirm - you want to extract the lines that start with W and a digit ?

Thanks & Regards,
Nived N

Stay connected:
LinkedIn | YouTube | Blogs

Was this answer helpful?
If yes, please mark it as the solution by selecting the checkbox in the discussion thread.
Your feedback motivates me to keep contributing. Thank you!

Was this reply helpful? Yes No
AaronC 16 on at

Like (0)

Report
Copy link

Link copied!

Hi @Nived_Nambiar

I will confirm this afternoon and let you know in a few hours. Thank you for the response!

Was this reply helpful? Yes No
AaronC 16 on at

Like (0)

Report
Copy link

Link copied!

Hi @Nived_Nambiar

What is needed is basically a row would be created and each item would be its own fields.

For example:

(This would be the text)

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

GGCX > Field 1

5843 > Field 2

2821141 > Field 3

RR > Field 4

Moving on to the next line w3...

515582 > Field 5

20241206 > Field 6

and so on...

The only exception is that Line W5 can all be extracted into a single field. Does this help?

Was this reply helpful? Yes No
Suggested answer

Nived_Nambiar 18,136 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi

So what understand is

you have a text data like this

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

where each paragraph should be inserted into a row - right ?

Thanks & Regards,
Nived N

Stay connected:
LinkedIn | YouTube | Blogs

Was this answer helpful?
If yes, please mark it as the solution by selecting the checkbox in the discussion thread.
Your feedback motivates me to keep contributing. Thank you!

Was this reply helpful? Yes No
Verified answer

Chriddle 8,632 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
This splits your text into sectioons starting with "W2",

then splits each section into lines and each row into values:

Compose

The text from your file

Select

From

skip( split( outputs('Compose'), concat(decodeUriComponent('%0A'), 'W2') ), 1 )

Map Field1

split( split( concat('W2', item()), decodeUriComponent('%0A') )[0], '*' )[1]

Map Field2

split( split( concat('W2', item()), decodeUriComponent('%0A') )[0], '*' )[2]

... etc

(the outer array index is the value, the inner array index is the row)

Was this reply helpful? Yes No
CU07011500-0 on at

Like (0)

Report
Copy link

Link copied!

Thank you Chriddle

When I run the following expressions...

From:

skip(split(outputs('Compose'),concat(decodeUriComponent('$0A'), 'W2')),1)

Map:

Field 1

split(split(concat('W2',item()),decodeUriComponent('%0A'))[0],'*')[1]

The Select output is blank. What am I doing wrong?

Was this reply helpful? Yes No
AaronC 16 on at

Like (0)

Report
Copy link

Link copied!

@Nived_Nambiar

Here is an example (I'm using excel to visually show what it should be in Dataverse).

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

W2*GGCX*5843*2821141*RR*L*98*********C214
W3*515582*20241206*ATKOREPLAPIP*EUGENE*OR
W4*WESTLACHEVIN*UP*53714*ALLEMANIA*LA
W5*UP*EUGEN*CORP

In Dataverse it should look something like this...

Each item above has the same information so it would look all the same in this situation. Field 11 I'm showing that the last line (W5) can be populated with the full line. So image all the other lines being broken out into each * section having its own field with exception to the first line and everything after 98. We dont need the **********C214.

Was this reply helpful? Yes No
AaronC 16 on at

Like (0)

Report
Copy link

Link copied!

@Chriddle

This is super helpful!

I ran the following...

From -

skip(split(outputs('Compose'),concat(decodeUriComponent('$0A'), 'W2')),1)

Map -

split(split(concat('W2',item()),decodeUriComponent('%0A'))[0],'*')[1]

My results were blank. What am I doing wrong?

Was this reply helpful? Yes No
AaronC 16 on at

Like (0)

Report
Copy link

Link copied!

@Chriddle

I got it to work!

I was trying to post in the expression with one line which wasn't working.

I then copy and pasted your code and it worked!

Was this reply helpful? Yes No