How can I automatically organize data from a text file in a spreadsheet?

Question

Hi folks,

I work at a local business where we are trying to capture all of our customer and their order information onto a Excel spreadsheet.

I have a big text file like this:

and I would like to organize into Excel like this:

I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger scale. I have a huge text file with numerous entries of customer orders, formatted like the text file picture above.

What I've tried so far is to import the text file data into Excel using the "Text Import Wizard" feature with delimiters, I got stuck. I tried to transpose the columns into rows, but that doesn't neatly organize it like I want it. 

This is what I get after importing the delimited text file which is separated by tabs:

The entries are divided into three columns, however, I am not sure how to organize and replicate to the desired state. I apologize if this is a very basic problem, but I am a novice when it comes to dealing with raw data from text files and how to incorporate it into Microsoft Excel.

Is there an efficient way to tackle this problem? Do I need to do some coding or is there a built-in function? If I do need coding, could someone point me in the right direction?

Thanks for your time!

[PII is masked by Dihao Guo MSFT Support]

Answer 1

Upload a sample text file and the required Excel file on your OneDrive and post the download link here. I'll take a look.

Login to https://onedrive.live.com/ (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose share.
Click Get a Link.
Copy the link and paste it here.

Andreas.

Answer 2

Hi,

unfortunately there is no simple way for such transformation. I suppose it could be done if you or someone else will write a macros to parse text file. I do not think the task is very complex but unfortunately my macros skills are not good enough.

I suggest to create a new thread with clearly stated "Please help me to write an Excel macros".

May be it will be useful to ask a question at MSDN forum - https://social.msdn.microsoft.com/Forums/en-us/...

Answer 3

Thank you Igor! I shall post at the MSDN forum.