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