troubleshooting Question
Text File Manipulation and Excel Power Pivot
asked on
I use text files as source data to feed Access tables due to 2GB size limitation of MS Access. I am currently using two text files that are merged daily after the one is updated, one of the text files is 2016-2017.txt historical data merged with daily updated 2018.txt file. The merged file size is 1.24 GB and I can work just fine with it without any problems.
Lately I have decided to use a bigger size text file for a larger historical analysis like going back to 2012. I compiled text files into one 2012-2017.txt file using Windows commands on C:\ prompt and added my daily updated 2018.txt file into this history so I could work with one historical data set all the way back to the year 2012. This is the method that I use daily for my current daily updates anyway without any issues. The expanded file size is 3.45 GB and when opened in Excel Power Pivot it displays 90 fields. My goal is after loading the data model in Excel power pivot cerate various pivot tables for historical data analysis.
The problem is that when I merge daily refreshed 2018.txt file into the historical file then connect to the resulting merged file via Excel Power Pivot (Access fails to open this file due to size but power pivot works ok along with Power BI) and insert a pivot table what I see is that all the years' total sales dollars from 2012 to 2017 get displayed correctly but 2018 is incomplete by a huge margin. I tried to load and re-load several times in different Excel files and I tried to merge history (2012-2017.txt) into the current year (2018.txt) the end result is that whatever the merged text file is it shows incomplete in Excel power pivot.
I know that there is no file size limitation in text files and the only limit is the size of the HDD so I just can't find any reason why this is the case. When I created my text files I used comma delimiter and in the same fashion I load them into Excel Power Pivot data model.
Does anyone have any idea or experience with a problem similar to what I have just described here?
Thanks in advance
Lately I have decided to use a bigger size text file for a larger historical analysis like going back to 2012. I compiled text files into one 2012-2017.txt file using Windows commands on C:\ prompt and added my daily updated 2018.txt file into this history so I could work with one historical data set all the way back to the year 2012. This is the method that I use daily for my current daily updates anyway without any issues. The expanded file size is 3.45 GB and when opened in Excel Power Pivot it displays 90 fields. My goal is after loading the data model in Excel power pivot cerate various pivot tables for historical data analysis.
The problem is that when I merge daily refreshed 2018.txt file into the historical file then connect to the resulting merged file via Excel Power Pivot (Access fails to open this file due to size but power pivot works ok along with Power BI) and insert a pivot table what I see is that all the years' total sales dollars from 2012 to 2017 get displayed correctly but 2018 is incomplete by a huge margin. I tried to load and re-load several times in different Excel files and I tried to merge history (2012-2017.txt) into the current year (2018.txt) the end result is that whatever the merged text file is it shows incomplete in Excel power pivot.
I know that there is no file size limitation in text files and the only limit is the size of the HDD so I just can't find any reason why this is the case. When I created my text files I used comma delimiter and in the same fashion I load them into Excel Power Pivot data model.
Does anyone have any idea or experience with a problem similar to what I have just described here?
Thanks in advance
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.