[Solved] Macro to convert CSV to doc with tables

Creating a macro - Writing a Script - Using the API

[Solved] Macro to convert CSV to doc with tables

Postby gr85z » Wed Jul 01, 2020 10:53 pm

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.
Attachments
report-sample.ods
ods file
(18.06 KiB) Downloaded 23 times
report-sample.csv
CSV file
(41.02 KiB) Downloaded 24 times
Last edited by gr85z on Fri Jul 03, 2020 2:50 pm, edited 1 time in total.
OpenOffice 4.1.7
gr85z
 
Posts: 4
Joined: Wed Jul 01, 2020 10:35 pm

Re: Macro to convert CSV to doc with tables

Postby Villeroy » Thu Jul 02, 2020 7:34 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to convert CSV to doc with tables

Postby gr85z » Thu Jul 02, 2020 3:52 pm

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.
Attachments
Annotation 2020-07-02 084708.png
OpenOffice 4.1.7
gr85z
 
Posts: 4
Joined: Wed Jul 01, 2020 10:35 pm

Re: Macro to convert CSV to doc with tables

Postby UnklDonald418 » Thu Jul 02, 2020 9:24 pm

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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1322
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Macro to convert CSV to doc with tables

Postby gr85z » Fri Jul 03, 2020 2:19 am

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.
OpenOffice 4.1.7
gr85z
 
Posts: 4
Joined: Wed Jul 01, 2020 10:35 pm

Re: Macro to convert CSV to doc with tables

Postby Villeroy » Fri Jul 03, 2020 9:58 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[solved] Re: Macro to convert CSV to doc with tables

Postby gr85z » Fri Jul 03, 2020 2:50 pm

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.
OpenOffice 4.1.7
gr85z
 
Posts: 4
Joined: Wed Jul 01, 2020 10:35 pm

Re: [Solved] Macro to convert CSV to doc with tables

Postby JeJe » Sat Jul 04, 2020 12:26 am

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   Expand viewCollapse view

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Macro to convert CSV to doc with tables

Postby JeJe » Sun Jul 05, 2020 1:05 am

Slightly improved version with more generic functions and some speed improvements

Code: Select all   Expand viewCollapse view
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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests