Importing raw logs into Excel

[Sinner_G]

What's the maximum amount of data you can import into M$ Excel? I have tried to import a raw log (over 1 GB) and it was cut after 2 days' worth of log.

[starec]

It was somewhere around 65.500 rows last time I checked. Excel is not the way to go. In a busy site that's just a few hours of traffic...

[Dreamquick]

~64,000 rows is the limit Excel can handle, if you want to deal with more than that you'll need a database of some kind.

If memory serves Access has a 2gb database size limit so you may have to try importing them into a real database program if an Access import fails.

[Sinner_G]

Around 65'000 rows is about what I get.

2Gb should be enough, but I am no Access guru. So how do I import raw log data into an Access database?

[Dreamquick]

Personally I don't import logs into access so you'll have to forgive me if this is a little vague in places...

1) Create a new blank access database.
2) Right click in the white area inside the new "database" window.
3) Choose import from the pop-up menu.
4) Follow the instructions the wizard will give you as it takes you through the various steps.

Once it has sat and processed for a while (potentially several hours) it'll display a message saying that it either completed successfully, totally failed or failed on some records.

Unfortunately beyond this advice there's not a lot to say as any problems etc. will be dependant upon the data you are attempting to import (e.g. which fields you have, what data they contain etc.)

Tony

[Sinner_G]

Thanks Dreamquick

Basically this is exactly what I was just trying. Works fine until I choose my log file and click the import button. Then Access tells me something about there not being an object. When I click ok the wizard appears, kind of.The boy is there, but empty except for the cancel, next and finish buttons.

Any idea what I am doing wrong?

[Kevin]

Hi,
I would suggest that you change the log file to *.txt first. Then import as advised previously.
When asked for delimited or fixed choose delimited. Then choose space that separates the fields (It’s not perfect but it’s as best as you will get).
It works for me - hope its works for you.
If it does work you will need to learn the basic of Access query. Let me know if this has worked and if you need more advice.

[Sinner_G]

Kevin, thanks for the advice, but my problem was that the Access import wizard did not work, i.e. I never came to the window where you can choose the separator. I tried to do it on another computer (same set-up and everything) and it worked perfectly. Makes no sense but hey, this is Micro$oft...

What's the place to start learning about Access queries?

[ukgimp]

Sinner_G

I tried this and it worked fine.

After saving the file as a text from a zipped log file. Go to File >get external data. Then change type to .txt. Choose the file, it then give you the option to delimit. I chose the top one, and then space to delimit. Follow the wizzard and Bingo.

Hope you get it sorted.

Have a good play with the queries, and look at Cross Tab queries. They are pretty slick at grouping data quickly, say by IP or referer.

[Kevin]

As the file is so big I would say its either the set-up Access on your PC or the power of your PC. Access will load most of the text file into memory before it allows you to choose delimited.

Try testing this out with a smaller text file. If it still doesn't work its the set-up of Access. Don't know what version you have but the later versions will only install a minimum amount of the program on install. If that is the case just modify the installation for Access to ‘Run from PC’.

Can’t recommend a site but searching on Access Tutorial there is a long list. Try [fgcu.edu ]

[Sinner_G]

I was already using a smaller file for trials. And the computer it worked on is a laptop with less RAM than the PC where it did not...

As I said, Micro$oft. I researched the matter a bit and it seems others have made the same experience.

[alesimu]

I am facing the same problem right now. Did you discover any way of importing in MSAccess such big files (or even just linking?) Size of my txt file is just over 2G.

[Sinner_G]

Hi alesimu and welcome to WebmasterWorld.

As stated above, the problem I had was with Access itself. Once that was resolved (actually it wasn't just tried on another computer), the import of files over 1GB was no problem. Just takes a looooong time, but that is to be expected.