Tricky Access VBA Code - read from .txt file into table

Posted on 2008-10-02
Last Modified: 2013-11-27
How would you proceed with the attached file?  Here´s the situation/problem:

SAP system is throwing lots of "unusable" txt files (the best one I could get is the attached one).  Right now I export into excel and then manually clean-up the mess (lately with help of some macros), but this particular export is longer than the 65.000 lines that Excel allows.  It will always look like the attached sample.  Although the txt file is around 11MB (thousands of lines like the ones you have on the sample).

I have set the length and datatypes as they look like in the real file (ex.: 1111111111 is a nummeric value that represents the Order number, it is always nummerical and has 10 digits).  Here the fields that I need (read out of this txt file and imported into Access):

1111111111    > 10 digits, nummerical is Order Number
9999999999    > 10 digits, nummerical is Vendor Number
Some Companyname  > varying lengths, alpha-nummerical is Company Name of Vendor
27-05-2008  > Datefield of Purchase Order
00010  > this is a annoying part of this report it is the position number of the ordered item, i don´t know if this should go into the AccessDB
C1C1111111111  > 13 positions, alpha-nummerical value is Partnumber that has been ordered.
Part120-Sparexxxxx30  > varying lengths,  alpha-nummerical is the Partname

The next line has different elements that I need:
5 PC  > this is under the Partnumber represents the total quantity that has been ordered, on the same line the NetPrice should be kept and finally the "per Unit" value > 1 PC in the sample
Still to be delivered
Still to be invoiced
These two lines belong to the item above and must be imported, too along with the US$ value.

Everything else must go (all other info, % signs, lines, etc.)

The job could look something like this (in VBA Code? or should I use some Regular Expression code to clean-up first? Or should VBA code do this "job"?):

1. Open .txt file at fixed location (C:\sampledata.txt)
2. Read line by line > cleanup
3. save with the data that should be kept
4. Import in a Pivotlike table like this:

Order table:

Orderdate (27-05-2008 in example)
OrderNumber  (1111111111 in example)
VendorID > links to Vendorstable that has Vendorname and Vendornumber
NetValue in US$

Maybe a second table OrderStatus

StatusID = autonumber
OrderID = number linked to first table

Maybe this second table isn´t a great idea of database design, but I finally need to query and see what has been delivered and what is outstanding or else not invoiced.

What is difficult is that only the first line has the vendor data in it and Ordernumber (1111111111), every other line item needs to be related to this ordernumber.  Also, every order could have one to n-number of items.

Question by:MasterandCommander
  • 2
  • 2
  • 2
LVL 18

Accepted Solution

jmoss111 earned 500 total points
ID: 22624624
hi MasterAndCommander,

How I would tackle this is to purchase a copy of Monarch from DataWatch. The reason that I say this is if you have "lots" of unusable text files then you'll be writing code for each different style report file that you want to extract data from. With Monarch you can build a model and be extracting data quickly. Monarch can output to many file formats and you can automate it from Access. I have no interest in, nor do I know anyone  that works at DataWatch or has any interest in DataWatch. It's a good product for this type of task that doesn't have too steep of a learning curve. The purchase price would be recovered quicky when you consider reinventing the wheel every time versus creating a resuable model in a few minutes.


Author Comment

ID: 22625571
Thank you jmoss111.  It looks like a fantastic product....right there it just gave me another idea for another IT problem that I have...but this report is the only one that I have to manage and get right, so ONE solution in vba code or .net code would keep me happy for a while.  It is correct that I have lots of unusable text files, but the others are small enough for manual cleaning.  This last one is a "monster"... boss isn´t very keen on spending more on IT guys like me....  :-(      

So, I look forward to at least an approach on how to tackle this....the "Salami"-way (slice by slice).

Thanks for your quick reply and valuable contribution.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22644610

Just my 2c,

Jim is correct, Monarch is a great product , with a great track record.
It has been around since the days of DOS,  Circa 1986.

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 18

Expert Comment

ID: 22644952
Hi Jeff,

I think that people look at the price of a product but then don't look at what they will save in the amount of time that they will save by using a solution like Monarch or other similar products.

MasterAndCommander could easily save the purchase price on the first conversion.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22646152
One of the things I have learned form LSM is that you never want to loose a $5,000 project because you don't want to spend $500 for a utility.


Author Closing Comment

ID: 31502319
Thanks jmoss111.  I will give the points and close this down.  It wasn´t the solution I was looking for and my Boss isn´t going to buy the software - our IT department is going through severe budget cuts....almost bleeding....(eventhough I will continue to bugger him).....but it is a good solution, nevertheless.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now