Inventory aggregation with csv files

We have a drop-ship company and receive 15 inventory updates daily from our drop-ship vendors. Currently we are manually editing and uploading each and every one of these csv files that we receive to our inventory management system. We would like a solution that provides aggregation of the csvs and automatically processes these csvs with the needed formating to upload to our inventory management system. Hopefully this makes sense.

Any ideas?

Sincerely,

Completely Clueless
tbawarehouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
Much of this depends on your inventory management system. If it is a common database, and you have access to it, I would probably set up a web site, ask the vendors to upload their files. I would parse them with php and then directly insert the new records into the existing database.

php has builtin str_getcsv to parse the csv and has adapters to all the common databases.

Of course, there are an infinite number of other languages that could do the same thing.
tbawarehouseAuthor Commented:
Sounds great but I worry if they have to upload to a website I may get some kick back. Seeing as now they are just ftping the file which is an automated process. Would this be as well?
mankowitzCommented:
Sure. Or you can use email. In order to do that you would periodically check for new items in the FTP directory or inbox. You'd have to have access to task scheduler (windowS) or cron (unix) in order to do that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
This very much depends on input and output format, of course. Processing CSVs isn't difficult, in Windows you would e.g. use PowerShell or VBS with an FileSystemWatcher or WMI event monitoring script to kick-start the script, or start it on a regular base as stated abvove.
Do you want to insert that into a database (which DBMS?), or construct a web page (HTML table)?
AlexPaceCommented:
I solved a very similar problem by using MS SQL Server Integration Services.  You can make an SSIS task that imports directly from the CSV files into a database table.  In my situation there was a requirement to massage the data by combining and re-arranging some columns so my SSIS task first imported the CSVs into a raw format and then it launched a stored procedure that reformatted and inserted into a second table where it was actually used.  

At first I used a SQL Server Job to run it every hour but then they wanted it to run ASAP when a file was uploaded.  To accomplish this I switched the FTP software to Robo-FTP Server and associated a script with the UploadComplete event.  This script connected to the database and executed an SQL command that ran the job to import the newly uploaded CSV file so the files were processed instantly as soon as they were uploaded.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Could you tell why you chose that two answers as solution?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.