Automated file acquisition, identification, extraction and transformation
Managing large amounts of third party client data may require sorting through files for patterns and extracting data to create a customized user interface for the third party client. One example method of operation may include examining file names for data files stored in a database, parsing specified names and specified dates from the file names, categorizing the data files according to the specified names and specified dates, tagging the data files, and transforming content of the data files into a customized data table format associated with known client requirements.
1. A method comprising:
examining file names for a plurality of data files stored in a database;
parsing specified names and specified dates from the file names;
identifying a defined pattern associated with a particular client and a particular type of data;
identifying a data pattern of each data file of the plurality of data files;
identifying data files, of the plurality of data files, associated with the particular client based on the parsed specified names and the parsed specified dates conforming to the defined pattern associated with the particular client and the particular type of data;
categorizing the plurality of data files according to the specified names and specified dates;
tagging the data files; and
transforming content of the data files into a customized data table format associated with requirements of the particular client.
2. The method of claim 1 , wherein transforming content of the data files into the customized data table format associated with the known client requirements comprises performing a bulk insert from the plurality of data files into the customized data table format.
3. The method of claim 2 , further comprising:
loading the data files into the database as a binary large object (BLOB).
4. The method of claim 1 , further comprising:
identifying a year and month from the data file names; and
logging a status of each of the data files.
5. The method of claim 3 , wherein tagging the data files comprises labeling the data files with one or more of a client identifier, a type identifier, year identifier and a month identifier.
6. The method of claim 5 , further comprising:
loading a column or row in a table with content of the tagged data files based on the data file labels.
7. The method of claim 6 , further comprising:
matching names of the data files to known patterns comprising a client category and a type category.
8. An apparatus comprising:
a processor configured to:
examine file names for a plurality of data files stored in a database;
parse specified names and specified dates from the file names;
identify a defined pattern associated with a particular client and a particular type of data;
identify a data pattern of each data file of the plurality of data files;
identify data files, of the plurality of data files, associated with the particular client based on the parsed specified names and the parsed specified dates conforming to the defined pattern associated with the particular client and the particular type of data;
categorize the data files according to the specified names and specified dates;
tag the data files; and
transform content of the data files into a customized data table format associated with requirements of the particular client.
9. The apparatus of claim 8 , wherein the processor transforms content of the data files into the customized data table format associated with the known client requirements comprises a bulk insert from the plurality of data files into the customized data table format.
10. The apparatus of claim 9 , wherein the processor is further configured to:
load the data files into the database as a binary large object (BLOB).
11. The apparatus of claim 8 , wherein the processor is further configured to:
identify a year and month from the data file names, and
log a status of each of the data files.
12. The apparatus of claim 11 , wherein the processor tags the data files via labels applied to the data files with one or more of a client identifier, a type identifier, year identifier and a month identifier.
13. The apparatus of claim 12 , wherein the processor loads a column or row in a table with content of the tagged data files based on the data file labels.
14. The apparatus of claim 13 , wherein the processor matches names of the data files to known patterns comprising a client category and a type category.
15. A non-transitory computer readable storage medium configured to store at least one instruction that when executed by a processor causes the processor to perform:
examining file names for a plurality of data files stored in a database;
parsing specified names and specified dates from the file names;
identifying a defined pattern associated with a particular client and a particular type of data;
identifying a data pattern of each data file of the plurality of data files;
identifying data files, of the plurality of data files, associated with the particular client based on the parsed specified names and the parsed specified dates conforming to the defined pattern associated with the particular client and the particular type of data;
categorizing the plurality of data files according to the specified names and specified dates;
tagging the data files; and
transforming content of the data files into a customized data table format associated with requirements of the particular client.
16. The non-transitory computer readable storage medium of claim 15 , wherein transforming content of the data files into the customized data table format associated with the known client requirements comprises performing a bulk insert from the plurality of data files into the customized data table format.
17. The non-transitory computer readable storage medium of claim 16 , wherein the processor is further configured to perform:
loading the data files into the database as a binary large object (BLOB).
18. The non-transitory computer readable storage medium of claim 15 , further configured to store at least one instructions that causes the processor to perform:
identifying a year and month from the data file names; and
logging a status of each of the data files.
19. The non-transitory computer readable storage medium of claim 18 , wherein tagging the data files comprises labeling the data files with one or more of a client identifier, a type identifier, year identifier and a month identifier.
20. The non-transitory computer readable storage medium of claim 19 , further configured to store at least one instructions that causes wherein the processor to perform:
loading a column or row in a table with content of the tagged data files based on the data file labels; and
matching names of the data files to known patterns comprising a client category and a type category.