[Solved] Multiple import csv/txt to Calc - save as ods

Creating a macro - Writing a Script - Using the API

[Solved] Multiple import csv/txt to Calc - save as ods

Postby andyroberts1234 » Mon Feb 21, 2011 11:38 am

I am trying to import and change multiple files (c.300) from .txt to .ods. I have tried using the code for single csv files (plan to change to work on multiple files and .txt when I get this working) from http://www.oooforum.org/forum/viewtopic.phtml?t=76333. The code is also below:

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
' Main routine
'
' 1. determine your CSV filter options and edit the code line
'(Search FilterOptions)

' 2. run insertCSV2Calc from your target Calc document


Sub insertCSV2Calc

sMakroName = "insertCSV2Calc "
sMakroVersion = "1.5.0 "
sMakroDatum = "20081221 "

oImport2Calc = _
StarDesktop.getCurrentComponent().getCurrentController().getModel()
' Examine whether Macro called is for a Calc Spreadsheet


' load of auxiliary functions library
GlobalScope.BasicLibraries.LoadLibrary( "Tools" )

' File dialog to select the CSV
oFileDialog = _
CreateUnoService( "com.sun.star.ui.dialogs.FilePicker" )
With oFileDialog
.appendFilter( "Text CSV (*.txt;*.csv;*.xls)", "*.txt" )
.appendFilter( "Text (*.txt)", "*.txt" )
.appendFilter( "Text Encoded (*.txt)", "*.fixcc" )
.appendFilter( "Text File (Fixed Width)", "*.fixcc" )
.Title = "006 - Copy"   REM select file to import
.setDisplayDirectory( ConvertToURL( "C:\Users\DMRC\Documents\AndyR\OpenOffice\Runonmultiplefiles\") ) REM select directory
' .setDisplayDirectory( ConvertToURL( "/zentrale/2008/data/CSVs/") )
End With

' if selected, and closed with OK
If oFileDialog.execute() = _
    com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
    ' selected file name
    sUrl = oFileDialog.Files(0)
    ' Worksheet query
    sSheetName = Inputbox( "Name of the Sheet to insert", _
    sMakroName & " -Sheet Name", "new Import data" )
    if sSheetName > "" then
        ' if available: not OK
        if oImport2Calc.Sheets().hasByName( sSheetName ) then
    MsgBox _
    "End the macro: Sheet already exists." & CHR(10) _
    & CHR(10) & "Explanation:" _
    & CHR(10) & "A Sheet with the name entered exists." _
    & CHR(10) & "Therefore no data was imported." _
    & CHR(10) & "Makro " & sMakroName & " is now finished." _
    , 48 , sMakroName & "Version " & sMakroVersion
    Exit Sub
        End If

        ' sheet with given name does not exist:
        ' check if too much sheet already there
        if oImport2Calc.Sheets().getCount() < 255 then
            oImport2Calc.Sheets().insertNewByName( sSheetName , _
             oImport2Calc.Sheets().getCount() )
        else
    MsgBox _
    "End the macro: Maximum number of sheets." & CHR(10) _
    & CHR(10) & "Explanation:" _
    & CHR(10) & "This Calc file has the maximum number of CALC sheets:" _
    & CHR(10) & "no new sheet for import can be inserted." _
    & CHR(10) & "Therefore no data was imported" _
    & CHR(10) & "Makro " & sMakroName & " is now finished." _
    , 48 , sMakroName & "Version " & sMakroVersion
    Exit Sub
           
        End If
    Else
        msgbox "Table name not known - macro ends without action" _
         ,,  sMakroName & sMakroVersion
        Exit Sub
    End If
       
    ' Object for the new sheet     
    oNewSheet = _
     oImport2Calc.Sheets().getByName( sSheetName )

    ' Determine Filter
    Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
    FileProperties(0).Name = "FilterName"
    FileProperties(0).Value ="Text - txt - csv (StarCalc)"
    FileProperties(1).Name = "44/32/MRG,34,ANSI,1,,0,false,true"
    ' FilterOptions
    '
' >>>>
' insert YOUR filter data here
' >>>>
   FileProperties(1).Value ="59/44,34,ANSI,1,"

    ' Open File
    oCSV = _
     StarDesktop.loadComponentFromURL( _
     sUrl, "_blank", 0, FileProperties())
       
    ' Identify the area of data
    oSourceSheet = oCSV.Sheets( 0 )
   
    Dim iiColumns as Long
    Dim iiRows as Long
    iiColumns = _
        iC2C_getLastUsedColumn( oSourceSheet )
    iiRows = _
        iC2C_getLastUsedRow( oSourceSheet )
       
    ' pull out all data as an array
    oSourceArea = _
     oSourceSheet.getCellRangeByPosition( _
     0, 0, iiColumns, iiRows )
       
    allData = _
     oSourceArea.getDataArray()
       
    ' Target area in the same size set
    oEndArea = _
     oNewSheet.getCellRangeByPosition( _
     0, 0, iiColumns, iiRows )
     ' purely write Data array
     oEndArea.setDataArray( allData() )
   
    ' CSV file closed 
    oCSV.close( TRUE )     
       
End If
       
End Sub

' ========================================================================
' pure: Sheet as Object
' Out: Number of the last row / column (starting from zero)
Function iC2C_getLastUsedColumn(oSheet as Object) as Integer
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
iC2C_getLastUsedColumn = aAddress.EndColumn
End Function

Function iC2C_getLastUsedRow(oSheet as Object) as Integer
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
iC2C_GetLastUsedRow = aAddress.EndRow
End Function

' =========================
' To determine the filtering options for * your * CSV file: open it
' (from OOo, with File>Open... dialogue, use the Import dialogue, '
' with all the necessary Settings), then run the following routine.
' Note the returned values (copy) and replace the ones coded above
' (search "FilterOptions")

' http://www.oooforum.org/forum/viewtopic.phtml?t=40544
' Villeroy Aug 02, 2006 12:08 am
Sub showFilterOptions
Dim args(),i%
   args() = thisComponent.getArgs
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
   next
End Sub


The code is running, but stops when it brings up the filedialog box which does not seem to set the display directory as requested. I wondered if this was due to this known issue http://www.openoffice.org/issues/show_bug.cgi?id=110141. If so, does anyone know a workaround, or a better way of achieving my objectives? If not, can anyone see what maybe the problem.
Last edited by andyroberts1234 on Thu Mar 03, 2011 5:18 pm, edited 1 time in total.
Andy

OOO 3.3.0 / Windows 7 64-bit
andyroberts1234
 
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Multiple import csv/txt to calc - save as ods

Postby rudolfo » Tue Feb 22, 2011 3:20 pm

Have you followed the advice that is given in the issue tracker?
Changing the File Dialog from OS to OpenOffice specific is done in "Tools -> Options". And then select OpenOffice.org--General in the navigation area on the left side and check the box for "Use OpenOffice.org dialogs" on the right side.
To be on the save side restart your OpenOffice and run the macro again. If it is stll not working you'd better debug your macro (read the section about "Programming with OpenOffice.org Basic" in the online help). For a quick test a
MsgBox( ConvertToURL("C:\Users\DMRC\Documents\AndyR\OpenOffice\Runonmultiplefiles\") )
in the with ... end should give you some first insight, as well.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Multiple import csv/txt to calc - save as ods

Postby andyroberts1234 » Thu Feb 24, 2011 11:23 am

Rudolfo,

Thanks for your suggestions, I wasn't sure how to implement the minimal code given in the issue tracker into my code. However, changing the file dialog to OOSpecific seems to be a good way forward- I'm still coming up with some errors, but will try and debug before posting again (probably early next week).

Cheers
Andy

OOO 3.3.0 / Windows 7 64-bit
andyroberts1234
 
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Multiple import csv/txt to calc - save as ods

Postby Villeroy » Thu Feb 24, 2011 1:49 pm

csv is a database exchange format. It is much easier to handle csv import in Base. Once you have it in Base you have it in any other office document as well. In the user interface it's just a drag&drop.
You can collect all text files with equal import parameters in one directory, connect a database document to that directory and never have to deal with any import dialogs, filter options, nor file paths. Each file in that directory is a member of a table collection in a pseudo-database.
The following macro simply reads the table names from a registered datasource (Const cSource), loads a new spreadsheet, adds tables as required, renames the sheets according to the table names, creates one import descriptor for A1 on each sheet and imports (links) each table to the corresponding sheet.
The exact same code imports arbitrary database tables from csv, mail addresses, dBase, MySQL, PostgreSQL, OracleDB, MS Access, MS SQL,... It does even work with databases connected to other spreadsheets.
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
REM replace with your actual data source name:
Const cSource = "Mail Adressen"

Sub Main
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDatabaseContext.getByName(cSource)
REM GetConnection("user","password") for a real database log-in:
oConnection = oDataSource.GetConnection("","")
'mri oConnection
oTables = oConnection.getTables()
oElementNames() = oTables.getElementNames()
doc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array())
shx=doc.getSheets()
'mri shx
dbx=doc.DatabaseRanges
'mri dbx
for i = shx.getCount() to uBound(oElementNames())
   shx.insertNewByName("NewSheet_"& i, i+1)
next
for i = 0 to shx.getCount()-1
   sn = oElementNames(i)
   sdb = "Import_"& i
   shx.getByIndex(i).setName(sn)
   addr = createUnoStruct("com.sun.star.table.CellRangeAddress")
   addr.Sheet = i
   if not dbx.hasByName(sdb) then dbx.addNewByName(sdb, addr)
   dbr = dbx.getByName(sdb)
   a() = getNewImportDescriptor(cSource, sn, com.sun.star.sheet.DataImportMode.TABLE)
   rg = dbr.getReferredCells()
   rg.doImport(a())
   REM unlink the database range if you like:
   ' dbx.removeByName(sdb)
next
oConnection.close()
End Sub
Function getNewImportDescriptor(src,stbl,ntype)
Dim a(2) as new com.sun.star.beans.PropertyValue
   a(0).Name = "SourceType"
   a(0).Value = ntype
   a(1).Name ="DatabaseName"
   a(1).Value = src
   a(2).Name ="SourceObject"
   a(2).Value= stbl
getNewImportDescriptor = a()
End Function


Preparations for a collection of text tables (*.csv) in one directory:
File>New>Database...
[X] Connect to existing database
Type: Text
Specify directory and import parameters
[X] Register database
Save database document which is a mere configuration file so far. It does not store any table data.
Adjust constant cSource in the Basic code to your actual datasource name.
Glitch: Certain import parameters (char set) are accessible after the database has been established. db-menu:Edit>Database>Properties...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Multiple import csv/txt to calc - save as ods

Postby andyroberts1234 » Tue Mar 01, 2011 6:35 pm

Villeroy,

That's a great solution. I did wonder about using Base, but have no experience with it at all. Thanks for the simple step by step guide. My files were separated by both a comma and a space so couldn't initially be imported into Base correctly. Luckily I found a very useful app http://www.ecobyte.com/replacetext/ that allowed me to delete all the spaces and so convert to a simple csv.

I'm just adjusting the macro to create a new document with each table rather than a new worksheet - 2 reasons really, 1- I had more tables than calc allows worksheets so the macro stopped; 2- I have a macro to go that runs on multiple files rather than multiple worksheets. Hopefully will be up and running soon.

Thanks again
Andy

OOO 3.3.0 / Windows 7 64-bit
andyroberts1234
 
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Multiple import csv/txt to calc - save as ods

Postby andyroberts1234 » Thu Mar 03, 2011 2:05 pm

I have made some adjustments to your code (below) so that it will create a new ods file for each txt file. As I'm not that quick at reading/understanding code, I've heavily remarked on it - might help others too. Only problem I'm having is that after the first file is saved, the next one stops at the import stage and comes up with a message box saying 'Protected cells can not be modified'. Clicking OK means the import does not happen, but the (empty) file is still saved. Any ideas why this would happen despite being part of a loop?

Thanks


Code: Select all   Expand viewCollapse view
Sub csvtoods   REM put all csv files into a database first

Const cSource = "Allpressurefiles"   REM replace with your actual data source name (press F4 to check) *case sensitive

oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDatabaseContext.getByName(cSource) REM defines the name of the database
oConnection = oDataSource.GetConnection("","") REM connects to the database

oTables = oConnection.getTables() REM connects to the tables in the database
oElementNames() = oTables.getElementNames() REM gets a sequence of all names in the Database


for i = 0 to uBound(oElementNames()) REM for 1st iteration to last(count of number of tables in database) iteration
   doc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array()) REM loads a new calc document
   shx=doc.getSheets()   REM returns the collection of sheets in the document.
   dbx=doc.DatabaseRanges REM provides access to the settings and options of a database range.

   sn = oElementNames(i) REM name of each table
   sdb = "Import_"& i REM Import number
   shx.getByIndex(0).setName(sn) REM gets the first sheet and gives it the same name as the table
   addr = createUnoStruct("com.sun.star.table.CellRangeAddress") REM contains a cell range address within a spreadsheet document
   addr.Sheet = i REM Sheet number i
   if not dbx.hasByName(sdb) then dbx.addNewByName(sdb, addr) REM if dbx doesn't have sdb as a name then a new one is added using sdb and addr
   dbr = dbx.getByName(sdb) REM the database table as named by sdb
   a() = getNewImportDescriptor(cSource, sn, com.sun.star.sheet.DataImportMode.TABLE) REM import details (database name, table name, dataimportmode)
   rg = dbr.getReferredCells() REM returns the cell range object that is represented by the database table
   rg.doImport(a()) REM import using the import details provided by 'a' and the range set by rg
   
   saveasURL = "file:///C:/Users/DMRC/Documents/AndyR/OpenOffice/Runonmultiplefiles/Converted Docs/" & sn & ".ods" REM URL of new .ods file with filename same as table
   Dim Dummy()   REM Empty Sequence
   doc.storeAsURL(saveasURL, Dummy())   REM Saves as
   doc.Close(True) REM closes the document
   REM unlink the database range if you like:
   ' dbx.removeByName(sdb)
next

oConnection.close()
End Sub

Function getNewImportDescriptor(src,stbl,ntype)
Dim a(2) as new com.sun.star.beans.PropertyValue
   a(0).Name = "SourceType"
   a(0).Value = ntype
   a(1).Name ="DatabaseName"
   a(1).Value = src
   a(2).Name ="SourceObject"
   a(2).Value= stbl
getNewImportDescriptor = a()
End Function
Andy

OOO 3.3.0 / Windows 7 64-bit
andyroberts1234
 
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: Multiple import csv/txt to calc - save as ods

Postby Villeroy » Thu Mar 03, 2011 5:00 pm

I have no idea how to solve this. The error and its error message is incomprehensible to me. Don't waste your time. Do the import for the first 256 elements, then for the next 256 elements until you got your job done.
First run for i = 0 to 255 ... manual save
Next run: for i = 256 to 511 ... manual save
Next run: for i = 512 to 765 ... manual save
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Multiple import csv/txt to calc - save as ods

Postby andyroberts1234 » Thu Mar 03, 2011 5:17 pm

OK, I have worked it out, simple mistake - forgot to change
Code: Select all   Expand viewCollapse view
addr.Sheet = i
to
Code: Select all   Expand viewCollapse view
addr.Sheet = 0
. After the first import, the previous syntax was trying to import into a sheet that wasn't there (I have default one sheet open for new calc docs).

The full working code is below.
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub csvtoods   REM put all csv files into a database first

Const cSource = "Allpressurefiles"   REM replace with your actual data source name (press F4 to check) *case sensitive

oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDatabaseContext.getByName(cSource) REM defines the name of the database
oConnection = oDataSource.GetConnection("","") REM connects to the database

oTables = oConnection.getTables() REM connects to the tables in the database
oElementNames() = oTables.getElementNames() REM gets a sequence of all names in the Database


for i = 0 to uBound(oElementNames()) REM for 1st iteration to last(count of number of tables in database) iteration
   doc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array()) REM loads a new calc document

   
   shx=doc.getSheets()   REM returns the collection of sheets in the document.
   dbx=doc.DatabaseRanges REM provides access to the settings and options of a database range.
   sn = oElementNames(i) REM name of each table
   sdb = "Import_"& i REM Import number
   
   shx.getByIndex(0).setName(sn) REM gets the first sheet and gives it the same name as the table
   addr = createUnoStruct("com.sun.star.table.CellRangeAddress") REM contains a cell range address within a spreadsheet document
   addr.Sheet = 0 REM Sheet number 0
   if not dbx.hasByName(sdb) then dbx.addNewByName(sdb, addr) REM if dbx doesn't have sdb as a name then a new one is added using sdb and addr
   dbr = dbx.getByName(sdb) REM the database table as named by sdb
   a() = getNewImportDescriptor(cSource, sn, com.sun.star.sheet.DataImportMode.TABLE) REM import details (database name, table name, dataimportmode)
   rg = dbr.getReferredCells() REM returns the cell range object that is represented by the database table
   rg.doImport(a()) REM import using the import details provided by 'a' and the range set by rg
   
   saveasURL = "file:///C:/Users/DMRC/Documents/AndyR/OpenOffice/Runonmultiplefiles/Converted Docs/" & sn & ".ods" REM URL of new .ods file with filename same as table
   Dim Dummy()   REM Empty Sequence
   doc.storeAsURL(saveasURL, Dummy())   REM Saves as
   doc.Close(True) REM closes the document
   REM unlink the database range if you like:
   ' dbx.removeByName(sdb)
next

oConnection.close()
End Sub

Function getNewImportDescriptor(src,stbl,ntype)
Dim a(2) as new com.sun.star.beans.PropertyValue
   a(0).Name = "SourceType"
   a(0).Value = ntype
   a(1).Name ="DatabaseName"
   a(1).Value = src
   a(2).Name ="SourceObject"
   a(2).Value= stbl
getNewImportDescriptor = a()
End Function



Many thanks for all your help.
Andy

OOO 3.3.0 / Windows 7 64-bit
andyroberts1234
 
Posts: 75
Joined: Fri Feb 11, 2011 2:29 pm

Re: SOLVED: Multiple import csv/txt to calc - save as ods

Postby Villeroy » Thu Mar 03, 2011 6:15 pm

:super:
:bravo:

You can leave the address as is. createUnoStruct creates a new struct of integers where all members are zero.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Multiple import csv/txt to Calc - save as ods

Postby tiger » Fri Aug 28, 2015 11:24 am

Hi,
Is there need programming skill for this type of program or macro?
I dont' get it how above code is working because I also want to open two different files in one macro like in excel "Text from"...
How to use this code to open both file at a time using macro
Open file1 and below open file 2 (if file1 range is A1:D50 then File 2 should be paste at A51:D...)
Any clue..?
LibreOffcie 5.1.3.2 on Window 10
tiger
 
Posts: 15
Joined: Thu May 21, 2015 1:02 pm

Re: [Solved] Multiple import csv/txt to Calc - save as ods

Postby subwoofer » Wed Nov 29, 2017 5:38 pm

Sorry for replying to this old thread, but I am desperately searching for a solution to import several csv-Files in one file.

The solution from Villeroy works very well, but I need to import all csv files in one sheet instead of separate sheets.
I tried many times to change the macro but I do not have any skills in this language. :knock:
I managed to import to sheet 0 but it gets overwritten by every csv :D

How do I find the last row and how do I start the import from there?
Is it possible to import headline only from first file?
LibreOffice 5.4.2.2(x64) on Windows 10 64-bit
subwoofer
 
Posts: 1
Joined: Tue Nov 28, 2017 6:27 pm

Re: [Solved] Multiple import csv/txt to Calc - save as ods

Postby Villeroy » Wed Nov 29, 2017 6:53 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 14 guests