TXT to Column Help

[ryyl]

Hello,

I am trying to convert my TXT file to Excel but having some difficulty. I've attached a sample raw data and how I want the output to look. I've tried using the text to column function in Excel but the section header on each page is through this off.

Looking for some advice/direction as best to accomplish this.

Thank you in advance for any assistance can you can provide.

Richard

 

[Marc L]

Hello, as your file is a print file like seen more than 30 years ago obviously not designed to be easily imported​

so the easy way is to ask for a file to be imported like a csv format for example …​

​

Or from such print file, import it in a single column then start with the Text To Columns feature to parse the numbers in the same row​

(headers 'Number' to 'Ending Balance') …​

 

[ryyl]

Hello, as your file is a print file like seen more than 30 years ago obviously not designed to be imported​

so the easy way is to ask for a file to be imported like a csv for example …​

​

From such print file, import it in a single column then start with the Text To Columns feature to parse the numbers in the same row​

(headers 'Number' to 'Ending Balance') …​

Appreciate the response.

Yes its a system limitation that we are trying to get updated to the 21st century but not likely anytime soon.

Text to column works for the most part (starting at Asset Number) but what I'm struggling with is how to get these page headers to replicate on the line items.
Currency: USD
Book: SXFAUS120
Ledger: SX GL US 120
Company: 3005
Depreciation Reserve Account: 00014210
Cost Centre: 03545

When I do the Text to column it splits some of these cells (e.g Cost Centre: 03 and then 545) into different columns. If you look at the Excel file that is the output I was hoping for.

 

[p45cal]

In the attached, test right-clicking the table at cell A6 and choosing Refresh.
It will error because my Sample Raw.txt file is not in the same place as yours.
Follow the numbered steps in the picture below: All are single-clicks except no. 4 which is a double-click (or a right-click and choose Edit).




You can do the same with Sample Raw keeping all columns which is just a duplicate of the first but without removing some of the columns and is at cell Q6.

 

[ryyl]

Hello p45cal, Thank you for you assistance. I've tried using your attached template and have update the source file to the same path that you had but getting errors once the data source has changed. Attached are the error messages I'm getting. I'm using Office365. I should be able to just import my full raw data file into the query as the new source? Really appreciate your help.

 

[p45cal]

Update your version!
Anyway, I tweaked the M Code to make it what I think is compatible in the attached, all I did was to change:
= Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
to
= Table.AddIndexColumn(Source, "Index", 1, 1)

 

[ryyl]

AWESOME! THANK YOU SO MUCH.

 

[ryyl]

Update your version!
Anyway, I tweaked the M Code to make it what I think is compatible in the attached, all I did was to change:
= Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
to
= Table.AddIndexColumn(Source, "Index", 1, 1)

Hello, Appreciate all the help! This is great. Looking to learn so I'm trying to replicate the code to another TXT file. Can I simply update the Sample Raw query using the Advanced Editor? Or is there more to the code/script?

If it's easier do you mind if I attach another raw data file and sample output again?

 

[p45cal]

If it's easier do you mind if I attach another raw data file and sample output again?

Sure.

Looking to learn so I'm trying to replicate the code to another TXT file. Can I simply update the Sample Raw query using the Advanced Editor?

If the text file is laid out in the same way you don't need Advanced Editor to edit the code, go throught the steps in the picture in msg#4 to point to the new text file.

Or is there more to the code/script?

There could be, we could put the full path and file name in a cell (or a small table) on an excel sheet and get the code to look at that to find the right text file.

 

[ryyl]

Sure.


If the text file is laid out in the same way you don't need Advanced Editor to edit the code, go throught the steps in the picture in msg#4 to point to the new text file.

There could be, we could put the full path and file name in a cell (or a small table) on an excel sheet and get the code to look at that to find the right text file.

Here's the other sample and output file.

 

[p45cal]

In the attached, there is a named range fullfilepath at cell B2 of Sheet1.
Both queries now look to this cell to workout where to find the text file.

 

