Linking Large Text Database to Excel

[cardan]

PROBLEM
I am trying to find a way to decrease the file size of a document
where I currently import data from text documents (which I have to
convert to Excel format through a Tab Delimited conversion).
Currently the file is around 20M and will most likely be shared and is
a process that will be repeated every 3 months. I am totally ignorant
when it come to working outside Excel (and my Macro skills are sub
par

Below is a background of the issue and with my current setup to give
context to my problem.

BACKGROUND
The information comes from Bank Call Reports which I can download bulk
data from the FDIC website for free. Every report from every bank
uses this template. Every bank is given a unique identifier number
and every reported dollar number is assigned a code. For Example
Total Assets is assigned the Identifier RCON2170 (RCON for short),
for all banks. There are over 7,500 banks that report quarterly and
well over 1,000 RCON codes.
When I download the bulk data, it downloads in a compressed folder
which I unzip. The data is in approximately 40 different text files
broken apart by the section (Schedule) in the report (ie Schedule RC
is the Balance Sheet section)

CURRENT SETUP
Right now I import the sections I need (about 6). Each sheet lists
all 7,500 banks by ID number and average 100 RCON numbers per tab
(750,000 cells per tab). I then link up these pages to a template
that searches the appropriate tab through and INDEX-MATCH formula
which finds the number based on the RCON number and the banks ID. The
template is set up so numerous banks can be compared side by side just
by entering the banks unique identifier.

ISSUE
My problem is that the files are creeping up towards 20M. Is there a
way to have my INDEX MATCH formula (or something similar) search the
text files and then return the right number based on the RCON number
and banks unique ID number while limiting my file size? Any help
would be tremendously helpful. Thank you for your time.

 

[cardan]

PROBLEM
I am trying to find a way to decrease the file size of a document
where I currently import data from text documents (which I have to
convert to Excel format through a Tab Delimited conversion).
Currently the file is around 20M and will most likely be shared and is
a process that will be repeated every 3 months.  I am totally ignorant
when it come to working outside Excel (and my Macro skills are sub
par

Below is a background of the issue and with my current setup to give
context to my problem.

BACKGROUND
The information comes from Bank Call Reports which I can download bulk
data from the FDIC website for free.  Every report from every bank
uses this template.  Every bank is given a unique identifier number
and every reported dollar number is assigned a code.  For Example
Total Assets is assigned the Identifier RCON2170 (RCON for short),
for all banks.  There are over 7,500 banks that report quarterly and
well over 1,000 RCON codes.
When I download the bulk data, it downloads in a compressed folder
which I unzip.  The data is in approximately 40 different text files
broken apart by the section (Schedule)  in the report (ie Schedule RC
is the Balance Sheet section)

CURRENT SETUP
Right now I import the sections I need (about 6).  Each sheet lists
all 7,500 banks by ID number and average 100 RCON numbers per tab
(750,000 cells per tab).  I then link up these pages to a template
that searches the appropriate tab through and INDEX-MATCH formula
which finds the number based on the RCON number and the banks ID.  The
template is set up so numerous banks can be compared side by side just
by entering the banks unique identifier.

ISSUE
My problem is that the files are creeping up towards 20M. Is there a
way to have my INDEX MATCH formula (or something similar) search the
text files and then return the right number based on the RCON number
and banks unique ID number while limiting my file size?  Any help
would be tremendously helpful.  Thank you for your time.

PLEASE DISREGARD THIS LAST POST. IT IS A REPEAT OF MY EARLIER ISSUE.
MY GOOGLE WAS SHOWING THE ORIGINAL MESSAGE NEVER UPLOADED SO I
REPEATED THE POST ONLY TO FIND IT TOOK. SORRY FOR DOUBLE POST.

 

[David]

You will need to write code to 'parse' the required text from the text files
into excel.
The detail of that code will depend on the exact nature of the text
structure (you will be trying to dentify patterns in the text structure that
correlate with the text you wish to extract). You could do a lot worse than
taking a look at John Walkenbach's 'Power programing with VBA' under
'manipulating text files' for useful examples. (google this and see what
comes up)