Solved

daily auto import sales csv file

Posted on 2014-09-18
17
448 Views
Last Modified: 2014-10-02
Hi,

I am trying to create a sales dashboard that I can integrate with SharePoint, each morning I get a csv file (the CSV export has a different name each day (the sales data date)) with the previous days sales figures saved to a network folder, is it possible to have an excel model look for a file automatically and import the data into a different column each day?

I have attached two example csv sales data and the excel model I have started.

Regards,
Sales-model.xlsx
0
Comment
Question by:hellblazeruk
  • 7
  • 7
17 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40332389
It looks like you are going to import into a different worksheet each day and sum the worksheets' data on the Sum worksheet.  Is this correct?
0
 

Author Comment

by:hellblazeruk
ID: 40332401
hi,

That was my first idea but happy to take recommendations.

Regards,
0
 
LVL 14

Expert Comment

by:peetjh
ID: 40332409
Am I correct that the daily sheets will only be 01 - 31?
0
 

Author Comment

by:hellblazeruk
ID: 40332432
yes, one for each day.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40332440
Your design should derive from your data needs.
* Is this just for reporting or data archival?
* Do you need to retain all the detailed data or consolidate it at the end of each month?
* What should happen when a product is added or deleted?
* Do you have to use Excel or do you have Access? (This looks a database problem/solution to me)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40332443
* is the day of the month your only concern or do you need to track sales figures by week, month, quarter, or year?
0
 

Author Comment

by:hellblazeruk
ID: 40332624
* Is this just for reporting or data archival?
-Just for reporting

* Do you need to retain all the detailed data or consolidate it at the end of each month?
-Consolidate

* What should happen when a product is added or deleted?
-New line to be added?


* Do you have to use Excel or do you have Access? (This looks a database problem/solution to me)
-Happy with either excel or access

* is the day of the month your only concern or do you need to track sales figures by week, month, quarter, or year?
-longer term view is the better solution
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
ID: 40342793
I think you should be able to run your daily imports and even pick up any missed days.  Reporting should be fairly straightforward.
0
 

Author Comment

by:hellblazeruk
ID: 40351612
Can anyone recommend where I should start with this, any websites to read etc?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40352118
What are the file names?
Where are the files? (network paths, either mapped or UNC)
Please post a sample CSV file.
0
 

Author Comment

by:hellblazeruk
ID: 40353312
The CSV export has a different name each day (the sales date)
the csv files are saved to d:\export each day around 5am
01090914.csv
20140915.csv
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40353673
is "01090914" supposed to be a date?
0
 

Author Comment

by:hellblazeruk
ID: 40353984
it should be 20140914
20140914.csv
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40354362
take a look at this database.  There is a routine in module1 to handle the importing.  The current action is to rename the .csv files as .txt files.  You may alter the code to delete or move the csv files.

I've split the data into separate product and sales tables.

Note: There are a couple of Sales report queries to give you an idea how these tables can be used.  One is a fixed number of days (30) prior to the current date.  The second one prompts you for the prior number of days.
Q-28521077.mdb
0
 

Author Comment

by:hellblazeruk
ID: 40356731
Hi Aikimark,

Excellent, just what I need to make a start,

Thank you for the help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now