Converting large text files to excel

[fari]

Hi,

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

I will really appreciate the help.

Warm regards

Farida

[Denis Beauregard]

Le 26 Feb 2006 20:50:29 -0800, "fari" <[email protected]> écrivait
dans soc.genealogy.computing:

Hi,

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

With Excel, you can select the line where you begin to read
the file.


Denis

[Helen Castle]

You can import in Access or any other database.

Some have WYSIWYG interface to you can see the format of each line - or at
least tell it the denominator. I used to do this stuff fairly regularly
about 10 years ago so I could have a go for you, I would need to fiddle with
it to do it, I couldnt talk you thru it - mainly because I havent done it
for so long - I moved a lot of stuff from DOS to Windows 3, 3.1, 95, 98 ME
etc.

Importing more than 65000 rows into Excel is pointless as you know but a
database should be able to do it fairly easily.

Helen Castle
Narangba Qld 4504

"fari" <[email protected]> wrote in message
news:[email protected]...

Hi,

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

I will really appreciate the help.

Warm regards

Farida

[Dennis Lee Bieber]

On 26 Feb 2006 20:50:29 -0800, "fari" <[email protected]> declaimed the
following in soc.genealogy.computing:

Hi,

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

Well, the | is no problem... Presuming it never appears within the
data itself.

Any decent text editor (probably not Notepad) should be able to
slurp in the file, and do a find&replace on | with \n (or \n\r if using
raw MS-DOS convention). Or, if one has Python available, something on
the likes of:

fin = open("big_ugly_file", "r")
data = fin.read().split("|")
fout = open("real_lines_file", "wt")
for ln in data:
fout.write(ln + "\n")

fin.close()
fout.close()

As for import? Is the data comma separated, tab separated, fixed
width? String data quoted?

Excel's CSV import function (also available in Access) allows for
defining the columns...

Too many rows? Well, split the file using some editor...
--

==============================================================
[email protected] | Wulfraed Dennis Lee Bieber KD6MOG
[email protected] | Bestiaria Support Staff
==============================================================
Home Page: <http://www.dm.net/~wulfraed/
Overflow Page: <http://wlfraed.home.netcom.com/

[Steve Hayes]

On 26 Feb 2006 20:50:29 -0800, "fari" <[email protected]> wrote:

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

I'd call it into my word processor and do a search and replace -- replacing |
with the LF character (for Unix) or CR-LF for other formats.


--
Steve Hayes from Tshwane, South Africa
http://www.geocities.com/Athens/7734/stevesig.htm
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

[T.M. Sommers]

Steve Hayes wrote:

On 26 Feb 2006 20:50:29 -0800, "fari" <[email protected]> wrote:

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

I'd call it into my word processor and do a search and replace -- replacing |
with the LF character (for Unix) or CR-LF for other formats.

If it's on Unix, then use tr.

--
Thomas M. Sommers -- [email protected] -- AB2SB

[Robert G. Eldridge]

On 26 Feb 2006 20:50:29 -0800, "fari" <[email protected]> wrote:

Hi,

I have several text files having more than 65000 rows. The character
separating the rows is | (piped character). Can someone help me as to
how this can be done. Its all right if the data runs across several
sheets for each file.

I will really appreciate the help.

Warm regards

Farida

The following is one option provided the content of the longest row
will fit into an Excel cell.

1. Divide the text files so that there are only 65,000 rows or less in
each file.

2. Copy and paste each text file into Excel. All rows should now be in
the A column.

3. Select the A column and then use Excels Data/Text to Columns...
menu function first selecting Delimited then Next then remoce the tick
from the default Tab selection and select Other and enter the |
character in the field to the right of Other then Next and Finish.

The data will now be divided into columns and all | characters gone.

If some of the data is dates then all bets are off - use a database
application instead <grin>. Dates and Excel don't mix easily.

Note
Excels text to columns facility is great for removing the leading
space often found when data is copy/pasted from Web pages.
Use the fixed width option, set the divider where the data starts and
then don't import the first column to instantly remove the leading
space content.

--
Robert G. Eldridge Toronto NSW Australia
http://www2.hunterlink.net.au/~ddrge/
Now researching ELDRIDGE families world wide
1000's at my Web site * Wanted * Any Eldridge related information