Welcome Guest.   Make a donation to an author on the site April 17, 2014, 07:21:21 PM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
Read the full one-year retrospective report on DonationCoder.com.
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1] 2 Next   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: Converting Spreadsheet to Table in forum  (Read 16088 times)
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« on: May 12, 2007, 02:29:44 AM »

I'm trying to convert a (LARGE) spreadsheet file to a Table that will post here

e.g. from the Mini-Review Tamplate
App NameName of app reviewed
App URLLink to the app's homepage
App Version ReviewedVersion being reviewed

which actually is
[copy or print]
[table]
[tr][td][b]App Name[/b][/td][td]Name of app reviewed[/td][/tr]
[tr][td][b]App URL[/b][/td][td]Link to the app's homepage[/td][/tr]
[tr][td][b]App Version Reviewed[/b][/td][td]Version being reviewed[/td][/tr]
[/table]

I was wondering, using some sort of find & replace &
some way of adding prefix/suffix to lines
could I convert the seperators in a .CSV file to the appropriate table "td" thingys  smiley

I'm sorry the only way I can show this is as code -
otherwise it forms crazy tables & cant be read properly.
Makes it hard to read but you could cut & paste it elsewhere

[copy or print]
I thought I was being very smart -
I saved file as .csv file and tried to use [tr][td] instead of a comma as a separator.
But
only the first character "[" came through

But got me thinking
would there be a way of
prefixing each line with "[tr][td]"
suffixing each line with "[/td][/tr]"

and then using some sort of find & replace function to replace the separator with "[/td][td]"
(I could do that in Freehand but I'm sure some text editors would have it)

I would then at the end of each line have "[/td][td][/td][/tr]"
where i think the first two "[/td][td]" would have to be removed
but
I could live with deleting that "by hand" if I could get the rest automated to a certain extent ...

I can live without this but found it an interesting challenge - if you do too, please help if you can  cheesy  thumbs up

PS I did try pasting html table in post but it looses format, see dc thread here

[EDIT: solution I used is here, post#27/30  thanks, crono!
« Last Edit: May 25, 2007, 08:01:28 AM by tomos » Logged

Tom
app103
That scary taskbar girl
Global Moderator
*****
Posts: 5,018



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #1 on: May 12, 2007, 03:55:50 AM »

Converting spreadsheets to something you can post here can be hell.  Sad

First you would have to convert it to html, somehow. That is best done manually or in an html editor, as it is the only way it will look good. Using the 'export as html' option can have some bad results.

Make sure it is stripped of all extra code that is unnecessary, such as font sizes & colors, table & cell width info, etc.

Then you would have to do a find & replace of all the <'s with ['s and then all the >'s with ]'s
Logged

tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #2 on: May 12, 2007, 06:31:10 AM »

Then you would have to do a find & replace of all the <'s with ['s and then all the >'s with ]'s
thats the easy bit methinks  smiley

First you would have to convert it to html, somehow. That is best done manually or in an html editor, as it is the only way it will look good. Using the 'export as html' option can have some bad results.

Make sure it is stripped of all extra code that is unnecessary, such as font sizes & colors, table & cell width info, etc.
I think I'm out of my depth there already app  Sad

Well I just had a look at the html version as exported from OOo, jeez, what a load of code  smiley

Tried stripping out some stuff (find, replace)
but definitely starting to think this might be more work than it's worth

Nope that's definitely beyond my capabilities ...


When you say "First you would have to convert it to html ... in an html editor"
Would i have to go back to school to figure that one out ?  tellme  cheesy
Would it be a very time consuming process?
Logged

Tom
app103
That scary taskbar girl
Global Moderator
*****
Posts: 5,018



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: May 12, 2007, 02:02:55 PM »

Well I just had a look at the html version as exported from OOo, jeez, what a load of code  smiley

Tried stripping out some stuff (find, replace)
but definitely starting to think this might be more work than it's worth

Nope that's definitely beyond my capabilities ...

You just discovered why I said not to do that.  Grin

When you say "First you would have to convert it to html ... in an html editor"
Would i have to go back to school to figure that one out ?  tellme  cheesy
Would it be a very time consuming process?

There are WYSIWYG html editors where you can create a table as easily as painting in MS Paint.

Recreate the table in one of those and copy & paste the data from the spreadsheet into the boxes.

Then you go through and make sure you remove all that extra stuff from the code, like I mentioned before.

Like I said, it can be hell.  embarassed

I think what we need around here is a WYSIWYG forum post editor for creating the bbcode. Then you wouldn't have to create a spreadsheet and go through all this. You could create it in the editor instead.
Logged

tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #4 on: May 12, 2007, 03:29:04 PM »

thanks, app

think I'll have to reconsider ...

It's the List of Disc Cataloguers that Crush prepared that I was hoping to fit in a post on thread of same name.
The list now has 213 entries, with, I think, four columns ...

You can see why I'm looking for some kind of automated solution ...

Do you think the idea of replacing separators in a .csv file is a non runner
(see post one, esp the second code box, for anyone who hasn't a clue what I'm on about smiley )
Logged

Tom
app103
That scary taskbar girl
Global Moderator
*****
Posts: 5,018



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #5 on: May 12, 2007, 04:22:57 PM »

if you replace every "," with this:

[copy or print]
[/td][td]

and then manually edited and ended every line, except the last, with this:

[copy or print]
[/td][/tr][tr][td]

added this before first line:

[copy or print]
[tr][td]

and this after last line:

[copy or print]
[/tr][/td]

and inserted the whole thing between this:

[copy or print]
[table]
[/table]

It might work...but that's still a lot of manual editing.  Sad
Logged

Hirudin
Charter Member
***
Posts: 542


View Profile Give some DonationCredits to this forum member
« Reply #6 on: May 12, 2007, 06:29:31 PM »

I don't know how off hand, but I'm pretty sure AHK will read and write lines from a file. You could easily read a line, add the necessary characters to the beginning and end, do any find and replacing you need, and write the line back and go to the next.

This sounds like something I'd be capable of (with my very limited programming skillz) so I'm sure a lot of y'all would be too. I'm heading off for the weekend, but if someone hasn't made anything by the time I get back I'll take a stab at it.
Logged
kimmchii
Honorary Member
**
Posts: 360


View Profile Give some DonationCredits to this forum member
« Reply #7 on: May 12, 2007, 06:52:53 PM »

can try save as html in Excel.
Logged

If you find a good solution and become attached to it, the solution may become your next problem.
~Robert Anthony
app103
That scary taskbar girl
Global Moderator
*****
Posts: 5,018



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #8 on: May 12, 2007, 10:39:14 PM »

can try save as html in Excel.

You have any idea what that code looks like? It's a mess. Possibly worse than OOo. ohmy

It wouldn't make it easier and most likely would make it more difficult.
Logged

tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #9 on: May 13, 2007, 04:44:51 AM »

can try save as html in Excel.

You have any idea what that code looks like? It's a mess. Possibly worse than OOo. ohmy

Yeah, OOo html is a pretty scary business smiley
Logged

Tom
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #10 on: May 13, 2007, 04:48:30 AM »

I don't know how off hand, but I'm pretty sure AHK will read and write lines from a file. You could easily read a line, add the necessary characters to the beginning and end, do any find and replacing you need, and write the line back and go to the next.

This sounds like something I'd be capable of (with my very limited programming skillz) so I'm sure a lot of y'all would be too. I'm heading off for the weekend, but if someone hasn't made anything by the time I get back I'll take a stab at it.

That sounds great Hirudin

I'm back in 24 hrs myself,
will have a closer look at csv file then
I think a comma (or something..) will have to be stripped from the end of each sentence as well
but if everything else could be done on auto,
that could easily be done manually.
Logged

Tom
crono
Charter Honorary Member
***
Posts: 178


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #11 on: May 13, 2007, 10:09:29 PM »

Keep it simple - this sounds like a job for sed (download it from the gnuwin32 Project). You can run a Batch file with some sed commands to replace text and convert your CSV

The Batch file
[copy or print]
@echo off
set infile=%1%
sed -e "s/;/\[\/td\]\[td\]/g" "%infile%" > temp1.txt
sed -e "s/$/\[\/td\]\[\/tr]/g" temp1.txt > temp2.txt
sed -e "s/^/\[tr\]\[td\]/g" temp2.txt > temp1.txt
echo [table] > table.txt
type temp1.txt >> table.txt
echo [/table] >>  table.txt
del temp1.txt
del temp2.txt

call it on the commandline
Quote
c:\ convert.bat MyCSVFile.csv
or simply Drop the CSV onto the Batchfile. The Result will be saved in as "table.txt"

Demo Input:
[copy or print]
13;┬╗;Alien Quadrilogy;0
16;?;Alien: Director's Cut: Special Edition;2
17;?;Alien Quadrilogy Bonus-Disc;1
18;?;Alien - Die Wiedergeburt: Special Edition;2
19;?;Aliens - Die R├╝ckkehr: Special Edition;2
20;?;Alien┬│: Special Edition;2
21;21;American Beauty;1
23;23;American Psycho: Special Edition;1
24;┬╗;Ang Lee Limited Edition;0

Demo Output:
13┬╗Alien Quadrilogy0
16?Alien: Director's Cut: Special Edition2
17?Alien Quadrilogy Bonus-Disc1
18?Alien - Die Wiedergeburt: Special Edition2
19?Aliens - Die R├╝ckkehr: Special Edition2
20?Alien┬│: Special Edition2
2121American Beauty1
2323American Psycho: Special Edition1
24┬╗Ang Lee Limited Edition0


[copy or print]
[table]
[tr][td]13[/td][td]┬╗[/td][td]Alien Quadrilogy[/td][td]0[/td][/tr]
[tr][td]16[/td][td]?[/td][td]Alien: Director's Cut: Special Edition[/td][td]2[/td][/tr]
[tr][td]17[/td][td]?[/td][td]Alien Quadrilogy Bonus-Disc[/td][td]1[/td][/tr]
[tr][td]18[/td][td]?[/td][td]Alien - Die Wiedergeburt: Special Edition[/td][td]2[/td][/tr]
[tr][td]19[/td][td]?[/td][td]Aliens - Die R├╝ckkehr: Special Edition[/td][td]2[/td][/tr]
[tr][td]20[/td][td]?[/td][td]Alien┬│: Special Edition[/td][td]2[/td][/tr]
[tr][td]21[/td][td]21[/td][td]American Beauty[/td][td]1[/td][/tr]
[tr][td]23[/td][td]23[/td][td]American Psycho: Special Edition[/td][td]1[/td][/tr]
[tr][td]24[/td][td]┬╗[/td][td]Ang Lee Limited Edition[/td][td]0[/td][/tr][/table]


bye
Logged
app103
That scary taskbar girl
Global Moderator
*****
Posts: 5,018



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #12 on: May 13, 2007, 10:36:59 PM »

Very nice!    Thmbsup
Logged

Perry Mowbray
N.A.N.Y. Organizer
Charter Member
***
Posts: 1,795



Thoughtful Scribbles

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #13 on: May 14, 2007, 01:44:23 AM »

If you wanted to do it straight from Excel then the following code should work without too many issues. To use it you just have to copy and paste it into your Excel Code Editor in a new Module. The Procedure formats a string and puts it onto the Clipboard:


When I worked at in University we ran a Java based Wiki, and I did many other similar macros to convert from other Office documents to its markup.

Logged

tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #14 on: May 14, 2007, 01:51:23 AM »

Keep it simple - this sounds like a job for sed (download it from the gnuwin32 Project). You can run a Batch file with some sed commands to replace text and convert your CSV

The Batch file

.....

bye

thanks, crono

will try that today.

tom
Logged

Tom
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #15 on: May 14, 2007, 01:53:12 AM »

If you wanted to do it straight from Excel then the following code should work without too many issues. To use it you just have to copy and paste it into your Excel Code Editor in a new Module. The Procedure formats a string and puts it onto the Clipboard:

.........

When I worked at in University we ran a Java based Wiki, and I did many other similar macros to convert from other Office documents to its markup.

thanks Perry!

I'm afraid I don't have Excel myself,
but sure that will be of use to others

tom

EDIT or could it work in Open Office too ??
« Last Edit: May 14, 2007, 01:55:23 AM by tomos » Logged

Tom
Perry Mowbray
N.A.N.Y. Organizer
Charter Member
***
Posts: 1,795



Thoughtful Scribbles

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #16 on: May 14, 2007, 02:10:22 AM »

thanks Perry!

I'm afraid I don't have Excel myself,
but sure that will be of use to others

Ah, sorry: I just assumed embarassed

Quote
EDIT or could it work in Open Office too ??

I've not done an OO macro, but it doesn't use VBA does it? The principles would be the same, just a different language would be my guess.

- Perry
Logged

tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #17 on: May 14, 2007, 03:05:30 AM »

Keep it simple - this sounds like a job for sed (download it from the gnuwin32 Project). You can run a Batch file with some sed commands to replace text and convert your CSV

The Batch file

.....

bye

Okay,

I tried this -
without success I'm afraid but I suspect it may be to do with my csv file.
The final table.txt just has
[copy or print]
[table]
[/table]

I notice your demo input has ";" as separator and no inverted commas "" to delimit text.
So I made a csv file like that (Open Office)
but
I notice my csv file has varying amounts of separators -
sometimes three at the end of of a line, sometimes just one

any ideas there ?
thanks!!
tom
-

PS -
or maybe I should have rebooted after installing sed ?
or maybe I have to have batch file somewhere special ?
Logged

Tom
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #18 on: May 14, 2007, 06:00:03 AM »

I should qualify the last post by saying that I'm not very knowledgeable about what goes on "under the hood" in a soft or hardware sense

so
its well possible I'm just doing (or maybe not doing) something silly (or sensible as the case may be  smiley )

I'll go before i tie myself up in verbal knots here ...
Logged

Tom
crono
Charter Honorary Member
***
Posts: 178


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #19 on: May 14, 2007, 06:08:40 AM »

Sounds like your SED is not found. To make sure, open a command prompt ([Windows]+[R] -> cmd). Type "sed" - if you got something like "command not found", you have 2 options:

1) Change the path in your Batchfile to "C:/Program Files/YOUR_PATH_TO_SED/sed.exe"
or
2) Add SED to your path, so it is available everywhere. How to: http://www.computerhope.com/issues/ch000549.htm

