Inventory and Product updates Update Database

[ankrups]

Hi,

I need to build the database to update inventory / product info for my online stores from the vendor sources that I am receiving the data feed in csv links.

I am currently receiving feeds in CSV links. What I want is to update the links on daily basis with some calculated fields in each feeds depending on vendor format then it generates 3-4 pre defined feeds to update into the my online system by downloading them into csv files.

I hope I have explained in brief the concept that I need to build. If any one can help me then it will be greatly appreciated.

Currently I am using excel with 250 MB file which is taking long time to amend from start to finish.

Thank you in advance.

-Ankit

 

[CPGDeveloper]

If I am understanding correctly you have linked tables in MS Access that are linked to CSV Files? And you need to update those links as you receive new/additional csv files?

 

[ankrups]

No, Let me explain. Currently I am receiving CSV links and csv files from my 12 different suppliers. What I want is to create database where mix of links connect to tables in access and csv files.

Then it converts the data into pre defined format with some calculations in order to generate one single file for my other softwares and my perusual.

Let me know if you need any further clarifications.

 

[Micron]

It's common to link csv files in Access. You have the option to import, append or create linked tables. IIRC, those tables are not editable, so one method is to have Access create the linked tables then you use queries to append/update tables that you create with the right properties for each field. That way, data in the linked table that is text data type (regardless of what it looks like) will end up being the correct data type in the db tables you create. Not sure if that helps as I don't understand some of the terms of your posts, like "update the links" for example. To me that means the file path or url to the data has changed. You can maintain a table of links if that's the case, and update those. That may be an unnecessary step though. Might be better if you described what happens as a series of steps.

 

[ankrups]

Thank you for your reply.

Ok let me show explain. Basically once we can make standardised format done, Step 3 to 5 is just matter of formatting the data into the format that other systems needs it.

Step1: Update / append all links from CSV file from different suppliers in separate table in MS access. Now each of the Supplier follows different pricing structure and % margins so when we link this into Standardized format, it needs to create selling price as per pricing structure list which is defined.

(Crucial Step)
Step 2: Update / amend all data from CSV links into standardised format of the table from different suppliers and apply the formula as defined in Standardised Table date. In this table, I want new products to be added as per supplier files and run the formula as setup while building the table.

Step 3: Create Inventory File as per the set up done.

Step 4: Create Product Feed as per the set up done.

Step 5: Create New Product Data feed as per set up done.

There are some other format of feed required but we can discuss that later stage.

If we can achieve Step1 and Step 2 then rest is easy to manage I believe.

I hope I have explain bit better this time. Let me know if you still have any questions.

Thank you so much for your help.