Notices
Computer & Technology Related Post here for help and discussion of computing and related technology. Internet, TVs, phones, consoles, computers, tablets and any other gadgets.

.txt problem

Thread Tools
 
Search this Thread
 
Old 25 January 2005, 01:09 AM
  #1  
velohead66
Scooby Regular
Thread Starter
 
velohead66's Avatar
 
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like on 1 Post
Question .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.
Old 25 January 2005, 03:24 AM
  #2  
markr1963
Scooby Regular
 
markr1963's Avatar
 
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes on 0 Posts
Default

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
Old 25 January 2005, 08:01 AM
  #3  
Stueyb
Scooby Regular
 
Stueyb's Avatar
 
Join Date: May 2002
Posts: 1,893
Likes: 0
Received 0 Likes on 0 Posts
Default

you could use sed (stream editor) to remove all the excessive blank fields. Once you have the output file, it will be excelable
Old 25 January 2005, 08:34 AM
  #4  
GaryK
Scooby Regular
 
GaryK's Avatar
 
Join Date: Sep 1999
Location: Bedfordshire
Posts: 4,037
Likes: 0
Received 0 Likes on 0 Posts
Default

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
Old 25 January 2005, 09:05 AM
  #5  
Dracoro
Scooby Regular
 
Dracoro's Avatar
 
Join Date: Sep 2001
Location: A powerslide near you
Posts: 10,261
Likes: 0
Received 0 Likes on 0 Posts
Default

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.
Old 25 January 2005, 09:35 AM
  #6  
IWatkins
Scooby Regular
 
IWatkins's Avatar
 
Join Date: Mar 2000
Location: Gloucestershire, home of the lawnmower.
Posts: 4,531
Likes: 0
Received 0 Likes on 0 Posts
Default

http://www.textpad.com/ is your friend.

Open the file here and use regular expressions to select and delete blank lines etc.

Cheers

Ian
Old 25 January 2005, 04:05 PM
  #7  
bashful
Scooby Regular
 
bashful's Avatar
 
Join Date: Aug 2002
Posts: 154
Likes: 0
Received 0 Likes on 0 Posts
Default

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.
Old 26 January 2005, 02:09 AM
  #8  
velohead66
Scooby Regular
Thread Starter
 
velohead66's Avatar
 
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like on 1 Post
Question 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.
Old 26 January 2005, 06:14 AM
  #9  
markr1963
Scooby Regular
 
markr1963's Avatar
 
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes on 0 Posts
Default

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
Old 26 January 2005, 07:29 AM
  #10  
HankScorpio
Scooby Regular
 
HankScorpio's Avatar
 
Join Date: Feb 2004
Posts: 5,848
Likes: 0
Received 0 Likes on 0 Posts
Default

Searching from the bottom deals with the contigency of having more than 2 empty lines together.
If that works, that's a top tip!
I do similar regularly and I've always just run the replace several times until no dual occurrences.
Old 26 January 2005, 09:24 AM
  #11  
markr1963
Scooby Regular
 
markr1963's Avatar
 
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes on 0 Posts
Default

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
Old 26 January 2005, 10:02 AM
  #12  
markr1963
Scooby Regular
 
markr1963's Avatar
 
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes on 0 Posts
Default

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:
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
Old 26 January 2005, 08:32 PM
  #13  
Jamie_B
Scooby Regular
 
Jamie_B's Avatar
 
Join Date: Jan 2004
Location: Derby
Posts: 415
Likes: 0
Received 0 Likes on 0 Posts
Default

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
Here's another vote for TEXTPAD .
Old 26 January 2005, 09:14 PM
  #14  
velohead66
Scooby Regular
Thread Starter
 
velohead66's Avatar
 
Join Date: Oct 2002
Location: ex UK [SE], now Sunshine State [QLD,AUS]
Posts: 565
Likes: 0
Received 1 Like on 1 Post
Thumbs up 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.
Related Topics
Thread
Thread Starter
Forum
Replies
Last Post
Abx
Subaru
22
09 January 2016 05:42 PM
PetrolHeadKid
Driving Dynamics
10
05 October 2015 05:19 PM
T.K
General Technical
10
02 October 2015 11:35 AM
the shreksta
Other Marques
26
01 October 2015 02:30 PM
minguela
Wheels And Tyres For Sale
0
29 September 2015 11:28 AM



Quick Reply: .txt problem



All times are GMT +1. The time now is 03:58 PM.