Data split

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

Sample.txt

Input

Text Mining

Data Investigation

Accepted answers

All comments

caltang

You can try this:

ForNiranjanK1.yxzp

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:

录制_2023_10_09_14_58_14_611.gif

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

Sample.txt

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:

Where does the data come from?
What sort of exports are available - is it just .txt files or are there alternatives?
How many pages are there in one dataset that you wish to use?
Which of the pages are used by you?
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.