Main Area and Open Discussion > General Software Discussion
Converting Spreadsheet to Table in forum
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.
-Hirudin (May 12, 2007, 06:29 PM)
--- End quote ---
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
--- ---@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
--- End quote ---
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 Quadrilogy016?Alien: Director's Cut: Special Edition217?Alien Quadrilogy Bonus-Disc118?Alien - Die Wiedergeburt: Special Edition219?Aliens - Die Rückkehr: Special Edition220?Alien³: Special Edition22121American Beauty12323American Psycho: Special Edition124»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:
Very nice! :Thmbsup:
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: Visual Basic ---Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _ As LongDeclare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _ As LongDeclare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _ ByVal dwBytes As Long) As LongDeclare Function CloseClipboard Lib "User32" () As LongDeclare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _ As LongDeclare Function EmptyClipboard Lib "User32" () As LongDeclare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _ ByVal lpString2 As Any) As LongDeclare Function SetClipboardData Lib "User32" (ByVal wFormat _ As Long, ByVal hMem As Long) As Long Public Const GHND = &H42Public Const CF_TEXT = 1Public 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 IfEnd FunctionSub 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 strBBTextEnd 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
-crono (May 13, 2007, 10:09 PM)
--- End quote ---
thanks, crono
will try that today.
tom
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version