Hello all,
Is the following possible with standard Excel?
I have a CSV file with data that is refreshed every night. In a separate excel file i would like to import the data and make a pivot table from it.
The amount of rows in the csv file will change after each refresh (less or more rows). How to manage this?
Can the data in the pivot be updated automatically or do i need to add a button to refresh?
Thanks in advance!
Yes it can be done, although probably using VBA, you automate the steps of importing csv, and refreshing pivot. This can either be done on a button or as part of opening the excel workbook
Is the CSV file always the same name?
Yes, the program will overwrite the existing csv with a new one.Originally Posted by davsth
I will be able to create a button with a refresh macro, however it would be nice to make it refresh automatically when opened.
Is it hard to accomplish this?
As you are using the 2016 version of Excel you might consider using the Get & Transform feature as described in the linked Highline College video (24:15 mark). This part of the video is specifically talking about importing text files, however I believe that the method should work for CSV files.
https://www.youtube.com/watch?v=ohGFPF12Qwc&t=1275s
Best of luck.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Yep, using PowerQuery to load data and perform any necessary transformation. Then load to data model.
Then use the data model as source for your pivot table.
If that's not an option (i.e. you need to make it compatible with older version)... you could use Pivot Wizard to create pivot table directly from text file.
You can see small how-to I wrote in link below.
https://chandoo.org/forum/threads/cr...2/#post-173495
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
The pivot table wizard works fine for me. I already did some transformation in Qlikview, so i won't be needing to do that in Excel.
Nice about the wizard is that it reloads the data when i open the file.
Thank you all for your help and cooporation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks