troubleshooting Question

How can I parse this lot into a standard format? Variable CSV files into fixed .

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessProgrammingMicrosoft Excel
20 Comments1 Solution806 ViewsLast Modified:
originally here

I receive multiple csv files by email for each of the following csv examples and many more besides.

I need some way of collecting them all in to one table, that means columns have to be sorted into the right order and have gap columns where data is not present.
Not all data is required, but the following would be useful if present.
BANK NAME, DATE, ACCOUNT NAME, ACCCOUNT NUMBER, REF/TYPE, DESCRIPTION, AMOUNT,BALANCE

Typically the source name (i.e. the bank) or credit card) will be missing)
This can be viewed as a "standard", but subject to change.


MBNA CEDIT CARD
Transaction Date,MCC,Description,Amount
10/07/2010,    ,"PAYMENT DIRECT DEBIT - THANK YOU          ",25.82
12/07/2010,    ,"INTEREST CHARGED                                ",-22.87


PAYPAL
Date, Time, Time Zone, Name, Type, Status, Currency, Gross, Fee, Net, From Email Address, To Email Address, Transaction ID, Counterparty Status, Postal Address, Address Status, Item Title, Item ID, Postage and Packing, Insurance Amount, VAT, Option 1 Name, Option 1 Value, Option 2 Name, Option 2 Value, Auction Site, Buyer ID, Item URL, Closing Date, Escrow ID, Invoice ID, Reference Txn ID, Invoice Number, Custom Number, Receipt ID, Contact Phone Number, 

AMEX 
No headings
Example transaction:
03/11/2010,"Reference: AT104560035000010045213"," 244.55","PAYPAL *SPSERVICES SG Singapore SNG 7894","PAYPAL *SPSERVICES SG Singapore SNG 7894 Process Date 04/11/2010 0044568001                             PAYPAL *SPSERVICES SG Singapore SNG 7894",

ALLIANCE & LEICESTER CREDIT CARD
Transaction Date,MCC,Description,Amount
02/09/2010,    ,"PAYMENT DIRECT DEBIT - THANK YOU          ",122.16
03/09/2010,    ,"INTEREST CHARGED                                ",-116.95


RBS
Date, Type, Description, Value, Balance, Account Name, Account Number
NO TRANSACTIONS SINCE 31/3/2010
BALANCE IS NIL
AVAILABILITY IS NIL


RBS
Date, Type, Description, Value, Balance, Account Name, Account Number
08/01/2010,D/D,"'HMRC - NI DD",-12.00,20.59,"'Rev GEORGE Freeson","'262016-12107845",

BARCLAYS
Number,Date,Account,Amount,Subcategory,Memo
 ,14/03/2011,20-66-88 20637894,3000,DIRECTDEP,Mr Customer                    BGC
 ,07/03/2011,20-66-88 20637894,2400,DIRECTDEP,Mr Customer                    BGC
 ,06/03/2011,20-66-88 20627089,-1195,FT ,204455 34563456       HMRC VAT           FT 

Sometimes the headings are just missing.

I need everything sorted in exactly the order sent, BUT in ascending date order. What that means if they arrive newest first, I want the order exactly reversed: this means items on the same date retain their neighbours, but in reverse. Without this balance business controls do not work.

Time period is usually 12 months, so can be 12 x Barclays csv files and same for all the others - which can be concatenated on receipt in a "hot" folder.

That's just the start of trying to get some order.
It is tempting to adopt say the standard used by the QIF type of layout (the open standard one) rather than invent my own.

Having said that, how hard can it be with a bit of help from say vba or msquery of powerpivot?

For 2011 Mac, 2010 PC.
PC only solutions definitely considered.

Anthony
ASKER CERTIFIED SOLUTION
Anthony Mellor
Owner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros