[Solved] Macro to convert CSV to doc with tables

[gr85z]

We are working on getting data from CVSS (Common Vulnerability Security Scans)and take each host and findings from a CSV file and then take all findings from each host and do the following
In the header of Document have Information pertaining to scan time /date/etc/...

List the host IP/ Hostname
CVSS, Severity,Solution Type, Summary
Attached is sample CSV and imported into Calc, I am not fluent in Macros or if it is possible
Over all goal is to take the out put open in Calc run marco produce document we can sent to management.
Overall end product will be to have files land in a folder and run script against folder and do all the conversation etc.... with end result of document.

Thanks in advance for any input.

[Villeroy]

Collect the text files in a dedicated directory.
menu:File>New>Database...
[X] Connect to existing database.
Type: Text
Specify the import details (directory, encoding, separators etc)
[X] Register the database
Save the database document (*.odb)
The database document does not store any data. Your data are still in the text files.
Each text file is represented as one table of this pseudo-database.
You can add queries to select any subset of rows and columns in any order of rows and columns.
From tables and queries you can create reports which are text documents with database data embedded in the Base document.
You can copy tables and queries from the data source window into Writer and Calc documents. Just drag the icon of a table/query into your document.

[gr85z]

That works great for query and am able to generate a report but the form comes back with weird characters (see attached) even though query shows fine.
I am using the report wizard
Selecting my fields.
Labeling
Then grouping the results by IP/Hostname so end product should show single IP/Hostname and results below.
I then sort by severity so highest should be at the top under the IP/Hostname

I then finish report and I have tried just bringing it all over and still same results of only the one entry with garbage text.

Once I get this done I will have to figure out how to have it automated as much as possible. We will be generating these reports quarterly with hundreds of scan results.

Ideally would like to be able to open a calc template
Have a box to open and file it imports the CSV file and does all the separation of the data wee need since Column headers are the same. Then outputs that into a document with our default information on top and results at bottoom. Similar to what nmon reports would do.

[UnklDonald418]

That appears to be a report template generated by the report writer built into OO, which is quite rudidmentary. The Latin text shown is merely a place holder. Your screen shot leaves off the information line at the very top; it should display the database name, the report name and end with OpenOffice Base: Report Design indicating you are still in the design mode.
From there, either select File>Reload or exit the Report Design document and double click on the report to merge the data.

For a more versatile report writer download and install the
Oracle Report Builder Extension
LO has abandoned that old report writer and uses the ORB by default.

I don't believe you need a macro when using a Text connection in OO Base.
If the CSV files are always formatted the same simply replace the old CSV file with a new one using the same name.
Open the Base Database and run the report.
If you want to keep archival copies, simply rename the old CSV file before copying the new one into the directory. Then all the archival text files will be available as Tables in the Database.
Also, if you make any changes to the CSV files while Base is open, select Tables on the left of the window then from the menu View>Refresh tables

[gr85z]

That will not work renaming files each time I need this to be as automated as possible to allow for resources to be used more productive.
My end result should be drop files in folder - run script/macro / something - gather data and create the document. If I end up having to rename and run report I could be a few days with as many reports as we generate.
Also I tried the report page I was not in design mode I tried to reload and everything still did same thing. I am still looking at any other options at this point.

[Villeroy]

Write a script to rename the right file to the right name. Open the report. Done. We are not here to do your programming work.

[gr85z]

Not asking to to do programming work I can script the file names etc...
I just need to get the report into document in the format.
Thanks for assistance I will continue to try and figure out.

[JeJe]

Not thoroughly tested but this is how you might do this in OOBasic from scratch: Add the code to a blank document, change the path to your csv file URL, and choose which columns you want displayed

Code: Select all

Sub AddCSVToTable


'edit this bit
	Url = "C:\tmp\report-sample.csv" 'change to your csv url	
	DIM ColumnIndexes(3) 'pick the number columns you want
	ColumnIndexes(0) =3 ' and the index of original column for the new table column
	ColumnIndexes(1) =5
	ColumnIndexes(2) =2
	ColumnIndexes(3) =6
'/edit this bit

	otable = createtable (ubound(ColumnIndexes) +1 ,1)

	' stream bit based on https://forum.openoffice.org/en/forum/viewtopic.php?t=74313&p=336059#p336075
	Dim oSimple As Object, oTextInput As Object 
	Dim SimpleStream As Object
	Dim inString As String
	oTextInput = createUnoService("com.sun.star.io.TextInputStream")
	oSimple = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	SimpleStream = oSimple.openFileRead(Url)
	oTextInput.setInputStream(SimpleStream)

	do until oTextInput.isEOF = true
		spara = oTextInput.readString(Array(13),true) 'get each paragraph
		If spara <>"" then
			if pno<>0 then otable.rows.insertbyindex pno,1	'add a table row
			processpara(pno,spara,ColumnIndexes,oTable) 'process the paragraph
			pno=pno+1
		end if
	loop
End Sub

