473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Formatting Results so that They Can be Nicely Imported into a Spreadsheet.

Hi All.
Let's say I have some badly formatted text called doc:
doc=
"""
friendid
Female

23 years old

Los Gatos

United States
friendid
Male

24 years old

San Francisco, California

United States
"""

How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States
The latter is a lot easier to organize and can be quickly imported
into Excel's column format.

Thanks Much,
Sam

Aug 4 '07 #1
8 1248
On Aug 5, 9:35 am, SMERSH009 <SMERSH0...@gmail.comwrote:
Hi All.
Let's say I have some badly formatted text called doc:

doc=
"""
friendid
Female

23 years old

Los Gatos

United States
friendid
Male

24 years old

San Francisco, California

United States
"""

How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States

The latter is a lot easier to organize and can be quickly imported
into Excel's column format.
You write a script to read your input file and write it out either in
CSV format, using the Python csv module, or just use
';'.join(output_row) if you are sure that there are no ';' characters
in the data.

Is this a homework question?

Aug 5 '07 #2
On Aug 4, 6:35?pm, SMERSH009 <SMERSH0...@gmail.comwrote:
Hi All.
Let's say I have some badly formatted text called doc:

doc=
"""
friendid
Female

23 years old

Los Gatos

United States
friendid
Male

24 years old

San Francisco, California

United States
"""

How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States

The latter is a lot easier to organize and can be quickly imported
into Excel's column format.

Thanks Much,
Sam
d = doc.split('\n')

f = [i.split() for i in d if i]

g = [' '.join(i) for i in f]

rec = []
temprec = []
for i in g:
if i:
if i == 'friendid':
rec.append(temprec)
temprec = [i]
else:
temprec.append(i)
rec.append(temprec)

output = [';'.join(i) for i in rec if i]

for i in output: print i

## friendid;Female;23 years old;Los Gatos;United States
## friendid;Male;24 years old;San Francisco, California;United
States

Aug 5 '07 #3
<me********@aol.comwrote in message
news:11*********************@z24g2000prh.googlegro ups.com...
On Aug 4, 6:35?pm, SMERSH009 <SMERSH0...@gmail.comwrote:
>Hi All.
Let's say I have some badly formatted text called doc:

doc=
"""
friendid
Female

23 years old

Los Gatos

United States
friendid
Male

24 years old

San Francisco, California

United States
"""

How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States

The latter is a lot easier to organize and can be quickly imported
into Excel's column format.

Thanks Much,
Sam

d = doc.split('\n')

f = [i.split() for i in d if i]

g = [' '.join(i) for i in f]

rec = []
temprec = []
for i in g:
if i:
if i == 'friendid':
rec.append(temprec)
temprec = [i]
else:
temprec.append(i)
rec.append(temprec)

output = [';'.join(i) for i in rec if i]

for i in output: print i

## friendid;Female;23 years old;Los Gatos;United States
## friendid;Male;24 years old;San Francisco, California;United States
also, I would suggest you use CSV format. CSV stands for "Comma Seperated
Variable" and Excel can load such a sheet directly.

Instead of seperating using ; seperate using , Of course, this provides a
problem when there is a , in a string. Resolution is to quote the string.
Being such, you can just go ahead and quote all strings. So you would want
the output to be:

"friendid","Female","23 years old","Los Gatos","United States"
"friendid","Male","24 years old","San Francisco, California","United States"

Numbers should not be quoted if you wish to treat them as numeric and not
text.
Aug 5 '07 #4
On Aug 4, 9:21?pm, "Jim Langston" <tazmas...@rocketmail.comwrote:
<mensana...@aol.comwrote in message

news:11*********************@z24g2000prh.googlegro ups.com...


On Aug 4, 6:35?pm, SMERSH009 <SMERSH0...@gmail.comwrote:
Hi All.
Let's say I have some badly formatted text called doc:
doc=
"""
friendid
Female
23 years old
Los Gatos
United States
friendid
Male
24 years old
San Francisco, California
United States
"""
How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States
The latter is a lot easier to organize and can be quickly imported
into Excel's column format.
Thanks Much,
Sam
d = doc.split('\n')
f = [i.split() for i in d if i]
g = [' '.join(i) for i in f]
rec = []
temprec = []
for i in g:
if i:
if i == 'friendid':
rec.append(temprec)
temprec = [i]
else:
temprec.append(i)
rec.append(temprec)
output = [';'.join(i) for i in rec if i]
for i in output: print i
## friendid;Female;23 years old;Los Gatos;United States
## friendid;Male;24 years old;San Francisco, California;United States

also, I would suggest you use CSV format.
Well, the OP asked for a specific format. One is not
always at liberty to change it.
CSV stands for "Comma Seperated
Variable" and Excel can load such a sheet directly.
And Excel can load the shown format directly also,
just specify the delimiter.
>
Instead of seperating using ; seperate using , Of course, this provides a
problem when there is a , in a string.
Which explains the popularity of using tabs as delimiters.
The data deliverable specification I use at work
uses the pipe character | which never appears as data
in this particular application.
Resolution is to quote the string.
Which makes the file bigger and isn't necessary
when tabs and pipes are used as delimiters.
Being such, you can just go ahead and quote all strings. So you would want
the output to be:

"friendid","Female","23 years old","Los Gatos","United States"
"friendid","Male","24 years old","San Francisco, California","United States"
Which I would do if I had a specification that
demanded it or was making files for others. For my
own use, I wouldn't bother as it's unnecessary work.
>
Numbers should not be quoted if you wish to treat them as numeric and not
text.
A good reason not to use quotes at all. Besides which,
Excel can handle that also.

Aug 5 '07 #5
On Aug 4, 8:25 pm, "mensana...@aol.com" <mensana...@aol.comwrote:
On Aug 4, 9:21?pm, "Jim Langston" <tazmas...@rocketmail.comwrote:
<mensana...@aol.comwrote in message
news:11*********************@z24g2000prh.googlegro ups.com...
On Aug 4, 6:35?pm, SMERSH009 <SMERSH0...@gmail.comwrote:
>Hi All.
>Let's say I have some badly formatted text called doc:
>doc=
>"""
>friendid
>Female
> 23 years old
> Los Gatos
> United States
>friendid
>Male
> 24 years old
> San Francisco, California
> United States
>"""
>How would I get these results to be displayed in a format similar to:
>friendid;Female;23 years old;Los Gatos;United States
>friendid;Male; 24 years old;San Francisco, California;United States
>The latter is a lot easier to organize and can be quickly imported
>into Excel's column format.
>Thanks Much,
>Sam
d = doc.split('\n')
f = [i.split() for i in d if i]
g = [' '.join(i) for i in f]
rec = []
temprec = []
for i in g:
if i:
if i == 'friendid':
rec.append(temprec)
temprec = [i]
else:
temprec.append(i)
rec.append(temprec)
output = [';'.join(i) for i in rec if i]
for i in output: print i
## friendid;Female;23 years old;Los Gatos;United States
## friendid;Male;24 years old;San Francisco, California;United States
also, I would suggest you use CSV format.

Well, the OP asked for a specific format. One is not
always at liberty to change it.
CSV stands for "Comma Seperated
Variable" and Excel can load such a sheet directly.

And Excel can load the shown format directly also,
just specify the delimiter.
Instead of seperating using ; seperate using , Of course, this provides a
problem when there is a , in a string.

Which explains the popularity of using tabs as delimiters.
The data deliverable specification I use at work
uses the pipe character | which never appears as data
in this particular application.
Resolution is to quote the string.

