Apache OpenOffice Community Forum

User community support forum for Apache OpenOffice, LibreOffice and all the OpenOffice.org derivatives
https://forum.openoffice.org/en/forum/

Macro for converting text to calc with specific format

https://forum.openoffice.org/en/forum/viewtopic.php?t=64341

Page 1 of 1

Macro for converting text to calc with specific format

Posted: Tue Sep 17, 2013 6:32 am
by cuwid
Hi,
i have problems to convert text to calc using macro. I want to convert text file to calc with specific format. I have text file below.

000001/001 25/05/05 1657.88
000002/001 25/05/05 2,4910.63
000012/003 19/04/05 0.10
000016/002 20/04/05 7,5000.00
000016/006 20/04/05 13,5000.00
000021/003 20/04/05 7000.00
000021/004 20/04/05 32,5000.00

Before using Open Office, i use excel macro to convert the text file to xls. And the text file above become like below (on excel).

000001/001 25/05/05 1657.88
000002/001 25/05/05 24,910.63
000012/003 19/04/05 0.10
000016/002 20/04/05 75,000.00
000016/006 20/04/05 135,000.00
000021/003 20/04/05 7000.00
000021/004 20/04/05 325,000.00

On the third column, excel correcting the thousands separator (comma) to the right position (without any additional step). But on Open Office, it don't do anything to correct the thousands separator. This is my macro code using Open Office Basic :

Code: Select all

Sub ImportText()

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 = "Select File"
 .setDisplayDirectory( ConvertToURL( "C:\") )
End With

If oFileDialog.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
    sUrl = oFileDialog.Files(0)

    Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
    FileProperties(0).Name = "FilterName"
    FileProperties(0).Value ="Text - txt - csv (StarCalc)"
    FileProperties(1).Name = "FilterOptions"
    FileProperties(1).Value = "FIX,,0,1,0/2/10/36/19/4"

    oCSV = StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, FileProperties())
        
    oSourceSheet = oCSV.Sheets( 0 )
    osourcesheet.name="SheetName"
End If

End Sub
Please help me to solve my problems.
I am sorry for my bad english

Re: Macro for converting text to calc with specific format

Posted: Tue Sep 17, 2013 8:38 am
by lino
Workaround:
Have you consider opening the file in text editor and removing comas? Or even writing a simple script to do it? In this case it would work.
When you import it afterwards, it's a column with numbers (if your locale uses decimal point, not coma).

Re: Macro for converting text to calc with specific format

Posted: Wed Sep 18, 2013 10:35 am
by cuwid
Thank you for the reply lino.
You're absolutly right. I can open text editor and removing the comma. But it means there is additional step to do. I want to do the job with a simple click.
Actualy, I'm still beginner in programming. But now I can solve my problem with this code :

Code: Select all

Sub ImportText()

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 = "Select File"
.setDisplayDirectory( ConvertToURL( "C:\") )
End With

If oFileDialog.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
    sUrl = oFileDialog.Files(0)

    Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
    FileProperties(0).Name = "FilterName"
    FileProperties(0).Value ="Text - txt - csv (StarCalc)"
    FileProperties(1).Name = "FilterOptions"
    FileProperties(1).Value = "FIX,,0,1,0/2/10/36/19/4"

    oCSV = StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, FileProperties())
        
    oSourceSheet = oCSV.Sheets( 0 )
    osourcesheet.name="SheetName"
End If

dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = ","
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = ""
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())

End Sub
But there is one more problem. The code is working only if i put the module on My Macros. I think its because when i run the macro, it open new workbook (new file) so the macro won't run on the new file if i put the module on my current file.
In this condition, if I want to copy the file to another computer, I must install the module so the macro can run.
I want to put the macro module above to my file so if I copy the file i don't need to install the module to the new computer and a can use the macro.
I still need a help :D

Re: Macro for converting text to calc with specific format

Posted: Wed Sep 18, 2013 10:45 am
by Villeroy
Open the file manually and be verbose. Fill out each and every useful option of the text import dialog, the language and "special numbers" in particular.
If dates are dates, decimals are decimals, text is correctly decoded and all the columns are in perfect shape run the following macro and copy the filter options from the box:

Code: Select all

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

Re: Macro for converting text to calc with specific format

Posted: Thu Sep 19, 2013 7:38 am
by cuwid
Thanks villeroy.
I have that code before, but I don't know how to use it. Now I know it. You inspiring me.
Thanks dude.
But it still haven't solving my problem yet :(

Re: Macro for converting text to calc with specific format

Posted: Thu Sep 19, 2013 7:51 am
by FJCC
When you open the document, you get a reference to it in your variable oCSV. Use that instead of ThisComponent, which refers to the document from which the macro was called.

Re: Macro for converting text to calc with specific format

Posted: Thu Sep 19, 2013 12:04 pm
by Villeroy
With the help of the Base component and a couple of lines of very simple Basic code your import and filtering problem may be solvable within minutes. Spreadsheets are not designed to work like databases even if millions of spreadsheet users try very hard to overstretch spreadsheet programs in this way.

In this Basic hell, ThisComponent refers to the document where the code is stored in.
If the code is stored in "My Macros", ThisComponent refers to the currently active document.
The return value of loadComponentFromURL refers to the successfully loaded component.
All times are UTC+02:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited