CSV separator but not

Options
damen
damen Contributor

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

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answer

  • JasonAltenburg
    Answer ✓
    Options

    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.

Answers

  • david_cunningham
    edited May 7
    Options

    @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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • JasonAltenburg
    Answer ✓
    Options

    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
    Options

    @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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**