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:
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.