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 CARDTransaction Date,MCC,Description,Amount10/07/2010, ,"PAYMENT DIRECT DEBIT - THANK YOU ",25.8212/07/2010, ,"INTEREST CHARGED ",-22.87PAYPALDate, 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 headingsExample 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 CARDTransaction Date,MCC,Description,Amount02/09/2010, ,"PAYMENT DIRECT DEBIT - THANK YOU ",122.1603/09/2010, ,"INTEREST CHARGED ",-116.95RBSDate, Type, Description, Value, Balance, Account Name, Account NumberNO TRANSACTIONS SINCE 31/3/2010BALANCE IS NILAVAILABILITY IS NILRBSDate, Type, Description, Value, Balance, Account Name, Account Number08/01/2010,D/D,"'HMRC - NI DD",-12.00,20.59,"'Rev GEORGE Freeson","'262016-12107845",BARCLAYSNumber,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
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.
You might be better off using something like .NET to handle this, which makes working with oddly formatted text files like this easier.