.txt problem
#1
Scooby Regular
Thread Starter
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like
on
1 Post
.txt problem
I need to import a file into excel, which is easy, but the file is too large for the 65,000 rows of excel.
The file is not db compatible, either.
The file also contains many (probably thousands) of rows of blank data which is the root problem.
Q1 ) Can I import into excel in such a way, that when sheet 1 is full, sheet 2 is used.
Q2 ) Can I use any application like wordpad that will delete the superflous rows.
Q3 ) Can I do some thing with VB for excel that will solve my problem.
HELP.
The file is not db compatible, either.
The file also contains many (probably thousands) of rows of blank data which is the root problem.
Q1 ) Can I import into excel in such a way, that when sheet 1 is full, sheet 2 is used.
Q2 ) Can I use any application like wordpad that will delete the superflous rows.
Q3 ) Can I do some thing with VB for excel that will solve my problem.
HELP.
#2
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
What format is the .txt file in? Is it delimited ie comma or tab? In what way isn't it DB compatible?
Anyhow, your questions:
Q1) Don't know an easy way (if any at all) of doing that
Q2) You could open in wordpad or similar, do a find and replace on hard carriage returns (paragraph marks) - Search for 2 consecutive and replace with one - also search from the end of the file up
Q3) Not sure about a VB solution - probably do-able something along the lines of reading the file in, scanning it for multiple occurences of chr(13) (think thats a hard carriage return), write the chunk upto the occurence into a variable then repeat the process, concatenating the chucks separated by one hard carriage return.
HTH
Mark
Anyhow, your questions:
Q1) Don't know an easy way (if any at all) of doing that
Q2) You could open in wordpad or similar, do a find and replace on hard carriage returns (paragraph marks) - Search for 2 consecutive and replace with one - also search from the end of the file up
Q3) Not sure about a VB solution - probably do-able something along the lines of reading the file in, scanning it for multiple occurences of chr(13) (think thats a hard carriage return), write the chunk upto the occurence into a variable then repeat the process, concatenating the chucks separated by one hard carriage return.
HTH
Mark
#4
Scooby Regular
Join Date: Sep 1999
Location: Bedfordshire
Posts: 4,037
Likes: 0
Received 0 Likes
on
0 Posts
The excel 65K limit cannot be overcome, ridiculous what with 32bit porcessors that have been around for years but there you go! As mentioned your best bet is to read it line by line in VB, does it have any form of separation, comma tab or other character?
Gary
Gary
#5
Scooby Regular
Join Date: Sep 2001
Location: A powerslide near you
Posts: 10,261
Likes: 0
Received 0 Likes
on
0 Posts
If you know a way to break down/group the data then create a pivot table that reads from this text file then narrow down the pivot table selections (or group the data) and put the data in separate sheets. A few more steps than that though, but it'll be less work and quicker than writing a load of vba.
#6
Scooby Regular
Join Date: Mar 2000
Location: Gloucestershire, home of the lawnmower.
Posts: 4,531
Likes: 0
Received 0 Likes
on
0 Posts
http://www.textpad.com/ is your friend.
Open the file here and use regular expressions to select and delete blank lines etc.
Cheers
Ian
Open the file here and use regular expressions to select and delete blank lines etc.
Cheers
Ian
#7
There's a handy UNIX utility called 'split', which takes the name of your file and a targer number of lines, and creates as many files each containing up to that number of lines as you need. There're lots of DOS implementations of it out there.
Trending Topics
#8
Scooby Regular
Thread Starter
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like
on
1 Post
textpad
Textpad seems intriguing, i'll have a look.
It seems the best way to go is to copy paste from notepad into ms access.
This is feasible (ie access accepts the data) except that clipboard is too small ?
Q) how small is clipboard.
Q) can you see what size is currently in clipboard.
So I may as well copy paste into excel, but I may have to do this many many times, with each cycle painfully slow/laborious.
Opening in word is 5,000 plus pages long, 7,000,000 characters.
The blank lines are caused by "carrage return" ie a zero length something.
Q) If I could find & replace those, thats half the battle.
Thanks So Far.
It seems the best way to go is to copy paste from notepad into ms access.
This is feasible (ie access accepts the data) except that clipboard is too small ?
Q) how small is clipboard.
Q) can you see what size is currently in clipboard.
So I may as well copy paste into excel, but I may have to do this many many times, with each cycle painfully slow/laborious.
Opening in word is 5,000 plus pages long, 7,000,000 characters.
The blank lines are caused by "carrage return" ie a zero length something.
Q) If I could find & replace those, thats half the battle.
Thanks So Far.
#9
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
To deal with your last question:
Open your file in word, click on the paragraph mark button off the toolbar (looks like a reversed P). This will reveal carriage returns, tabs, spaces and so on. Got to Edit|Replace and click the 'more' button to reveal extra options. With the cursor in 'find' select Special then 'paragraph mark' twice then with the cursor in 'replace' again click 'Special' then select 'paragraph mark' once. In the search options select 'up'. Here's what your doing: 1 carriage return separates the paragraphs, the 2nd is your empty line. Searching from the bottom deals with the contigency of having more than 2 empty lines together.
HTH
Mark
Open your file in word, click on the paragraph mark button off the toolbar (looks like a reversed P). This will reveal carriage returns, tabs, spaces and so on. Got to Edit|Replace and click the 'more' button to reveal extra options. With the cursor in 'find' select Special then 'paragraph mark' twice then with the cursor in 'replace' again click 'Special' then select 'paragraph mark' once. In the search options select 'up'. Here's what your doing: 1 carriage return separates the paragraphs, the 2nd is your empty line. Searching from the bottom deals with the contigency of having more than 2 empty lines together.
HTH
Mark
#10
Searching from the bottom deals with the contigency of having more than 2 empty lines together.
I do similar regularly and I've always just run the replace several times until no dual occurrences.
#11
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
Just tried what I suggested and it doesn't work as I hoped. Better is to select All for the search direction and do multiple replaces as HankScorpio says. I'll see if I can figure a way to do it in VBA
Mark
Mark
#12
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
Ok, not very elegant but this seems to work. Record a new macro (doesn't matter what it does), assigning a keyboard shortcut (not strictly necessary, just easier to run). Stop recording the macro then Tools|Macro|Visual Basic Editor locate the NewMacros module in the upper left pane and replace the contents of the macro (just the bit between Sub YourMacroName and End sub) you've just created with the following:
HTH
Mark
Code:
i = 0 ' initial condition that allows loop to keep looping Do Until i = 1 'condition to exit loop Selection.EndKey Unit:=wdStory, Extend:=wdExtend Selection.MoveRight Unit:=wdCharacter, Count:=1 Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p^p" .Replacement.Text = "^p" .Forward = False .Wrap = wdFindAsk .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute With Selection If .Find.Forward = True Then .Collapse Direction:=wdCollapseStart Else .Collapse Direction:=wdCollapseEnd End If .Find.Execute Replace:=wdReplaceAll If .Find.Forward = True Then .Collapse Direction:=wdCollapseEnd Else .Collapse Direction:=wdCollapseStart End If .Find.Execute End With If Selection.Find.Found = True Then 'Are there still occurences of ^P^P ? i = 0 'this will make the loop continue Else i = 1 'this will make the loop exit End If Loop
HTH
Mark
#13
Originally Posted by IWatkins
http://www.textpad.com/ is your friend.
Open the file here and use regular expressions to select and delete blank lines etc.
Cheers
Ian
Open the file here and use regular expressions to select and delete blank lines etc.
Cheers
Ian
#14
Scooby Regular
Thread Starter
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like
on
1 Post
major success
Major Success....thanks to Alastair Harris.
Import the .txt into ms access (using "get external data") works a treat, simple & quick.
Thanks Alistair.
Also thanks to other contributors as you have touched upon some interesting areas that I should also explore.
FYI the file was 140,013 rows long, so 2.2 times excels capacity. Deleting superflous data is a breeze, now.
Import the .txt into ms access (using "get external data") works a treat, simple & quick.
Thanks Alistair.
Also thanks to other contributors as you have touched upon some interesting areas that I should also explore.
FYI the file was 140,013 rows long, so 2.2 times excels capacity. Deleting superflous data is a breeze, now.
Thread
Thread Starter
Forum
Replies
Last Post