[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Querying a 16 GB Text File..

Posted on 2013-02-07
11
Medium Priority
?
281 Views
Last Modified: 2013-02-28
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
Comment
Question by:tomfarrar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 450 total points
ID: 38864187
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38864399
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38864705
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 38866430
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 38868227
It is a text file..
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38869991
:-) 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
 
LVL 7

Author Comment

by:tomfarrar
ID: 38870094
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1050 total points
ID: 38870144
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 38871662
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
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 38940112
There is no real easy solution here, but your ideas were helpful.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 38940114
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question