Solved

daily auto import sales csv file

Posted on 2014-09-18
17
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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