Macro to full parse text file into use-able data set?

Attached are three original text file documents.

They display stock usage for products during a four month period.

I basically need a macro to split and modify the data from the original text for into relevant columns so it can be worked with in excel.

This is a macro that will be run repeatedly for different supplier stock files. For this reason I have attached three original text files. If the macro works successfully for all three we can assume it works correctly.

I have highlighted the data which needs to be kept on the attached sheet (bosch example). Information not relating to these parameters can be removed, e.g. "PRODUCT GROUP BOSCH", THS DIRECTSTOCK USAGE REPORT by Product Group      Produced On: 20/ 8/14                                                          Page     3"

note the product descriptions often push onto two lines. These need to be consolidate onto the one line whilst keeping the relevant data aligned.

Any questions just ask

Rob
crcstocklevels.csv
dormerstocklevels.csv
stocklevels.csv
bosch-example.txt
robmarr700Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
You can not highlight a txt file  you might want to do your text highlighting in a Word or Wordpad document.
0
robmarr700Author Commented:
Apologies please find attached. Please note The headings are only required once from the column headings. After this they can be discounted it is the corresponding data that is important.

Rob
0
aikimarkCommented:
nothing attached.  please try again
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

robmarr700Author Commented:
Apologies I've changed my mind on now I want to approach this question. I am going to request to close the question.
0
aikimarkCommented:
How do you want to approach this question now?  What has changed?
0
aikimarkCommented:
1. Would you like to pursue both solution paths simultaneously?
2. Does the worksheet you posted in that question reflect the translation of the data that you have not posted in this thread?
3. How is the data getting into the workbook?
4. You should include a larger slice of the data in that other question to show the experts what they are up against.  There are at least three different data line (set) formats.
5. What should happen when there is no Supplier data in the header?
0
robmarr700Author Commented:
The new question is independent from this one
0
aikimarkCommented:
ok.

Please answer my questions.
0
aikimarkCommented:
The new question is independent from this one
How are these two questions different from one another?
0
robmarr700Author Commented:
The data set on the original question is different, e.g. each sheet contains stock levels for only one Supplier.

The new question contains stock level data for all suppliers making it slightly different. I have an expert working on the new question now. Feel free to jump in if any problems arise.

Rob
0
aikimarkCommented:
please answer my questions
I am working on this, too
0
robmarr700Author Commented:
1. Would you like to pursue both solution paths simultaneously?
2. Does the worksheet you posted in that question reflect the translation of the data that you have not posted in this thread?
3. How is the data getting into the workbook?
4. You should include a larger slice of the data in that other question to show the experts what they are up against.  There are at least three different data line (set) formats.
5. What should happen when there is no Supplier data in the header?



3. You can open the original txt. file as a csv in excel and work with it from there.
4. The attached txt. file in the new question shows the full sample of data year to date. It will update daily.
5. If there is no supplier data in the header the supplier field can be left blank.
0
aikimarkCommented:
please inspect the attached workbook.  This is the first pass at parsing.  I've used the Bosch-example.txt file as input.

I'm trying to determine if I've captured all the data.  I've got 509 items and I think there might be 515 items.  Not sure of the discrepancy.  If you spot something, please let me know what you see.
Q-28501522-Proof.xls
0
aikimarkCommented:
@rob

What do you think?
0
robmarr700Author Commented:
Hi

Yes that has worked.

The only thing to note is that I wanted to keep the column headings. It was also my intention for the macro to work on all of my stock data with multiple suppliers (hence the new question) with the different source data. This way I could filter the supplier's data I need from the same sheet. Rather than having a separate sheet for each supplier stock file.

Rob
0
aikimarkCommented:
Headers come later.  I purposefully populated the worksheet starting on the second row to make room for the headings.

Multiple source files are secondary to the parsing problem.  I assume that they will all be in the same folder with similar names or extensions.

How many items do you expect when the example file is parsed properly?
0
aikimarkCommented:
I think I have parsed the correct number of items (520).  I'm now validating the numbers.
Q-28501522-Proof.xls
0
aikimarkCommented:
I think this is the data configuration you're seeking.  I've got the number of lines and the numbers check out.
Q-28501522-Proof.xls
0
aikimarkCommented:
Here is the solution workbook, including the VBA code.  I will test this in your other question as well.
Q-28501522.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.