Help: Text to Column / Regex on txt file

[pchong]

Hi there,

I am having difficulty reading text files. (Attached for reference)

This was what I did with my input tool:

Ultimately, I want to achieve a proper text to column that look like this :

However, row 2 to 5 will shift to the left because of blanks under column Date3

I would also like to avoid problems with any empty columns like above.

What is the best way to do Text to Column or Regex to get my desired result here?

Thank you.

[afv2688 · Accepted solution]

Hello @pchong ,

how about fixed delimited width?

Regards

[Felipe_Ribeir0 · Accepted solution]

Hi @pchong 

your file seems fixed width. Have you tried this option?

[pchong]

@Felipe_Ribeir0 @afv2688 

Thank you both. 
I'm giving it a try but there is something that giving me a hard time.

Not sure why the file layout go all the way to thousands even though the record length is just 175.

When I almost scrolled to the last column, the layout will jump to the right more than I wish to.

Then when I tried to scroll back to the left, it wouldn't go to the range that I want. (had to scroll a few times to make it work)

Do you have any idea what is causing this scrolling issue?

Thanks!

[Felipe_Ribeir0]

Hi @pchong 

I feel your pain. You need to start from the left to the right and go slowly, otherwise the thing get crazy and go very far away of where you want. Specially close to the last column.

But with some patience, it is possible.

[afv2688]

Yes, this happens from time to time with the tool,

what you could do is generate a so called flat file which has in the end all the info about the structure of your data. Afterwards you can do an import and use it for your data. This is an example of what one file looks like:

<flatfile version="1">
  <file eoltype="crlf" allowShortLines="t" allowLongLines="t" trimWhiteSpace="t" />
  <fields>
    <field name="Field_1" type="V_String" length="8" />
    <field name="Field_2" type="V_String" length="5" />
    <field name="Field_3" type="V_String" length="7" />
    <field name="Field_4" type="V_String" length="23" />
    <field name="Field_5" type="V_String" length="1" />
    <field name="Field_6" type="V_String" length="12" />
    <field name="Field_7" type="V_String" length="1" />
    <field name="Field_8" type="V_String" length="12" />
    <field name="Field_9" type="V_String" length="11" />
    <field name="Field_10" type="V_String" length="11" />
    <field name="Field_11" type="V_String" length="9" />
    <field name="Field_12" type="V_String" length="21" />
    <field name="Field_13" type="V_String" length="10" />
    <field name="Field_14" type="V_String" length="6" />
    <field name="Field_15" type="V_String" length="9" />
    <field name="Field_16" type="V_String" length="15" />
    <field name="Field_17" type="V_String" length="14" />
  </fields>
</flatfile>

just create one file with notepad and fill it like this (extension should be .flat)

Regards

[pchong]

@Felipe_Ribeir0 

That's unfortunate. 
I'm wondering if I can start data import from a specified row with fixed width format?

[Felipe_Ribeir0]

Hi @pchong 

after the input tool, you could use a sample tool to skip n rows (until the header), and take the header from the first row.

Or you cold filter some column in a smart way to keep just the necessary rows.

[pchong]

That's what I had in mind. Just wondering if there's any other way.

Thanks again!

[pchong]

Nice, this is a great tip.

Thanks!