Whats the best method

  • Thread starter Thread starter PG
  • Start date Start date
P

PG

Hi,

I have a MASSIVE delimited text document that basically
contains the Buys and Sells of stocks in an account.

The idea is that the buys equal the sells in quantity and
average price.

Unfortunately the list is too long for acces to load as
linked and run queries off of.

So I figure I need to read the list using a macro to do a
running total.

What bits of code do I need for this?
 
Ok I know how to read the text file

Sub Test()
Dim AccountTypeDescription, TradeDate, SettlementDate,
ExecutionTime, TradeNumber, CUSIPSymbol, ISIN,
ShortDescription, BuySellCode, Quantity, Price,
PrincipalAmount, CommissionGrossCalculated,
OtherCommission, NetAmount, CurrencyCode As String

Open "TESTFILE.txt" For Input As #1 ' Open file.
For x = 1 To 3

Input #1, AccountTypeDescription, TradeDate,
SettlementDate, ExecutionTime, TradeNumber, CUSIPSymbol,
ISIN, ShortDescription, BuySellCode, Quantity, Price,
PrincipalAmount, CommissionGrossCalculated,
OtherCommission, NetAmount, CurrencyCode ' Read line into
variable.

Debug.Print AccountTypeDescription, TradeDate,
SettlementDate, ExecutionTime, TradeNumber, CUSIPSymbol,
ISIN, ShortDescription, BuySellCode, Quantity, Price,
PrincipalAmount, CommissionGrossCalculated,
OtherCommission, NetAmount, CurrencyCode ' Print to the
Immediate window.

Next
'Loop
Close #1 ' Close file.
End Sub

But how can I most effectively structure a record set
That will group and structure the data?
 
Its tough to answer without seeing what the data looks like. In general
what you may want to do is to parse each "record" of the text file into
another database. The "record" may be a single line or multiple lines of
the text file, you will have to determine that from the data structure.
Create your own database one record (with all of its fields) at a time. Not
an easy task, but it can be done.
 
Back
Top