Pau Lo
asked on
splitting gigantic space delimited text file prior to excel import
are there any tools which can essentially split an enormous (2.5GB) text file into manageable 'chunks' which can be fully imported into excel? I have received 2x 2.5GB files which I am assured are simply space delimited log files, but excel cannot import all the rows due to limitations. I was hoping of an additional tool/process to split it into 4 smaller files and import 1 by 1. I have tried importing into SQL Server express but that's causing numerous errors. It does import into excel in the format required but misses lots of the rows (it is an unknown exactly how many rows there are in these beasts.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>Which version of Excel? These use to be an issue prior to .xlsx
2010. Ot definately cant handle or import all the data as it provided a message when I tried to say as such.
2010. Ot definately cant handle or import all the data as it provided a message when I tried to say as such.
>>Log parser wouldnt help with that
Looks like Logparser can handle Unix date format:
https://forums.iis.net/t/1209318.aspx
Looks like Logparser can handle Unix date format:
https://forums.iis.net/t/1209318.aspx
ASKER
Maybe i will try logparser and see if I can search and filter for the key rows then export and import just those into excel and apply the formula to convert the date stamps.
>>filter for the key rows then export
Output to CSV is built in: -o:csv
It has a LOT going for it. Hence my "very underutilized" comment.
Output to CSV is built in: -o:csv
It has a LOT going for it. Hence my "very underutilized" comment.
ASKER
Thanks will try it out tommorow.
I like LogParser because of the SQL abilities.
A possible alternative that I'm just starting to learn is PowerShell. I find it also deals with large amounts of data pretty effectively.
A quick Google shows how to deal with Unix dates in PowerShell:
https://gradualdev.wordpress.com/2014/01/21/converting-from-unix-timestamp/
Anything but Excel loading 1million rows per sheet, creating a formula on all the rows then 'finding' specific things.
A possible alternative that I'm just starting to learn is PowerShell. I find it also deals with large amounts of data pretty effectively.
A quick Google shows how to deal with Unix dates in PowerShell:
https://gradualdev.wordpress.com/2014/01/21/converting-from-unix-timestamp/
Anything but Excel loading 1million rows per sheet, creating a formula on all the rows then 'finding' specific things.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess that I should also include VBA and VBScript as file processors.
ASKER
Do you have examples for how to use powershell to filter out the rows in the file based on criteria
>>you can also use Powershell to filter records
I agree, which is why I also suggested it.
>>to filter out the rows in the file based on criteria
Select-String has -Pattern:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-string?view=powershell-6
Not as nice and has it's own problems:
Get-Content has possibilities
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-content?view=powershell-6
I agree, which is why I also suggested it.
>>to filter out the rows in the file based on criteria
Select-String has -Pattern:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-string?view=powershell-6
Not as nice and has it's own problems:
Get-Content has possibilities
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-content?view=powershell-6
Oops. I missed the PS part of your comment.
ASKER
just doing some reading on select-string, could anyone give an example where you supply the txt file for it to search, and it exports the results of all rows where a keyword is found in that row, to an output csv file?
create file q.txt with:
Then from a powershell prompt:
Hello
WorldThen from a powershell prompt:
Select-String -Path q.txt -Pattern "hello" | Export-Csv -Path q.csv
Since you awarded participation points to everyone, can you post which method you actually used?
That will help anyone that has the same problem in the future when they read this question.
That will help anyone that has the same problem in the future when they read this question.
Pleased to help
ASKER