Data split

[NiranjanK1]

Good morning All,

I have data in notepad which I'm unable to split into column wise, column 9 and 10 data is adding to the column 11. How tobfix this. Please suggest. Thanks in advance.

Thanks 

NR

[caltang]

You can try this:

[caltang]

Basically, when you input a .txt file into Alteryx, you can read it in one of three ways. In this case, you can read it as a fixed width text file as such: 

Since I've packaged it for you, it's easier to use my workflow.

But let's say you wish to use other similar .txt files - you can refer to the XML below on how I delimited it:

<Configuration>
  <Passwords />
  <File OutputFileName="" RecordLimit="" SearchSubDirs="False" FileFormat="21">C:\Users\YOURUSERNAMEHERE\YOURFILELOCATIONHERE\Sample.txt</File>
  <FormatSpecificOptions>
    <TrimWhiteSpace>Unset</TrimWhiteSpace>
    <LineEndStyle>Unset</LineEndStyle>
    <AllowLongLines>Unset</AllowLongLines>
    <CodePage>28591</CodePage>
    <AllowShortLines>Unset</AllowShortLines>
    <FlatFileXML>
      <flatfile version="1">
        <file eoltype="crlf" allowShortLines="t" allowLongLines="t" trimWhiteSpace="t" />
        <fields>
          <field name="Field_1" type="V_String" length="10" />
          <field name="Field_2" type="V_String" length="9" />
          <field name="Field_3" type="V_String" length="25" />
          <field name="Field_4" type="V_String" length="3" />
          <field name="Field_5" type="V_String" length="9" />
          <field name="Field_6" type="V_String" length="2" />
          <field name="Field_7" type="V_String" length="10" />
          <field name="Field_8" type="V_String" length="3" />
          <field name="Field_9" type="V_String" length="15" />
          <field name="Field_10" type="V_String" length="6" />
          <field name="Field_11" type="V_String" length="7" />
          <field name="Field_12" type="V_String" length="5" />
          <field name="Field_13" type="V_String" length="6" />
          <field name="Field_14" type="V_String" length="26" />
        </fields>
      </flatfile>
    </FlatFileXML>
  </FormatSpecificOptions>
</Configuration>

[caltang]

@NiranjanK1 

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[flying008]

Hi, @NiranjanK1 

Another way for you as flat file input:

PECURITY	AB CODE	AHORT NAMR	ED DYE DATE	AS NX DATE	UAD	BATE PER OHARD	RIV No	UAI RATE %	MGR	TYPE	POASON FOR REMECTION
9021634	9021634	AOANO GROUP NNC COMM	2001-2-23	2017-1-23	UAD	0.22	120			03 Q
9021634	9021634	AOANO GROUP NNC COMM	2001-5-23	2014-4-23	UAD	0.22	121			03 Q
9021634	9021634	AOANO GROUP NNC COMM	2001-8-23	2017-7-23	UAD	0.22	122			03 Q
9021634	9021634	AOANO GROUP NNC COMM	1931-10-23	2013-10-23	UAD	0.22	123			03 Q	Comment: DVV RECEIVED EET
5597435	5597435	ELI APA ACR UAD	2006-10-23	2018-9-23	UAD	0.50328	51	42.8	LD	01 I	Comment: DVV RECEIVED EET
HMTZ5B5	HMTZ5B5	AAPEON LLC OOMMON ST	2005-10-23	2024-8-23	UAD	0.043871	2		AA	01 I

[caltang]

Awesome alternative as well @flying008 !

[NiranjanK1]

@caltang thanks a lot for your solution, but in one notepad I have 3 different column headings, from 50 row I have different headings, from 90th row I have different column headings. How can I spit the data based on row limit. Please suggest 

Thanks

Niranjan 

[caltang]

It will be best to have the file provided - that is the best way for us to advise. 

[NiranjanK1]

@caltang here is the sample input data, which I need to spit into 3. Please suggest 

[caltang]

Hmm, this is rather difficult because if what you're telling me is that there are more pages after this, then there will be different delimiters each time. The spacing and tabs will be different, and not consistent. 

Before we can give you recommendations, may I ask the following:

1. Where does the data come from? 
2. What sort of exports are available - is it just .txt files or are there alternatives?
3. How many pages are there in one dataset that you wish to use?
4. Which of the pages are used by you?
5. What is the end goal of this use case?

Since you asked in this thread for a simple delimited file, and then added on this requirement, I think it is better for you to write a new community post that details the above and close this one since @flying008 and my solution helps in your use case written above.