Transform a Single Column Text File to 7 Columns Excel File

sameer79

New member
Joined
Sep 19, 2022
Messages
17
Reaction score
0
Points
1
Excel Version(s)
Excel 2013
Hello All,
I am enclosing herewith a sample of a two files Report.txt and End Result.xlsx

I would like to use PQ to transform the Report to the End Result.

Any help would be greatly appreciated.
 

Attachments

  • Report.txt
    25 KB · Views: 4
  • End Result.xlsx
    14.2 KB · Views: 4

sameer79

New member
Joined
Sep 19, 2022
Messages
17
Reaction score
0
Points
1
Excel Version(s)
Excel 2013
Dear All,
I am enclosing herewith the new set of sample data for transforming.
Please ignore the previous set of files

Thanks in advance.
 

Attachments

  • Input.txt
    22 KB · Views: 2
  • Result.xlsx
    15 KB · Views: 1

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
Surely there must be a better way of obtaining this data rather than have a printer version of it?
Supplier 8343 data doesn't line up because of its long name, the same with suppliers 10308, 11872, 12223,11609.
Something different about supplier 9811.
Page breaks and page headers complicate things.
It's hard work and I'm not sure we'd have a robust transformation at the end.
 

sameer79

New member
Joined
Sep 19, 2022
Messages
17
Reaction score
0
Points
1
Excel Version(s)
Excel 2013
Hi there,
Thanks for taking the time to look into my problem.
I have already raised the matter with the IT. I hope to get a reply (unfortunately they might not come up with a solution I presume)

I have requested the following to them:
The Supplier names should be in one row
The Supplier should not break over two pages eg Supplier 9811. If you will see 9811 is flowing down the next page.

However, if I can get PQ to transform this data to somewhat usable format then I can manage manual editing in the data.

Most important is to get the Supplier Names and the six columns of values. I would not mind if the Supplier names get truncated but the values should show properly.

Thanks once again for looking up in this matter.
 

pinarello

Member
Joined
Jun 21, 2019
Messages
199
Reaction score
2
Points
18
Location
Germany
Excel Version(s)
Office 365
See how it works.
 

Attachments

  • xlguru - Transform a Single Column Text File to 7 Columns Excel File (PQ).xlsx
    29 KB · Views: 2

sameer79

New member
Joined
Sep 19, 2022
Messages
17
Reaction score
0
Points
1
Excel Version(s)
Excel 2013
Hello Pinarello,
Thanks for your time.
How can I use it.
I am stuck on the Import_Data Query. How can I point it to the file on my system.
How can I amend the first applied step wk_file
D:\xlguru-data\Data - Transform a Single Column Text File to 7 Columns Excel File.txt

Kindly guide me.
 

pinarello

Member
Joined
Jun 21, 2019
Messages
199
Reaction score
2
Points
18
Location
Germany
Excel Version(s)
Office 365
I made it quasi parameterized and thus actually simple. You have to enter path and filename of the file in named field "wk_file" (cell B3) and then you can directly update the query. I also avoided to address the current headers in the queries, because otherwise they would not run with the data of other months.

However, from the specifics of the list, I could only take into account those that I could find in the sample workbook.

If you run the queries step by step, you will also be able to see the methodology I used to prepare the data. The most important thing was to analyze the input beforehand in order to be able to determine the sequence.
 

sameer79

New member
Joined
Sep 19, 2022
Messages
17
Reaction score
0
Points
1
Excel Version(s)
Excel 2013
Hi Pinarello,
Thank you so much for your help. It works like a charm.

But my knowledge of PQ is not that high as yours.

I am still trying to understand quasi paramaterized? This is the first time I have seen a transformation with this type.

The transformation part I am still trying to understand and connect the 3 queries you have made for the great job you have done.

Thanks once again for helping me out.
 
Top