We help IT Professionals succeed at work.
Get Started
Text File Manipulation and Excel Power Pivot
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
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE