[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Querying a 16 GB Text File..

I have a text file that is 16 GB that I need to query against.  I tried to link the file to an Access Database, but the database would not link to it, at least not for me.  Is this too large for Access to query against, and if so, can someone help me with some other options?  Thanks.
0
tomfarrar
Asked:
tomfarrar
2 Solutions
 
virtuadeptCommented:
Can you post the file here?  (Just kidding. Don't do that. :)

Do you have access to use MS SQL Server? If so you could probably use the BCP (bulk insert) utility to load that file into a table(s) and add some indexes on it and then it will be much faster to query against. Trying to use that file to search on a regular basis is going to be slow, putting it in a relational database will make your searches a lot faster if you'll need to search it on a regular basis.

Another idea, if you have to use Access, is to search the web for file splitting utility and split that text file up into smaller chunks that Access can handle. Then write some queries that will query each chunk and combine the results to a single result set.
0
 
peter57rCommented:
You definitely can't link to it from Access.  2Gb is the max size Access can deal with.

I would think that you should be able to read the file line by line using either basic text file file commands or maybe ADO code.
0
 
Jeffrey CoachmanCommented:
As a long shot, try opening it in Excel 2007 or newer.
...Then filtering it.

If it is less than 1 million rows, you might have a shot...
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
PortletPaulCommented:
It might be helpful to know "what type of file" this 16Gb monster is. Perhaps there are other ways to assist that don't require sql?

e.g.TextAnalysisTool.NET
0
 
tomfarrarAuthor Commented:
It is a text file..
0
 
PortletPaulCommented:
:-) yes, most probably constructed from characters...

ok, not quite what I was seeking. How is the file generated? (e.g. is it some sort of log file)
Was it generated on a Linux machine? Windows?
What type of data does it contain e.g. "all rows have the same columns", or, "no 2 rows are the same layout", something in between)

basically more background on the file, its purpose, and what you intend to do with the contents of that file
0
 
tomfarrarAuthor Commented:
Not sure I can answer your questions, but it is well structured.  It is a tab-delimited text file.  Probably generated on a Windows machine, but I am not sure.  All records (rows) have the same columns.  It is a transactional file.
0
 
PortletPaulCommented:
good answer - thanks; it is much clearer what that file is.

one "tactical idea" would be to split that file into smaller chunks so you can process each chunk using Access. This can be achieved by many methods and there are freeware options (e.g. http://www.gdgsoft.com/gsplit/ - I haven' t used this specific product its simply an example, it says it will split by line quantities.)

But: To be honest I'd much rather a "strategic solution" using a dbms with larger capacity and I'd also want to ensure that the transactional file(s) don't keep aggregating.
0
 
tomfarrarAuthor Commented:
I understand.  The data is historical, and will not change in the number of records.  I actually have three files (different years) that are about 16 GB.  Thanks.
0
 
tomfarrarAuthor Commented:
There is no real easy solution here, but your ideas were helpful.
0
 
tomfarrarAuthor Commented:
Thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now