Solved

daily auto import sales csv file

Posted on 2014-09-18
17
479 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
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.

 

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
 
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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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