topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Sunday October 13, 2024, 8:09 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Last post Author Topic: Converting Spreadsheet to Table in forum  (Read 40367 times)

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Converting Spreadsheet to Table in forum
« on: May 12, 2007, 02:29 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
[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  :)

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

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  :D  :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!
Tom
« Last Edit: May 25, 2007, 08:01 AM by tomos »

app103

  • That scary taskbar girl
  • Global Moderator
  • Joined in 2006
  • *****
  • Posts: 5,885
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #1 on: May 12, 2007, 03:55 AM »
Converting spreadsheets to something you can post here can be hell.  :(

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

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #2 on: May 12, 2007, 06:31 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  :)

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  :(

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

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:  :D
Would it be a very time consuming process?
Tom

app103

  • That scary taskbar girl
  • Global Moderator
  • Joined in 2006
  • *****
  • Posts: 5,885
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #3 on: May 12, 2007, 02:02 PM »
Well I just had a look at the html version as exported from OOo, jeez, what a load of code  :)

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.  ;D

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:  :D
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.  :-[

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.

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #4 on: May 12, 2007, 03:29 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 :) )
Tom

app103

  • That scary taskbar girl
  • Global Moderator
  • Joined in 2006
  • *****
  • Posts: 5,885
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #5 on: May 12, 2007, 04:22 PM »
if you replace every "," with this:

[/td][td]

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

[/td][/tr][tr][td]

added this before first line:

[tr][td]

and this after last line:

[/tr][/td]

and inserted the whole thing between this:

[table]
[/table]

It might work...but that's still a lot of manual editing.  :(

Hirudin

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 543
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #6 on: May 12, 2007, 06:29 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.

kimmchii

  • Honorary Member
  • Joined in 2005
  • **
  • Posts: 360
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #7 on: May 12, 2007, 06:52 PM »
can try save as html in Excel.
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
  • Joined in 2006
  • *****
  • Posts: 5,885
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #8 on: May 12, 2007, 10:39 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. :o

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

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #9 on: May 13, 2007, 04:44 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. :o

Yeah, OOo html is a pretty scary business :)
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #10 on: May 13, 2007, 04:48 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.
Tom

crono

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 179
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #11 on: May 13, 2007, 10:09 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
@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
c:\ convert.bat MyCSVFile.csv
or simply Drop the CSV onto the Batchfile. The Result will be saved in as "table.txt"

Demo Input:
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


[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

app103

  • That scary taskbar girl
  • Global Moderator
  • Joined in 2006
  • *****
  • Posts: 5,885
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #12 on: May 13, 2007, 10:36 PM »
Very nice!    :Thmbsup:

Perry Mowbray

  • N.A.N.Y. Organizer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 1,817
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #13 on: May 14, 2007, 01:44 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:

VBA Code
Code: Visual Basic [Select]
  1. Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
  2.    As Long
  3. Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
  4.    As Long
  5. Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
  6.    ByVal dwBytes As Long) As Long
  7. Declare Function CloseClipboard Lib "User32" () As Long
  8. Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
  9.    As Long
  10. Declare Function EmptyClipboard Lib "User32" () As Long
  11. Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
  12.    ByVal lpString2 As Any) As Long
  13. Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
  14.    As Long, ByVal hMem As Long) As Long
  15.  
  16. Public Const GHND = &H42
  17. Public Const CF_TEXT = 1
  18. Public Const MAXSIZE = 4096
  19.  
  20. Function ClipBoard_SetData(MyString As String)
  21.     Dim hGlobalMemory As Long, lpGlobalMemory As Long
  22.     Dim hClipMemory As Long, X As Long
  23.    
  24.     ' Allocate movable global memory.
  25.    '-------------------------------------------
  26.    hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
  27.    
  28.     ' Lock the block to get a far pointer
  29.    ' to this memory.
  30.    lpGlobalMemory = GlobalLock(hGlobalMemory)
  31.    
  32.     ' Copy the string to this global memory.
  33.    lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
  34.    
  35.     ' Unlock the memory.
  36.    If GlobalUnlock(hGlobalMemory) <> 0 Then
  37.        MsgBox "Could not unlock memory location. Copy aborted."
  38.        GoTo ExitHere
  39.     End If
  40.    
  41.     ' Open the Clipboard to copy data to.
  42.    If OpenClipboard(0&) = 0 Then
  43.        MsgBox "Could not open the Clipboard. Copy aborted."
  44.        Exit Function
  45.     End If
  46.    
  47.     ' Clear the Clipboard.
  48.    X = EmptyClipboard()
  49.    
  50.     ' Copy the data to the Clipboard.
  51.    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
  52.    
  53. ExitHere:
  54.  
  55.     If CloseClipboard() = 0 Then
  56.        MsgBox "Could not close Clipboard."
  57.     End If
  58. End Function
  59. Sub ExportBB()
  60.     Dim rSelected As Range
  61.     Set rSelected = Selection
  62.    
  63.     Dim rRow As Range, rCell As Range
  64.    
  65.     Dim strBBText As String
  66.    
  67.     strBBText = "[table]"
  68.    
  69.     For Each rRow In rSelected.Rows
  70.         strBBText = strBBText & "[tr]"
  71.         For Each rCell In rRow.Cells
  72.             'Debug.Print rCell.Address
  73.            strBBText = strBBText & "[td]" & rCell.Value & "[/td]"
  74.        
  75.         Next
  76.         strBBText = strBBText & "[/tr]"
  77.    
  78.     Next
  79.     strBBText = strBBText & "[/table]"
  80.     ClipBoard_SetData strBBText
  81. End Sub


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.


tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #14 on: May 14, 2007, 01:51 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
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #15 on: May 14, 2007, 01:53 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.
-Perry Mowbray (May 14, 2007, 01:44 AM)

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 ??
Tom
« Last Edit: May 14, 2007, 01:55 AM by tomos »

Perry Mowbray

  • N.A.N.Y. Organizer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 1,817
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #16 on: May 14, 2007, 02:10 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 :-[

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

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #17 on: May 14, 2007, 03:05 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
[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 ?
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #18 on: May 14, 2007, 06:00 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  :) )

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

crono

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 179
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #19 on: May 14, 2007, 06:08 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.computerh.../issues/ch000549.htm

after this, it should work correctly.

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?

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #20 on: May 14, 2007, 06:56 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  :-[ ??
I cant see anything obvious ...

sorry for bugging you ..
-
HEY - I figured it out - well YOU figured it out really  :-[  :D

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

So I just changed the name  :D

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
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!
Tom

crono

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 179
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #21 on: May 14, 2007, 07:05 AM »
 :Thmbsup:

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #22 on: May 14, 2007, 07:24 AM »
:up: :Thmbsup: :up: 
:-*

see here
Tom

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,963
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #23 on: May 14, 2007, 02:37 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
4cat140.zip.exe;http://files.chatnfi...re%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.chatnfi...re%203/026/files.htm
DOS Cataloger
Freeware

but unfortunately ends up as [when opened in OOo or when converted][EDIT]
4cat140.zip.exehttp://files.chatnfi...re%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 :) 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??
Tom
« Last Edit: May 15, 2007, 02:25 AM by tomos »

crono

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 179
    • View Profile
    • Donate to Member
Re: Converting Spreadsheet to Table in forum
« Reply #24 on: May 14, 2007, 03:00 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.