Converting Spreadsheet to Table in forum

[tomos]

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

e.g. from the Mini-Review Tamplate

App Name	Name of app reviewed
App URL	Link to the app's homepage
App Version Reviewed	Version being reviewed

which actually is

Code

[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

Code

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   

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!

[app103]

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]

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

[app103]

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. 

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 ?   
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]

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 )

[app103]

if you replace every "," with this:

Code

[/td][td]

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

Code

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

added this before first line:

Code

[tr][td]

and this after last line:

Code

[/tr][/td]

and inserted the whole thing between this:

Code

[table]
[/table]

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

[Hirudin]

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]

can try save as html in Excel.

[app103]

can try save as html in Excel.

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

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

[tomos]

can try save as html in Excel.

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

Yeah, OOo html is a pretty scary business

[tomos]

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.

[crono]

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

Code

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

Code

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

Code

[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]

Very nice!   

[Perry Mowbray]

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

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
    Dim hGlobalMemory As Long, lpGlobalMemory As Long
    Dim hClipMemory As Long, X As Long
    
    ' Allocate movable global memory.
    '-------------------------------------------
    hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
    
    ' Lock the block to get a far pointer
    ' to this memory.
    lpGlobalMemory = GlobalLock(hGlobalMemory)
    
    ' Copy the string to this global memory.
    lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
    
    ' Unlock the memory.
    If GlobalUnlock(hGlobalMemory) <> 0 Then
       MsgBox "Could not unlock memory location. Copy aborted."
       GoTo ExitHere
    End If
    
    ' Open the Clipboard to copy data to.
    If OpenClipboard(0&) = 0 Then
       MsgBox "Could not open the Clipboard. Copy aborted."
       Exit Function
    End If
    
    ' Clear the Clipboard.
    X = EmptyClipboard()
    
    ' Copy the data to the Clipboard.
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
    
ExitHere:

    If CloseClipboard() = 0 Then
       MsgBox "Could not close Clipboard."
    End If
End Function
Sub ExportBB()
    Dim rSelected As Range
    Set rSelected = Selection
    
    Dim rRow As Range, rCell As Range
    
    Dim strBBText As String
    
    strBBText = "[table]"
    
    For Each rRow In rSelected.Rows
        strBBText = strBBText & "[tr]"
        For Each rCell In rRow.Cells
            'Debug.Print rCell.Address
            strBBText = strBBText & "[td]" & rCell.Value & "[/td]"
        
        Next
        strBBText = strBBText & "[/tr]"
    
    Next
    strBBText = strBBText & "[/table]"
    ClipBoard_SetData strBBText
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]

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

[tomos]

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 ??

[Perry Mowbray]

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]

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

Code

[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 ?

[tomos]

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

[crono]

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.

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]

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   

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

So I just changed the name 

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!

[crono]

[tomos]

 


see here

[tomos]

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.chatnfiles.com/The%20Pier%20Shareware%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.exe

http://files.chatnfiles.com/The%20Pier%20Shareware%203/026/files.htm DOS Cataloger

Freeware

but unfortunately ends up as [when opened in OOo [b]or[/b] when converted][EDIT]

4cat140.zip.exe	http://files.chatnfiles.com/The%20Pier%20Shareware%203/026/files.htm
DOS Cataloger	Freeware

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??

[crono]

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.