Solved

daily auto import sales csv file

Posted on 2014-09-18
17
460 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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