Hello All,
I have a complex text file which I want to import into Excel. I have attached a sample which consists of 2 tables. The original file consists of 500 tables similar to the ones in the sample file. All the 500 tables are in one text file. Can someone please help or advise how I can get this into Excel so I can do my analysis on it.
Thanks.
Hi kamruddin,
In newer version of Excel there is a tool called Power Query that might be your answer. You would need to upgrade to 2010 or newer Excel and learn to use Power Query. If you upgrade your Excel and study a bit on PQ, we can help you build the M Code to get what I think you want.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Have you tried text to columns functionality?
Teach me Excel VBA
Using this macro then Text To Columns provides result as shown in attached file.
Please Login or Register to view this content.
Hi MarvinP,Originally Posted by MarvinP
I have Excel 2016. I was able to locate the PQ - I loaded the text file via the New Query --> From File ---> From Text option. The file is loaded and I am in the Query Editor. Not sure what I need to do next.
Great to hear,
Now, what lines of the text file do you want to save and what can be removed? Can you create an Excel workbook and show me what a few lines of data would look like for the final answer?
If you could attach the Excel workbook I'll work on the PQ answer to get the txt file to look like it. What part of the USA are you in?
Attached is the file. I have only shown the first two entries.
One caveat:
--- The headers in the file repeat after every table. You may have noticed that in the second table in the sample text file. Ideally, I would like to ignore these and only want the header once on the top of the sheet.
I live in Toronto - why do you ask?
OK kamruddin,
Live close to me, we could talk...
Power Query has a "Split" function where you can create columns based on number of characters in each row. I took your txt file and did successive "Split" based on number of characters and arrived at the attached.
To work on your own txt file do the following steps:
Open my workbook and click on Data Tab -> Show Queries
Right click on the Query that now shows on the right side of worksheet and then "Edit"
Click on the Gear icon to the right of "Source" and Browse to your text file and import.
Click on OK
Then click on the last (bottom) "Split Column" step.
This should load your text file and show what you see in the workbook. You could then Close and Load it to the current workbook.
This is just a first step in using PQ. If I was smarter (better with PQ) I could write a better script to deal with your specific data. I'd throw away unneeded rows and format columns. PQ is the right tool because you can load 500 test files and do the steps to each automatically.
PQ Delimit on No of Chrs.xlsx
https://www.bing.com/videos/search?q...F5&FORM=VRDGAR
MarvinP,
This is quite helpful - I will have to play around with this PQ function to get it to 100%. I appreciate your help.
Logit - thanks for your detailed code. I appreciate your help.
Well I took a stab at it. Change to the path to your file. I grabbed the main data I think?I didn't mess with the headers.
Please Login or Register to view this content.
Last edited by mike7952; 04-23-2017 at 02:53 AM.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
You are welcome. Best wishes.
I have data in Notepad that looks like in pages when converted to Excel. I've tried to format it in columns. Please help.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks