Author Topic: Converting Spreadsheet to Table in forum (Read 41024 times)

tomos · « **Reply #25 on:** May 15, 2007, 02:36 AM »

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
[Select]
[br]
.
I tried to save the Spreadsheet as HTML, but then you lose the Linebreak. Hmmm... I try to find a solution.
-crono (May 14, 2007, 03:00 PM)

didnt really understand till -
only saw the "br" when I quoted you

sounds like a good idea!

tomos · « **Reply #26 on:** May 16, 2007, 01:35 PM »

Any luck crono

I think if the # idea is feasible it might be the best !?

But I can do that in Text-Editor as well !!

crono · « **Reply #27 on:** May 16, 2007, 02:39 PM »

Hi Tomos,
I tried to dive into "Openoffice.org XML Export Filter"-Programming. This would be the "cleanest" way to do it, but I haven't found the time right now.
The #-thing would work at least I believe...

Try:

[Select]

@echo off
set infile=%1%
sed -e "s/#/\[br\]/g" "%infile%" > temp1.txt
sed -e "s/;/\[\/td\]\[td\]/g" temp1.txt > temp2.txt
sed -e "s/$/\[\/td\]\[\/tr]/g" temp2.txt > temp1.txt
sed -e "s/^/\[tr\]\[td\]/g" temp1.txt > temp2.txt
echo [table] > table.txt
type temp2.txt >> table.txt
echo [/table] >> table.txt
del temp1.txt
del temp2.txt

Its untested - please try it

tomos · « **Reply #28 on:** May 16, 2007, 04:45 PM »

thanks crono
might not be able to try for a couple of days (holiday here tomorrow - moving furniture plans..)
will let you know how i get on (with the script!) when i try it.

David1904 · « **Reply #29 on:** May 16, 2007, 05:03 PM »

Hi all,

There is a program called Columnizer (freebie) - from Gulf Coastal Software.
It does a lot of stuff with aligning columns, adding or deleting lines or characters. I really don't know whether it is of any use in this situation or not, but thought I'd mention it

David

tomos · « **Reply #30 on:** May 21, 2007, 03:40 AM »

Hi Tomos,
I tried to dive into "Openoffice.org XML Export Filter"-Programming. This would be the "cleanest" way to do it, but I haven't found the time right now.
The #-thing would work at least I believe...

Try:
[Select]
@echo off
set infile=%1%
sed -e "s/#/\[br\]/g" "%infile%" > temp1.txt
sed -e "s/;/\[\/td\]\[td\]/g" temp1.txt > temp2.txt
sed -e "s/$/\[\/td\]\[\/tr]/g" temp2.txt > temp1.txt
sed -e "s/^/\[tr\]\[td\]/g" temp1.txt > temp2.txt
echo [table] > table.txt
type temp2.txt >> table.txt
echo [/table] >> table.txt
del temp1.txt
del temp2.txt
Its untested - please try it
-crono (May 16, 2007, 02:39 PM)

Now [finally

] tested -

4cat140.zip.exe

http://files.chatnfi...re%203/026/files.htm
DOS Cataloger

Freeware

from this .csv file (using "#" as separator to indicate new line within a cell)

[Select]

4cat140.zip.exe;http://files.chatnfiles.careware%203/026/files.htm#DOS Cataloger;Freeware;;

looks very good to me Crono

thanks again!
- will try it with the full list spreadsheet sometime (soon) this week and post a link here when done

tomos · « **Reply #31 on:** May 21, 2007, 03:46 AM »

Hi all,

There is a program called Columnizer (freebie) - from Gulf Coastal Software.
It does a lot of stuff with aligning columns, adding or deleting lines or characters. I really don't know whether it is of any use in this situation or not, but thought I'd mention it

David
-David1904 (May 16, 2007, 05:03 PM)

Hi David,
thanks for that

I couldnt find a home page for that -
could you tell us a bit more about it or give a link ?

I did find the programme for download here -
http://www.simtel.ne...product_page%5D91922

thanks
tom

David1904 · « **Reply #32 on:** May 21, 2007, 06:57 AM »

Hi Tom,

Can't help you much sorry. I hunted for Gulf Coastal for a while - but without success.
I just remembered that a while back I found the program somewhere and tracked it back to the Gulf Coastal site - but that was a year or two back.
I've not really used it (just thought - that must be useful for something one day - and downloaded it)
So if you have it and have looked at it - you will know as much as I do (maybe more

)

David

AbteriX · « **Reply #33 on:** May 22, 2007, 02:01 AM »

That's a very interesting thread to me.

WOW David thank you for this info about Columnizer, i find this tool really handy.

Some Info's about Columnizer:

Columnizer 5.38
Gulf Coastal Software --- Note: This program is no longer supported.
Columnizer is a special program for working with column and row data.
Found by: David1904
Download: columnzr.zip ~650kB

DL possibility: http://www.simtel.ne...product_page%5D91922
Note you get there an "download-columnzr.zip.exe", start this to download the columnzr.zip ~650kB
(need to find an better DL)

Install: run the Setup.exe or just unpack the Setup.exe with UNIExtract

Size: Columnizer.exe ~ 1MB (300kB geUPXt) that is in total not many more then ~300kB

Stand alone?: Seams to be stand alone, uses an ini and text files to store the setings

"Columnizer does..."

Attempt to realign HTML
Insert into a column of data
Insert a column of data
Copy a column of data
Retain / Delete a column of data
Replace data within a column
Insert spread sheet separation character
Insert a row
Delete a row of data
Delete blank lines
Retain / Delete lines per data
Insert lines per data
Insert a carrage control
Vertical column concatenation
Insert incrementing letters
Replace line per data
Horizontal column concatenation
Append lines per data
Retain / Delete starting at specified data
Insert data within line Before / After specified data
Replace data with a top-down data list
Align all data into columns
Shuffle data
Find and mark duplicate lines
Replace with special characters
Insert incrementing numbers
Delete leading / trailing blanks
Format for ReadMe
Special Routines
Sort Ascending / Descending and Reverse order
Convert data to Courier font
Total a specified column
Break up file records
Replace any character within a file

"News"

New in version 5.38:
1. The 'Vertical Concatenation' combobox entry was not
droping down the correct control.

New in version 5.37:
1. The 'Delete web addresses' routine should be working
properly now.
2. A 'Delete (Strip out) data' routine was added. This
will strip out chosen data within all lines or bulk
data.
3. Some single line data parameters (10 max) are now
saved in a dropdown box for future use.

New in version 5.36:
1. A 'Delete web addresses' check box was added into
the ReadMe section that should take out all web
addresses for better listening.
2. All check boxes last checked positions are now
remembered.

New in version 5.35:
1. 'Row: To:' can now be made to auto reset to zeros
(although transparent) before each data modification.
2. ReadMe routine can now delete all tabed lines.
3. User work area colors saved between executions.
4. Routine dropdown box is continuously in view now.
5. There was a program error when the 'Hints' check
box was not clicked.

New in version 5.33 and 5.34:
1. Inconsequential changes were made.

New in version 5.32:
1. The 'Replace Special' routine was not replacing the
correct 'Replace with' character in some cases.

New in version 5.31:
1. The program now expands to larger screen sizes, as well
as centers.

New in version 5.30:
1. The text in the search boxes are now highlighted upon
focus.

New in version 5.29:
1. Replicate data.

AbteriX · « **Reply #34 on:** May 22, 2007, 05:59 AM »

Thank you all for the inspiration.
I have "rigging up" an script for >PSPad< to do this.
Just select some comma separated text in PSPad and execute this script.

Look in the script for Const mySEPARATOR = ","
and modify the comma to an char your CSV is separated, if you need this.
( Or improve this script with something like mySEPARATOR = InputBox("Separator:",,",") )

"The code for PSPad"

[Select]

' Save this script as e.g. CSV2BBTable.vbs
' into your PSPad script folder, e.g. "C:\Program Files\PSPad\Script\VBscript\CSV2BBTable.vbs"
' Then select some separated text (see mySEPARATOR) and execute this script from the script menu.
' ------------------------------------------------------------------------------
' author : Stefan Schuck 2007 for PSPad.com and DonationCoder.com
' Based on an proof of concept from "Crono" at https://www.donationcoder.com/forum/index.php?topic=8504.msg61390#msg61390
' Use code from : Scott Greenberg <[email protected]> [GoGoModule.vbs]
' http://gogogadgetscott.info/computers/cc/snippet.php?sid=34
' Use code from : James Swan [text_to_list.vbs]
' ------------------------------------------------------------------------------
Const module_name = "CSV-2-BBTable"
Const module_ver = "0.001"
Const mySEPARATOR = "," ' the separator in your CSV file to separate the table cells, e.g. , ; :
Sub Init
menuName = "&" & module_name
addMenuItem "Execute &CSV to BBcode Table" , menuName, "replaceRegExp"
addMenuItem "&Open this script file" , menuName, "openFile"
End Sub
Sub openFile
Set obj1 = newEditor()
obj1.openFile(moduleFileName(module_name))
End Sub
' ------------------------------------------------------------------------------
' Text editing.
' ------------------------------------------------------------------------------
Dim arrLines
' Pattern Find
Dim vFind(4)
vFind(0)= "#" 'not needed
vFind(1)= mySEPARATOR
vFind(2)= "$" 'start of each line
vFind(3)= "^" 'end of each line

' newtext Replace
Dim vReplace(4)
vReplace(0) = "[br]"
vReplace(1) = "[/td][td]"
vReplace(2) = "[/td][/tr]"
vReplace(3) = "[tr][td]"

Sub replaceRegExp
'*******************************************************************************
' author : James Swan ' Created : 28 September 2005
'*******************************************************************************
Set editor = newEditor()
With editor
.assignActiveEditor()
strInput = .selText()
End With
If Len(strInput) > 0 Then
arrLines = Split(strInput, vbCrLf)
For L = 0 to UBound(arrLines)
For i = 1 to UBound(vFind)
arrLines(L) = runRegExpReplace(arrLines(L),vFind(i) ,vReplace(i))
Next
Next
strList = "[table]" & vbCrLf
For L = 0 To UBound(arrLines)
strLine = Trim(arrLines(L))
strList = strList & strLine & vbCrLf
Next
strList = strList & "[/table]"
editor.selText strList
Else
MsgBox "Please select some text... ", 0, MODULE_TITLE
End If
Set editor = Nothing
End Sub

' ------------------------------------------------------------------------------
' Private function: Find and replace with RegEx
' ------------------------------------------------------------------------------
' author Scott Greenberg <[email protected]>
Private Function runRegExpReplace(haystack, Pattern, newtext)
Set regEx = New RegExp
If Pattern = "" Then
runRegExpReplace = haystack
Exit Function
End If
regEx.Pattern = Pattern
regEx.IgnoreCase = TRUE
regEx.Global = TRUE
runRegExpReplace = regEx.Replace(haystack, newtext)
End Function

For the other way around, export a web side table (e.g. to Excel) there is Table2Clipboard, an FireFox extension:
https://addons.mozil...e/firefox/addon/1852

"Table2Clipboard Infos"

Mozilla applications allow to select rows and columns from a table
simply pressing Control key and picking rows/columns with left mouse button.
...
If you want to paste data in Microsoft Excel on OpenOffice Calc with correct disposition simply use Table2Clipboard.
Pasting in plain text editors is also supported as CSV file (but you can change rows and columns separators from option dialog)

brett · « **Reply #35 on:** May 22, 2007, 09:06 AM »

How about CSVed
http://csved.sjfrancke.nl/index.html

All the power you will ever need to manipulate a CSV file.
The help file is limited, so sometimes you need to explore and learn.

Load a CSV file into the app, pay attn to the 'Set Separator' and refresh

You can modify/replace/search/prefix/suffix etc , its all there, plenty of power under the hood.
or even better....
File > Export fast > to html (view or file),

HTML of 'Demo Input' from previous post (export fast)

Spoiler

<HTML>
<HEAD>
<META HTTP - EQUIV = "Content - Type" CONTENT = "TEXT/HTML">
<META NAME = "GENERATOR" CONTENT = "Max's Export Components for Delphi" >
<TITLE></TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#0000FF" ALINK="#FF0000" VLINK="#FFFF00" TEXT="#000000">

<HR>
<TABLE WIDTH="100%" CELLPADDING="4" CELLSPACING="0" BORDER="1" BGCOLOR="#FFFFCF">

<TR BGCOLOR="#336699">
<TD>Column 1</TD>
<TD>Column 2</TD>
<TD>Column 3</TD>
<TD>Column 4</TD>
</TR>
<TR>
<TD>13</TD>
<TD>»</TD>
<TD>Alien Quadrilogy</TD>
<TD>0</TD>
</TR>
<TR BGCOLOR="#FFFFFF">
<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 BGCOLOR="#FFFFFF">
<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 BGCOLOR="#FFFFFF">
<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 BGCOLOR="#FFFFFF">
<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>
<HR>
Created: 23/05/2007 12:00:54 AM

</BODY>
</HTML>

Brett

AbteriX · « **Reply #36 on:** May 22, 2007, 09:51 AM »

Thank you brett for reminding me to CSVed.

Note, our solutions do this:

Did you read that the intention was

to create BB code formated table

for to input e.g. here in the forum ?

from this text:

Did you read that the intention was
to create BB code formated table
for to input e.g. here in the forum ?

Can CSVed this too ?

---
Edit:
upps, i had used no separator in my example ... nice too ;-)

tomos · « **Reply #37 on:** May 25, 2007, 08:31 AM »

See post Number 2
in this thread for a list of 213 (or is it 214 ) created by Crush
and facilitated by Crono's convert from spreadsheet to Forum Table batch file

Please post any opinions !!
-tomos (May 25, 2007, 07:21 AM)

follow the link to post#2 in quote above to see the final (successful) conversion of .csv file to forum table

- takes a bit of work though to get the multi-line cells.
(especially when there's over a hundered of them

)
If others want to use this it's possible limiting the width of the columns [edit: if that's possible] would be an easier way to go?

thanks again to crono & everyone else who chipped in

tom

tomos · « **Reply #38 on:** March 01, 2009, 11:10 AM »

hi Crono

I'm trying to update that disc catalogue list, I've changed computers so I've freshly installed sed
I saved your code (below) as Convert.bat
I'm attempting - unsuccessfully so far - to convert a csv file via drag-drop (I removed spaces from csv file name - that was initially a problem last time)
Separater is comma

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.
-crono (May 14, 2007, 06:08 AM)

I've tried option two above without success (I still get "'sed' is not recognised" when I try via command line)
In Option #1 you say "Change the path.."
eh ... what path (please remember I'm no coder nor even a power(ful) user for that matter

)

[Select]

@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

I've restarted and double checked/tried everything again but no luck

The only thing that does happen is if I right click Convert.bat and click open, it then creates a table.txt file with just
[table][/table]
as content

EDIT/ I tried replacing %1% in the batch file with the sed.exe path but that didnt work either
FORGOT TO SAY:
Windows XP SP2 up-to-date

EDIT2/ the sed file I downloaded was sed-4.1.5-setup.exe (the "Complete package, except sources") from
http://gnuwin32.sour...net/packages/sed.htm

crono · « **Reply #39 on:** March 02, 2009, 06:55 PM »

Hi Tomos,

in which folder have you installed your sed.exe? Lets assume it is in "C:\Program Files\Gnu\Sed\", than you should change the bat-file like this to ensure your sed.exe is found (this is what I meant by "change the path"):

[Select]

@echo off
set infile=%1%
"C:\Program Files\Gnu\Sed\sed.exe" -e "s/;/\[\/td\]\[td\]/g" "%infile%" > temp1.txt
"C:\Program Files\Gnu\Sed\sed.exe" -e "s/$/\[\/td\]\[\/tr]/g" temp1.txt > temp2.txt
"C:\Program Files\Gnu\Sed\sed.exe" -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

Hope that helps

bye bye

Btw: If you drop a file on the bat-file, the path of the dropped file is stored in %1%

Target · « **Reply #40 on:** March 02, 2009, 07:51 PM »

you could also do this in Excel using concatenation - this is perhaps not as elegant as some of the other solutions but it's quick and easy and it doesn't have any dependancies...

formula would look something like ="<tr><td>"&A1&"</td><td>"&B1&"</td><td>"&C1&"</td></tr>"

copy and paste the result into your forum (you'll need to add opening and closing table tags) and your done

you could also put your tags into a cell and reference that cell in your formula to save yourself some typing (the cell refs will need to be absolute)

tomos · « **Reply #41 on:** March 03, 2009, 04:23 PM »

in which folder have you installed your sed.exe? Lets assume it is in "C:\Program Files\Gnu\Sed\", than you should change the bat-file like this to ensure your sed.exe is found (this is what I meant by "change the path"):
[Select]
@echo off
set infile=%1%
"C:\Program Files\Gnu\Sed\sed.exe" -e "s/;/\[\/td\]\[td\]/g" "%infile%" > temp1.txt
"C:\Program Files\Gnu\Sed\sed.exe" -e "s/$/\[\/td\]\[\/tr]/g" temp1.txt > temp2.txt
"C:\Program Files\Gnu\Sed\sed.exe" -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
-crono (March 02, 2009, 06:55 PM)

It finds SED all right now (online armor requested permission to start it) but: a command window opens, closes and nothing else happens ...
the batch file is correct, I've checked it again and again
I copied sed.exe path into file in quotes as you show - it was in C:\Program Files\GnuWin32\bin\sed.exe (just in case)

command window said it couldnt find file path - broke it up in two parts actually, so I moved both files/folders up the tree a bit but now the command window shows completely blank
Is it important where the files are, and where in relation to each other ?

crono · « **Reply #42 on:** March 03, 2009, 05:12 PM »

Hello again,

[Select]

@echo off
REM File which is dropped onto the Batch File
set infile=%1%

REM change the WorkingDirectory to the folder which contains the dropped file
REM The new file table.txt will be created in the same folder as the source file
cd /d %1\..

REM Do the transformation
"C:\Program Files\GnuWin32\bin\sed.exe" -e "s/,/\[\/td\]\[td\]/g" "%infile%" > temp1.txt
"C:\Program Files\GnuWin32\bin\sed.exe" -e "s/$/\[\/td\]\[\/tr]/g" temp1.txt > temp2.txt
"C:\Program Files\GnuWin32\bin\sed.exe" -e "s/^/\[tr\]\[td\]/g" temp2.txt > temp1.txt

REM Build the output
echo [table] > table.txt
type temp1.txt >> table.txt
echo [/table] >> table.txt

REM Cleanup
del temp1.txt
del temp2.txt

I changed the script to work with comma as separator - the other versions used ";". If it does not work add "PAUSE" (without the quotes) at the end of the batch file (it prevents the command window from being closed) and post the contents and maybe a demo file which should be transformed.

bye

tomos · « **Reply #43 on:** March 04, 2009, 04:40 AM »

Hello again,
...

I changed the script to work with comma as separator - the other versions used ";". If it does not work add "PAUSE" (without the quotes) at the end of the batch file (it prevents the command window from being closed) and post the contents and maybe a demo file which should be transformed.

bye
-crono (March 03, 2009, 05:12 PM)

hi crono
that's working now

thanks!

the original script didnt work for me with ";" as seperator either (just tried today) so happy to get this one
many thanks again, tom

Author Topic: Converting Spreadsheet to Table in forum (Read 41024 times)

tomos

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

crono

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

David1904

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

David1904

Re: Converting Spreadsheet to Table in forum

AbteriX

Re: Converting Spreadsheet to Table in forum

AbteriX

Re: Converting Spreadsheet to Table in forum

brett

Re: Converting Spreadsheet to Table in forum

AbteriX

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

crono

Re: Converting Spreadsheet to Table in forum

Target

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum

crono

Re: Converting Spreadsheet to Table in forum

tomos

Re: Converting Spreadsheet to Table in forum