Hello - I am attempting to build a long term relationship with a developer or database administrator, and the initial task is to design a process for downloading and filtering a large file on a routine basis (i.e., 2x per week), most likely. This initial task will help me become familiar with you and your skill set so that you can hopefully continue to help with long term needs with our company's data.
The file size is 4GB, about 14 million rows, and about 45 columns. It is originally in a .dat format. The file is called "Stock V2 Full."
It is important to note that an ISBN is an internationally recognized unique identifier for a book. There are two kinds of ISBNs for each book, one is a 13 digit ISBN (ISBN-13) and another is a 10 digit ISBN (ISBN-10). I tend to focus on using the 10 digit ISBN.
The goal is for me to use the incoming source file (i.e., StockV2 full) to update our inventory so that (1) the price columns are updated, and (2) the items that are no longer in stock are known via a separate report. Using the reports described below, I will be updating prices and deleting inventory manually (weekly or daily)
The attached documentation has been created by the 3rd party that provides the data to me and describes exactly how the process should work, along with data columns locations and formats. It also outlines an option for either using the Main file (titled "Stock V2 Full"), or for using the weekly or daily Delta files to update the Main file. I don't mind which method is used, but I would encourage the design of the simpler, less problematic method.
The data file needs to be retrieved (via FTP), and then filtered to provide the following reports, which then will need to ultimately be imported into Microsoft Access 2010 (i.e., Access is what I currently use to manage my inventory).
Just ask, and I can send you a small sample of the Stock V2 file.
We can use whichever Database system or file splitting tools you recommend, but please also keep in mind that we are hoping to build on this project to add a more robust database management process in the long run. I do have MySQL and and MySQL workbench installed on a desktop computer, but I have not been using it yet, and I am not very experienced with SQL.
Below are the reports that need to be filtered from the file, and pulled into Microsoft Access.
Note that I will need to have the ability to manually update a .txt file (i.e., IngramActiveInventory.txt) that contains up to 100,000 ISBNS with the active inventory. The resulting reports outlined below will then be related to that Active Inventory File.
Report 1 - Out of Stock (and Deletes) Report. This report simply shows items that, compared to the Active Inventory File are no longer available in the Stock V2 Full File.
a. filter = 'La Vergne, TN On-Hand Quantity' = "00000000" (see full definition of 'out of stock' on page 5)
b. Join to 'IngramActiveInventory.txt' table (mentioned above)
b2. Location of Active Inventory Table: C:\Users\***\***\***\Access Database Test\Ingram Access Data)
c. Select these 2 Columns
c2. La Vergne, TN On-Hand Quantity
d. Append to the report any ISBNs where the Join from IngramActiveInventory.txt does not produce a match (i.e. where the ISBN-10 located in the Active Inventory no longer exists in the StockV2 file). This is simply the list of books in active inventory file that have been removed altogether from the stockV2 file. This list of "deletes" can also be placed in an entirely separate report, if that makes it easier.
d. Place resulting file showing all deleted ISBNs and all ISBNs that have a quantity of 0 into Microsoft Access Table, or txt file that can be imported into Microsoft Access
e. Final location of report will ultimately be C:\Users\***\***\***\Access Database Test\Ingram Access Data\1002_IngramOutofStockReport.mdb
Report 2 - Active Inventory Report - This Report simply shows selected columns from the Stock V2 File, as related to the smaller list of ISBNs located in the Active Inventory Table.
a. Filter Stock V2 file where 'La Vergne, TN On-Hand Quantity' > "00000000"
b. Select these Columns (see positions of the columns in attached specs, page 11-13)
b2. La Vergne, TN On-Hand Quantity
b3. Ft. Wayne, IN On-Hand Quantity
b5. Discount Level
b6. Publisher Status Code
b7. La Vergne, TN Stock Flag
b8. Publication Date
b8. On Sale Date
b9. Returnable Indicator
b10. Media Mail Indicator
b11. Product Type
b13. Ingram Publisher Number
b14. Product Classification Type
c. Final location of report will ultimately be C:\Users\***\***\***\Access Database Test\Ingram Access Data\1003_IngramActivesReport.mdb
d. If possible, it would also be helpful to set up the option for me to simple queries of the full Stock V2 report (or the list of columns listed above) within Access or MySQL, though splitting may be necessary to do so, in light of the size constraints.