[p45cal]

If the text file is laid out in the same way

Sample Raw2.txt has a completely different layout; it fails at the first steps because there are no lines beginning with Totals: to separate the records.

What Marc L said in msg#2 about the layout of these files:

obviously not designed to be easily imported

is true. They look very much like files prepared for printing. If there are lots of different layouts it's going to be hard work to write code for each.
Surely the report generator can produce reports in other ways (ones NOT designed for printing). What's the app which is producing these reports?

 

[ryyl]

Sample Raw2.txt has a completely different layout; it fails at the first steps because there are no lines beginning with Totals: to separate the records.

What Marc L said in msg#2 about the layout of these files:

is true. They look very much like files prepared for printing. If there are lots of different layouts it's going to be hard work to write code for each.
Surely the report generator can produce reports in other ways (ones NOT designed for printing). What's the app which is producing these reports?

Unfortuntely the file output is difficult to have changed if not impossible as this is a 3rd party application without support available. We have been requesting an update for over 5 years and developers have not provided an Excel or actual CVS version. Appreciate your time you have provided. .

 

[Marc L]

we are trying to get updated to the 21st century but not likely anytime soon.

As any text file import format exists since last century …​

​

By experience I do not like to work with such print files 'cause they may change​

even if the text file in the initial post can be easily imported in a worksheet through a VBA procedure​

if the code author just knows some Excel basics like VBA basics as here nothing needs some Excel / VBA expert (easy kid level logic) …​

As often the people in charge of the source application are well payed and do nothin' to help Excel users​

so the easy way is to ask them to do their job ! (Common case example : SAP)​

​

Now the obvious question is : how many different print files layouts do you have ?​

If only two, maybe I could give it a try for both but as you had the weird idea in your last attachment​

to share a worksheet which does very not match the source text file …​

​

 

[ryyl]

Hi Marc, p45cal helped with the first report for the Reserve script already. The more difficult one he pointed out is this one (Asset Addition). My mistake for providing incorrect sample/data. Here's a correct template if you can help. Appreciate it.

 

[Marc L]

Now the obvious question is : how many different print files layouts do you have ?

 

[ryyl]

Just these two layouts.

 

[Marc L]

According to your last attachment there is always only a single data line by Cost Centre & Reserve Account ?​

If not then attach a better relative source text file …​

​

Do you import always the same text filename ?​

​

As a reminder : the better elaboration, the less code modifications you will have to achieve …​

​

 

[ryyl]

Sorry here's the full raw data file. Appreciate your time looking into this.

 

[Marc L]

According to my previous post reminder and according to your last attachment imagine the result worksheet :​

do you have anything to add in your elaboration ?​

​

 

[ryyl]

Each Asset Number would be on its own line. Prior example attached.

Text file name changes but if hardcoded can rename file manually.

Again appreciate all the time and effort looking into this.

 

[Marc L]

Yes but this new workbook is not the expected result according to your post #19 text attachment or I missed something ?​

​

According to your post #19 attachment some result rows do not have the 'RESERVE ACCOUNT' column filled …​

​

 

[ryyl]

Example4.xlsx is the output for the Asset_Additions_Report_041121 .txt

 

[Marc L]

Just taking a glance again to your files I can't very not reproduce your result workbook according to the source text file​

so you should accurately elaborate how can it be possible a source text file with only 8 data rows​

becomes a worksheet with 14 rows where texts like numbers very do not exist in this source text file ?‼​

Even dates : october for source text file and september for result workbook so what's the logic if any ?‼​

​

As a reminder :​

According to your post #19 attachment some result rows do not have the 'RESERVE ACCOUNT' column filled …

 

[ryyl]

Hi Marc - please dont spend anymore time on this. I know its doesnt make sense with the print txt data which a slight change can throw off the VB scripting and all. I appreciate all the time you have taken. I know your time is valuable and appreciate you trying to help out. Thank you again.