after this, it should work correctly.

Quote
I notice my csv file has varying amounts of separators -
sometimes three at the end of of a line, sometimes just one
Hmmm... Sorry - must be a problem with OOo. Maybe there are some Blanks in the cells?
Logged
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #20 on: May 14, 2007, 06:56:29 AM »

See Below!!!

Hi Crono,

I did step 2) after which unfortunately it still doesnt work
(If I open run box and type sed I now get a flash of a (DOS?) window but nothing more which seems to indicate it has found the path - previously i got a window saying something in german along the lines of it couldnt be found)

but still no joy ...

You say change the path in the batch file
is there a path already there  embarassed ??
I cant see anything obvious ...

sorry for bugging you ..
-
HEY - I figured it out - well YOU figured it out really  embarassed  cheesy

the file name had spaces in it & didn't work when I dragged it onto the batchfile shortcut

So I just changed the name  cheesy

thanks crono, that's brilliant
I will post a link to the relevant post when I do post it - want to check it out a bit first ..


PS
Quote
I notice my csv file has varying amounts of separators -
sometimes three at the end of of a line, sometimes just one
Hmmm... Sorry - must be a problem with OOo. Maybe there are some Blanks in the cells?
Yes,
it was shooting [for the] blanks!
Logged

Tom
crono
Charter Honorary Member
***
Posts: 178


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #21 on: May 14, 2007, 07:05:28 AM »

 Thmbsup
Logged
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #22 on: May 14, 2007, 07:24:03 AM »

thumbs up Thmbsup thumbs up 
Kiss

see here
Logged

Tom
tomos
Charter Member
***
Posts: 8,053



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #23 on: May 14, 2007, 02:37:40 PM »

OOps

'fraid I'm running into problems again, but this time they definitely are at source - i.e. Open Office

I have a spreadsheet where there's more than one line in each cell -
in other words Ctrl+Return (OOo) has been used to create new line in the cell.

When this is saved as a csv file there is a new line for each internal "Return" in cells

e.g. this .csv file
Quote
4cat140.zip.exe;http://files.chatnfiles.c...areware%203/026/files.htm
DOS Cataloger;Freeware;;

When opened in OOo (or converted as per batch file above) this would ideally be (& was originally) like
4cat140.zip.exehttp://files.chatnfiles.c...areware%203/026/files.htm
DOS Cataloger
Freeware

but unfortunately ends up as [when opened in OOo or when converted][EDIT]
4cat140.zip.exehttp://files.chatnfiles.c...areware%203/026/files.htm
DOS CatalogerFreeware


The problem is with Open Office -

I wonder could it work in Excel -
I mean i wonder does Excel save a more successfull .csv file & if so what the difference is
would some kind person know already smiley or maybe try to create a similar spreadsheet -
(i.e. with more than one line within a cell) in Excel, save it as .csv file & then see if it opens okay??
« Last Edit: May 15, 2007, 02:25:52 AM by tomos » Logged

Tom
crono
Charter Honorary Member
***
Posts: 178


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #24 on: May 14, 2007, 03:00:30 PM »

This is really a problem. SED goes through the CSV line by line and I see no way to change this. One possible solution is, that you dont put multiple lines in one cell, but instead use some kind of seperator - # for example - which could be translated to a simple
.
I tried to save the Spreadsheet as HTML, but then you lose the Linebreak. Hmmm... I try to find a solution.
Logged
Pages: [1] 2 Next   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.095s | Server load: 0.31 ]