?
Solved

daily auto import sales csv file

Posted on 2014-09-18
17
Medium Priority
?
511 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 46

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:JP
ID: 40332409
Am I correct that the daily sheets will only be 01 - 31?
0
Back Up Your Microsoft Windows Server®

Back up 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.

 

Author Comment

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

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 46

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 46

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 46

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 46

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 46

Accepted Solution

by:
aikimark earned 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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