CSV separator but not

damen · 2024-05-07T15:47:19+00:00

There was an error rendering this rich post.

Ok so I have a string of data that has approximately 60 digits in it.

There is no obvious delimiter - we just have to know the spot where to seperate.

I am working in ETL and am essentially trying to do the SQL function where I start at a certain digit and end at another. I need to do this 7 times over so just using the string operations that doesn't splice the data wont help.

I could splice and resplice based off the created column but I am hoping for a more efficient solution.

Any suggestions?

Here is the string for reference

Find more posts tagged with

Magic ETL

Accepted answers

JasonAltenburg

I think if I'm understanding your problem, I would use SUBSTRING to split your string based on position and characters.

Based on your example:

If the columns are variable in length, and there is no pattern or delimiter, I'm not sure there's a great solution even using regex as you need a pattern to be able to match with regex.

All comments

david_cunningham

@damen If you're wanting to do this as efficiently as possible, your best bet is probably using regex to input commas at your defined intervals, and then use the split string tile.

This approach assumes that your desired split locations are consistent, and that you will always have the same number of splits.

Alternatively, this could be a good use for the Python or R tiles, and you could do it all in one go. If I was going to do it, I would probably use the Python tile.

JasonAltenburg

I think if I'm understanding your problem, I would use SUBSTRING to split your string based on position and characters.

Based on your example:

If the columns are variable in length, and there is no pattern or delimiter, I'm not sure there's a great solution even using regex as you need a pattern to be able to match with regex.

david_cunningham

@damen I like the answer provided by @JasonAltenburg better than my own. When you get ready to select an answer, please choose his instead of mine. I was unintentionally over-complicating things by suggesting python or r.

Agree with what Jason said as well about a variable length or inconsistent pattern, for either of our suggested approaches, those will be necessary.