|
tomos
|
 |
« 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 Name | Name of app reviewed | | App URL | Link to the app's homepage | | App Version Reviewed | Version 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  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   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
|
|
|
|
|
app103
|
|
« Reply #1 on: May 12, 2007, 03:55:50 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 |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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 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? |
|
|
|
|
Logged
|
|
|
|
|
|
app103
|
|
« 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 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. |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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 ) |
|
|
|
|
Logged
|
|
|
|
|
app103
|
|
« Reply #5 on: May 12, 2007, 04:22:57 PM » |
|
if you replace every "," with this: and then manually edited and ended every line, except the last, with this: added this before first line: and this after last line: and inserted the whole thing between this: It might work...but that's still a lot of manual editing. |
|
|
|
|
Logged
|
|
|
|
|
Hirudin
|
|
« 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
|
|
« 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
|
|
« 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.  It wouldn't make it easier and most likely would make it more difficult. |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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. Yeah, OOo html is a pretty scary business |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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
|
|
|
|
|
crono
|
|
« 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 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 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 |
[ 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
|
|
« Reply #12 on: May 13, 2007, 10:36:59 PM » |
|
Very nice!  |
|
|
|
|
Logged
|
|
|
|
|
Perry Mowbray
|
|
« 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: Formatted for Visual Basic with the GeSHI Syntax Highlighter [ copy or print] 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. |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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
|
|
|
|
|
tomos
|
|
« 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
|
|
|
|
|
Perry Mowbray
|
|
« 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 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
|
|
« 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 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
|
|
|
|
|
tomos
|
|
« 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 ) I'll go before i tie myself up in verbal knots here ... |
|
|
|
|
Logged
|
|
|
|
|
crono
|
|
« 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.htmafter 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? |
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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 ??
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 .. PSI 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
|
|
|
|
|
crono
|
|
« Reply #21 on: May 14, 2007, 07:05:28 AM » |
|
|
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« Reply #22 on: May 14, 2007, 07:24:03 AM » |
|
|
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« 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 When opened in OOo (or converted as per batch file above) this would ideally be (& was originally) like but unfortunately ends up as [when opened in OOo or when converted][EDIT] 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?? |
|
|
|
« Last Edit: May 15, 2007, 02:25:52 AM by tomos »
|
Logged
|
|
|
|
|
crono
|
|
« 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
|
|
|
|
|
