?
Solved

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

Posted on 2014-08-20
20
Medium Priority
?
161 Views
Last Modified: 2014-08-26
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
0
Comment
Question by:robmarr700
  • 13
  • 7
20 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40275283
You can not highlight a txt file  you might want to do your text highlighting in a Word or Wordpad document.
0
 

Author Comment

by:robmarr700
ID: 40275674
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40276013
nothing attached.  please try again
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:robmarr700
ID: 40276344
Apologies I've changed my mind on now I want to approach this question. I am going to request to close the question.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40276353
How do you want to approach this question now?  What has changed?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40276422
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
 

Author Comment

by:robmarr700
ID: 40276468
The new question is independent from this one
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40276513
ok.

Please answer my questions.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40276687
The new question is independent from this one
How are these two questions different from one another?
0
 

Author Comment

by:robmarr700
ID: 40276823
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40276831
please answer my questions
I am working on this, too
0
 

Author Comment

by:robmarr700
ID: 40276874
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40277142
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40278593
@rob

What do you think?
0
 

Author Comment

by:robmarr700
ID: 40278982
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40279094
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40279797
I think I have parsed the correct number of items (520).  I'm now validating the numbers.
Q-28501522-Proof.xls
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40280137
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40280844
Here is the solution workbook, including the VBA code.  I will test this in your other question as well.
Q-28501522.xls
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question