Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Large Text Database to Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Large Text Database to Excel
On May 6, 10:24*am, cardan wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Large Text Database to Excel
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) "cardan" wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Large Text Database to Excel | Excel Programming | |||
Linking Excel to a text file database | Excel Programming | |||
Need formula in excel for filtering large database | Excel Worksheet Functions | |||
Large Database Problem-linking worksheets and printing | Excel Discussion (Misc queries) | |||
Linking Large Access Table into Excel | Links and Linking in Excel |