Solved

Do you know how to import a large txt file into excel?

Posted on 2014-10-29
18
137 Views
Last Modified: 2014-10-30
I have a huge txt file (zipped in a 59mb zip file) and I am trying to import it into an excel file. However the txt file is so big it doesn't import all the list.

I'm asking if someone could import this attached file into an excel file and post it for me to use.

File can be downloaded here: https://www.dropbox.com/s/kwz6lryuz01jjn4/US.zip?dl=0
0
Comment
Question by:mabehr
[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
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 5

Expert Comment

by:ReneD100
ID: 40411600
Ha, so we meet again ;)
I'll try to leave the long/lat data out to make it a bit smaller, but this kind of data really gets handled easier in a database table than in Excel.
0
 
LVL 7

Expert Comment

by:slubek
ID: 40411605
Excel has limited number of columns and rows in sheet. The limits are different in different MS Office versions. Even if somebody with Excel 2013 (limited to 1,048,576 rows by 16,384 columns) can import your data (I didn't check number of rows in your file), you will not be able to use it in older versions.
If you want to make computations on such big file, use another software, MS Access for example.
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40411624
Excel 2010 also only 1,048,576  of the 2,172,789. It's no problem to read it into Access, if you don't have that maybe SQL Express is an option?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40411677
what version of excel do you have?  if you have 2010  or 2013 then we can use the powerpivot add-in that can import more than the limit of the excel.
confirm the version of excel you have
0
 

Author Comment

by:mabehr
ID: 40411756
I have excel 2010. I don't have Access
0
 
LVL 32

Expert Comment

by:Paul Sauvé
ID: 40412029
I imported your data using the Text Wizard (Data tab -> Get External Data -> From Text).

In fact, only the last line of the US.txt file is not imported into the page!
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40412040
Paul,

how could you import that whereas the rows are in millions? it is beyond the limit of excel max rows.
0
 
LVL 32

Expert Comment

by:Paul Sauvé
ID: 40412070
I'm using MS Office 2010 Home & Student edition - Win 7 Home Premium. Using the Text Wizard i imported 1048576 rows!

Not millions, a bit over a million (2 ** 20 = 1048576)!
0
 

Author Comment

by:mabehr
ID: 40412074
I can get that too but not anything over that. When I open a new spreadsheet and use that process, telling it to download at
that number row or even starting at 1,000,000 for some reason I just get a fragment of the remaining 1 million+ rows.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40412076
Paul,

the data which mabehr uploaded txt file has 2,172,788 rows  which is beyond the limit of 1048576
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40412113
Mabehr

here is the solution for you.

albeit powerpivot could be also an option, but i have improted the txt into excel into the 4 worksheets.

i used freeware http://csved.sjfrancke.nl/ software portable to split the txt file into the 4 pieces and then i imported them in Excel.

Now, to use all four sheets as your lookup table, you can combine lookup tables in Index-Match with adding iferror function.

i tried to attached the combined file, but EE has a limit of 50MB so could not be uploaded in this forum. but using that freware soft you can do it by yourself.
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40412642
Remains a question how handy a huge sheet like that is and what the performance of lookup is. Without Access I'd still go for free SQL server and create a link from Excel to the database.
0
 

Author Comment

by:mabehr
ID: 40413023
Rene, how would you do the above?
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40413050
Give me a bit and I'll see if I can come with a solution for you
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40413256
I thought you wanted the Zip codes - this file contains schools, hotels, restaurants etc.
0
 

Author Comment

by:mabehr
ID: 40413342
I do want zip codes. I was told that this site contained a listing of zip codes for every city and state in the us:
http://download.geonames.org/export/dump/ and specifically the US.zip file.
0
 
LVL 5

Accepted Solution

by:
ReneD100 earned 500 total points
ID: 40413378
I know - I told you that ;)
However, I used the allcountries file. Let me see what that extracts too if I only take the US ones. Ah, 43000 lines.
USZipOnly.xlsx
0
 

Author Closing Comment

by:mabehr
ID: 40413425
Finally! Glad we got that figured out. Thanks ReneD. I'll use this.

My next question will be a related one after I finish with what I was trying to do with my previous question.

Thanks, again. :)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

724 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