Import very large txt text file 101MB in MS Access

I have a very large file in text format (txt) 101 MB  that I can open in Notepad after 5 min

I need to import it in MS Access.
Uusing the import wizard function after 25/30 min the system says "the first row is too large". However I can not cut the first row in notepad
I tried twice, error message was the same

When trying to import this file in MS Excel using the import function, the system says "No memory" after 5/15 min

I tried to cut the notepad in half.. very long and tedious and the part I could open was not a proper representation of this section in notepad

Any advice to handle this case?

Thanks

pollacsekvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonLarsenCommented:
Do you have ms sql available? DTS is usually pretty quick, and shows you how it is going along the way....

However I suspect that the format of your text is possibly the problem....

How long is the first line?

Also have look at getting hold of a better text editor, EditPad Pro (http://www.editpadpro.com/) is my personal choice other people like ultraedit and others.

Maybe try getting the first 3 or 4 lines and making a new text file with them in and getting an import specification working in Access then try using that?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pollacsekvAuthor Commented:
Thanks Simon

can not download right now the editpadpro b/c of admin access needed

Will try last option .. what is the import specification? where do I input the information for the import specification?
I have not seen this option in import wizard function

Txs
0
Bob LambersonSoftware EngineerCommented:
Have you tried linking the file instead of importing it?

Bob
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SimonLarsenCommented:
When you do an import click on the advanced button. It lets you save or laod an import specification.
0
SimonLarsenCommented:
Something just occurred to me, editpad pro has a demo version and is just an executable from memory. So you should be able to run it as is, no admin needed.
0
frukioCommented:
any chance that the file has mac / unix line breaks?
this would cause Windows applications to see the entire file as a single line.
if so you would need to convert the line breaks somehow (BBEdit, vim, probably any number of little shareware tools just for converting linebreaks)
0
pollacsekvAuthor Commented:
Hi,

to reply to Simon and Frukio,
1) copying editpadpro that a colleague download it, my machine automatically erase it from C\:

2) What are the mac/unix breaks?

Thanks
0
frukioCommented:
Different platforms use different characters to signify the end of one line and the beginning of another.
unix uses 0x0a (10). mac uses 0x0d (13). Windows uses both characters - 0x0d followed by 0x0a.

If you open a file with mac or unix linebreaks in notepad, you will see box characters, and all the lines will run together.

The easiest way to test this is to open the file in Notepad. The cursor will be at the beginning of the file. Press the down arrow once. Does the cursor move to the beginning of what you think should be the next line? If so the linebreaks are ok. If it doesn't move, or moves to the end of the file, then the linebreaks may be a problem.
0
SimonLarsenCommented:
And while you're in notepad grab a couple of lines for a new file to check for anything else.

It is going to the linebreaks though.
0
frukioCommented:
Oh, Simon is right - down arrow in Notepad doesn't have anything to do with line breaks, it just moves the cursor down, wherever that happens to be.

You could try turning off "Word Wrap" in the Format menu.
0
Steve BinkCommented:
Do not accept this as a solution.

All experts had valid methods to try, hence the recommendation.  I use UltraEdit myself, and IT ROCKS!  Handles Unix/Mac formatting as well as DOS, and even has a hex editor for those hard to reach places...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.