The Poojer Posted October 28, 2015 Share Posted October 28, 2015 i have a csv file that is over 2GB and probably 280,000+ rows. Excel keeps crapping out, access won't bring it in...anyone know a good way to split the file in 2 then open it? Don't know linux or anything like that, simply looking for something in the windows world to help me get this damned file open. Link to comment Share on other sites More sharing options...
Jobu Posted October 28, 2015 Share Posted October 28, 2015 i have a csv file that is over 2GB and probably 280,000+ rows. Excel keeps crapping out, access won't bring it in...anyone know a good way to split the file in 2 then open it? Don't know linux or anything like that, simply looking for something in the windows world to help me get this damned file open. Sure. EditPlus will open a file that big. Have a hard time believing Access won't consume it. Sure you don't have a formatting problem within the CSV? I have done larger than that. What version are you using? Alternatively you can just do it from the command line using a batch file. Here is a link to stackoverflow, there is a batch file in the first response that works pretty well: http://stackoverflow.com/questions/25249516/split-text-file-into-smaller-multiple-text-file-using-command-line Link to comment Share on other sites More sharing options...
The Poojer Posted October 28, 2015 Author Share Posted October 28, 2015 i have office 2013, says there is a 2GB limit. i will look at the link you provided. I have a sql guy here, he will be my next step. Link to comment Share on other sites More sharing options...
Jobu Posted October 28, 2015 Share Posted October 28, 2015 i have office 2013, says there is a 2GB limit. i will look at the link you provided. I have a sql guy here, he will be my next step. Yeah you are right there is a size limit, I have definitely done more rows than that so my file must not have been as large. Your SQL guy should be able to do a bulk insert into a sql table for you you can then attach that table in access. Link to comment Share on other sites More sharing options...
The Poojer Posted October 28, 2015 Author Share Posted October 28, 2015 you'd think this far into the game, ms would have come up up with an addon in excel that is able to split a large file into manageable pieces, especially a delimited file with headers, should be easy to identify row breaks, oh well.....thanks for your help though! Yeah you are right there is a size limit, I have definitely done more rows than that so my file must not have been as large. Your SQL guy should be able to do a bulk insert into a sql table for you you can then attach that table in access. Link to comment Share on other sites More sharing options...
Jobu Posted October 28, 2015 Share Posted October 28, 2015 you'd think this far into the game, ms would have come up up with an addon in excel that is able to split a large file into manageable pieces, especially a delimited file with headers, should be easy to identify row breaks, oh well.....thanks for your help though! That large of a CSV is just not a good way to move data would be my guess. CSV has several drawbacks. Performance being at the forefront. It's an old school format and I doubt you will ever see any new functionality associated with it in the office platform. You do have a couple of Microsoft ways of doing it. If you can hack VBA you could easily write it into a table row by row in access. Apparently there also is an add in for Excel called Power Pivot that can do it it too. Never have used that though. I have not had good luck with add-ins. https://social.technet.microsoft.com/Forums/office/en-US/54ca6d3a-4b1c-4318-aa8c-7ac803d3af69/what-is-the-maximum-file-size-for-csv-that-excel-can-open-excel-2013-64bit?forum=excel Link to comment Share on other sites More sharing options...
The Poojer Posted October 28, 2015 Author Share Posted October 28, 2015 problem is, the windows based u/i i extracted the data from only allowed xlsx or csv, so i was kinda f-ed from the get go. i may look into the vba option if i can't get my sql guy to help That large of a CSV is just not a good way to move data would be my guess. CSV has several drawbacks. Performance being at the forefront. It's an old school format and I doubt you will ever see any new functionality associated with it in the office platform. You do have a couple of Microsoft ways of doing it. If you can hack VBA you could easily write it into a table row by row in access. Apparently there also is an add in for Excel called Power Pivot that can do it it too. Never have used that though. I have not had good luck with add-ins. https://social.technet.microsoft.com/Forums/office/en-US/54ca6d3a-4b1c-4318-aa8c-7ac803d3af69/what-is-the-maximum-file-size-for-csv-that-excel-can-open-excel-2013-64bit?forum=excel Link to comment Share on other sites More sharing options...
Jobu Posted October 28, 2015 Share Posted October 28, 2015 Don't overlook EditPlus. Open it up and save it into several files. Editplus has line numbers so you can open the file goto the end see how many lies you have and then move to the middle of the file. Select the lower half and delete, then save the file as file 1. Reopen the original do the same thing only deleting the top half of the file. Save as File2. EditPlus is not free, but you get to use it unlimited for 30 days. https://www.editplus.com/ Alternatively, there is a freeware text editor called Gun that is written in assembly. No size limit either. I have not used this but have heard good things about it. http://www.movsd.com/thegun.htm Good luck Pooj. Link to comment Share on other sites More sharing options...
Recommended Posts