Which makes the file bigger and isn't necessary
when tabs and pipes are used as delimiters.
Being such, you can just go ahead and quote all strings. So you would want
the output to be:
"friendid","Female","23 years old","Los Gatos","United States"
"friendid","Male","24 years old","San Francisco, California","United States"

Which I would do if I had a specification that
demanded it or was making files for others. For my
own use, I wouldn't bother as it's unnecessary work.
Numbers should not be quoted if you wish to treat them as numeric and not
text.

A good reason not to use quotes at all. Besides which,
Excel can handle that also.
Thanks for all your posts guys.
mensanator's was the most helpful, and I only ended up needing to use
a few lines from that code.
The only question that remains for me--and this is just for my
knowledge-- what does the "if i" mean in this code snippet?
f = [i.split() for i in d if i]
How is it helpful to leave a dangling "if i"? Why not just f =
[i.split() for i in d]?

And yes John, this was indeed a "homework question." It was for my
daughter's preschool. You are going to help her ace her beginner
Python class! (No, this was not a homework question).

Aug 5 '07 #6
On Aug 5, 4:06?am, SMERSH009 <SMERSH0...@gmail.comwrote:
On Aug 4, 8:25 pm, "mensana...@aol.com" <mensana...@aol.comwrote:


On Aug 4, 9:21?pm, "Jim Langston" <tazmas...@rocketmail.comwrote:
<mensana...@aol.comwrote in message
>news:11*********************@z24g2000prh.googlegr oups.com...
On Aug 4, 6:35?pm, SMERSH009 <SMERSH0...@gmail.comwrote:
Hi All.
Let's say I have some badly formatted text called doc:
doc=
"""
friendid
Female
23 years old
Los Gatos
United States
friendid
Male
24 years old
San Francisco, California
United States
"""
How would I get these results to be displayed in a format similar to:
friendid;Female;23 years old;Los Gatos;United States
friendid;Male; 24 years old;San Francisco, California;United States
The latter is a lot easier to organize and can be quickly imported
into Excel's column format.
Thanks Much,
Sam
d = doc.split('\n')
f = [i.split() for i in d if i]
g = [' '.join(i) for i in f]
rec = []
temprec = []
for i in g:
if i:
if i == 'friendid':
rec.append(temprec)
temprec = [i]
else:
temprec.append(i)
rec.append(temprec)
output = [';'.join(i) for i in rec if i]
for i in output: print i
## friendid;Female;23 years old;Los Gatos;United States
## friendid;Male;24 years old;San Francisco, California;United States
also, I would suggest you use CSV format.
Well, the OP asked for a specific format. One is not
always at liberty to change it.
CSV stands for "Comma Seperated
Variable" and Excel can load such a sheet directly.
And Excel can load the shown format directly also,
just specify the delimiter.
Instead of seperating using ; seperate using , Of course, this provides a
problem when there is a , in a string.
Which explains the popularity of using tabs as delimiters.
The data deliverable specification I use at work
uses the pipe character | which never appears as data
in this particular application.
Resolution is to quote the string.
Which makes the file bigger and isn't necessary
when tabs and pipes are used as delimiters.
Being such, you can just go ahead and quote all strings. So you would want
the output to be:
"friendid","Female","23 years old","Los Gatos","United States"
"friendid","Male","24 years old","San Francisco, California","United States"
Which I would do if I had a specification that
demanded it or was making files for others. For my
own use, I wouldn't bother as it's unnecessary work.
Numbers should not be quoted if you wish to treat them as numeric and not
text.
A good reason not to use quotes at all. Besides which,
Excel can handle that also.

Thanks for all your posts guys.
mensanator's was the most helpful, and I only ended up needing to use
a few lines from that code.
The only question that remains for me--and this is just for my
knowledge-- what does the "if i" mean in this code snippet?
f = [i.split() for i in d if i]
d is a list of strings, some of which are empty (caused
by the previous split). "if i" evaluates False for
an empty string, so that particular i will not get
split and ends up ommitted from f.
How is it helpful to leave a dangling "if i"? Why not just f =
[i.split() for i in d]?
Compare the result of having "if i":
[[], ['friendid'], ['Female'], ['23', 'years', 'old'], ['Los',
'Gatos'], ['United', 'States'], ['friendid'], ['Male'], ['24',
'years', 'old'], ['San', 'Francisco,', 'California'], ['United',
'States']]