sub processPara(pno,st,ColumnIndexes,oTable) 'separate paragraph into CSV items
	dim wasquote as boolean
	newitemi =1
	for i = 1 to len(st)
		ch = mid(st,i,1)
		select case ch
		case ","
			if openquote = false then
				setitem pno,c, mid(st,newitemi,i-newitemi),ColumnIndexes,oTable
				c= c+1
				newitemi =i+1
			end if
		case chr(34)
			openquote =not openquote
			if openquote then newitemi =newitemi +1
		end select
	next
	setitem pno, c, mid(st,newitemi+1,i-newitemi),ColumnIndexes,oTable
end sub

sub setitem(lno,index,value,ColumnIndexes,oTable) 'add item to table
	for i = 0 to ubound(ColumnIndexes)
		if ColumnIndexes(i) = index then
			if left(value,1) = chr(10) then mid(value,1,1)=""
			if left(value,1) = chr(34) then mid(value,1,1)=""
			if right(value,1) = chr(34) then mid(value,len(value),1)=""
			oTable.getcellbyposition(i,lno).setstring value
			exit for
		end if
	next
end sub

function createtable(cols,rows)
	vCursor = ThisComponent.CurrentController.getViewCursor ()
	oTable = ThisComponent.createInstance ("com.sun.star.text.TextTable")
	oTable.initialize (rows,cols)
	ThisComponent.Text.insertTextContent (vCursor, oTable, False)
	createtable= oTable
end function

Edit: note, will fail if one of the paragraph lengths in your CSV file is greater than OOBasic's maximum string size

[JeJe]

Slightly improved version with more generic functions and some speed improvements

Code: Select all

sub tst

	Url = "C:\tmp\report-sample.csv" 'change to your csv url
	DIM ColumnIndexes(3) 'pick the number columns you want
	ColumnIndexes(0) =3 ' and the index of original column for the new table column
	ColumnIndexes(1) =8
	ColumnIndexes(2) =9
	ColumnIndexes(3) =10

	thiscomponent.lockcontrollers
	on error goto hr

	res=GetParaArrayFromUrl (Url,arr)

	redim itmarr(res-1)
	otable = createtable (ubound(ColumnIndexes) +1 ,res)

	for pno = 0 to res-1
		ubitms = SplitCSVPara(arr(pno),itmarr) 'process the paragraph

		for i = 0 to ubound(ColumnIndexes)
			value= itmarr(ColumnIndexes(i))
			if left(value,1) = chr(10) then mid(value,1,1)=""
			if left(value,1) = chr(34) then mid(value,1,1)=""
			if right(value,1) = chr(34) then mid(value,len(value),1)=""
			oTable.getcellbyposition(i,pno).setstring value
		next
	next
hr:
	thiscomponent.unlockcontrollers

end sub

Function GetParaArrayFromUrl(Url,arr) 'get para array from file 
' split on chr(13) so when carr return line feed sequence next item first char will be line feed
	' based on https://forum.openoffice.org/en/forum/viewtopic.php?t=74313&p=336059#p336075
	Dim oSimple As Object, oTextInput As Object
	Dim SimpleStream As Object
	Dim inString As String
	oTextInput = createUnoService("com.sun.star.io.TextInputStream")
	oSimple = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	SimpleStream = oSimple.openFileRead(Url)
	oTextInput.setInputStream(SimpleStream)

	redim arr(999)
	ubarr = 1000
	ub = -1
	do until oTextInput.isEOF = true
		ub = ub +1
		if ub > ubarr then
			ubarr = ubarr +1000
			redim preserve arr(ubarr)
		end if
		arr(ub) = oTextInput.readString(Array(13),true) 'get each paragraph
	loop

	if ub<> ubarr then
		redim preserve arr(ub)
		GetParaArrayFromUrl =ub
	end if
	SimpleStream.closeInput()
End function

function SplitCSVPara(st,itmarr) 'separate paragraph into items array
	'	if there were no " to contend with we'd just use split function
	dim wasquote as boolean

	ub = ubound(itmarr)

	newitemi =1
	for i = 1 to len(st)
		ch = mid(st,i,1)
		select case ch
		case ","
			if openquote = false then
				if c>ub then
					ub = c
					redim preserve itmarr(ub)
				end if
				itmarr(c)= mid(st,newitemi,i-newitemi)
				c= c+1
				newitemi =i+1
			end if
		case chr(34)
			openquote =not openquote
			if openquote then newitemi =newitemi +1
		end select
	next
	if c>ub then redim preserve itmarr(c)
	itmarr(c)= mid(st,newitemi,i-newitemi)
	SplitCSVPara=c
end function


function createtable(cols,rows) 'insert table at viewcursor
	vCursor = ThisComponent.CurrentController.getViewCursor ()
	oTable = ThisComponent.createInstance ("com.sun.star.text.TextTable")
	oTable.initialize (rows,cols)
	ThisComponent.Text.insertTextContent (vCursor, oTable, False)
	createtable= oTable
end function

Edit: Reading Useful Macro Information For OpenOffice By Andrew Pitonyak, calling closeInput() on the TextStream may be necessary.(added)