to not having it:
>>ff = [i.split() for i in d]
ff
[[], ['friendid'], ['Female'], [], [], ['23', 'years', 'old'], [], [],
['Los', 'Gatos'], [], [], ['United', 'States'], ['friendid'],
['Male'], [], [], ['24', 'years', 'old'], [], [], ['San',
'Francisco,', 'California'], [], [], ['United', 'States'], []]

All the blank lines became empty lists which actually
wouldn't change anything if they get joined. But that
won't always be the case. Knowing how to use conditionals
in list comprehensions is worth knowing.

Even with the "if i" included, we end up with an
empty list at the start. This because the first "blank"
line wasn't blank, it was a space, so it passes the
"if i" test.
>
And yes John, this was indeed a "homework question." It was for my
daughter's preschool. You are going to help her ace her beginner
Python class! (No, this was not a homework question).
Aug 5 '07 #7
me********@aol.com <me********@aol.comwrote:
...
Even with the "if i" included, we end up with an
empty list at the start. This because the first "blank"
line wasn't blank, it was a space, so it passes the
"if i" test.
....and you can fix that by changing the test to [... if i.split()].
Alex
Aug 5 '07 #8
En Sun, 05 Aug 2007 06:06:54 -0300, SMERSH009 <SM********@gmail.com>
escribió:
The only question that remains for me--and this is just for my
knowledge-- what does the "if i" mean in this code snippet?
f = [i.split() for i in d if i]
How is it helpful to leave a dangling "if i"? Why not just f =
[i.split() for i in d]?
`if i` means `if i is considered true`. In this case we are talking about
strings: "" is false and all other strings are true. So this is a way to
say `if i is not the empty string`, effectively filtering out empty lines.
Perhaps using more meaningful names for variables makes the code more
clear:

exploded_lines = [line.split() for line in lines if line]

--
Gabriel Genellina

Aug 12 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: scooterm | last post by:
### Question Can anyone name a text editor or XML editor (prefer open source) that can open a simple table XML file in a spreadsheet-type GUI, without adding any extra formatting or junk to the...
1
by: Josh Schmidt | last post by:
I'm using the Excel application object to create a spreadsheet from a recordset. Everything seems to work fine except any date fields are not displaying properly. For example 4/18/2004 is...
2
by: Colleyville Alan | last post by:
I am using Access and have embedded the ActiveX control Formula One that came with Office 2000. (ver 3.04). I have created and formatted a spreadsheet and now I want to copy the info with...
0
by: Stewart Allen | last post by:
Hi there Opinions on the best way to do this. I want to import data from an Excel spreadsheet into Access and want the data to be tested before the tables are updated. At the moment all the...
0
by: ImraneA | last post by:
Hi there Have a database, where front-end interface allows user to select a ms access database. From there, standard tables are linked. Routine, that creates a spreadsheet, for each table a...
1
by: Will | last post by:
1. I have a list of products in an excel spreadsheet, the fields are: Prefix, Stock Code, Suffix and Description. I want to import them into Access and then merge the 3 fields Prefix, Stock Code,...
7
by: Middletree | last post by:
I am trying to display dates in a spreadsheet, but the dates need to be in a format that will allow them to be sorted in Excel. The datatype in the SQL Server database is datetime. In this case, I...
6
by: DeniseY | last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
8
by: victor.herasme | last post by:
Hi, i am building a little script and i want to output a series of columns more or less like this: 1 5 6 2 2 8 2 9 5 The matter is that i don't know in advance how many columns...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.