Macro for converting text to calc with specific format

Creating a macro - Writing a Script - Using the API

Macro for converting text to calc with specific format

Postby cuwid » Tue Sep 17, 2013 6:32 am

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   Expand viewCollapse view
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
Last edited by RoryOF on Tue Sep 17, 2013 6:40 am, edited 1 time in total.
Reason: Removed green tick, as this is reserved for successfully completed threads.
OOo 3.2 on Windows 7
cuwid
 
Posts: 3
Joined: Tue Sep 17, 2013 1:59 am

Re: Macro for converting text to calc with specific format

Postby lino » Tue Sep 17, 2013 8:38 am

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).
User avatar
lino
Volunteer
 
Posts: 312
Joined: Thu Mar 28, 2013 11:16 am
Location: http://goo.gl/maps/rsfPZ

Re: Macro for converting text to calc with specific format

Postby cuwid » Wed Sep 18, 2013 10:35 am

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   Expand viewCollapse view
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
OOo 3.2 on Windows 7
cuwid
 
Posts: 3
Joined: Tue Sep 17, 2013 1:59 am

Re: Macro for converting text to calc with specific format

Postby Villeroy » Wed Sep 18, 2013 10:45 am

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   Expand viewCollapse view
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
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: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro for converting text to calc with specific format

Postby cuwid » Thu Sep 19, 2013 7:38 am

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 :(
OOo 3.2 on Windows 7
cuwid
 
Posts: 3
Joined: Tue Sep 17, 2013 1:59 am

Re: Macro for converting text to calc with specific format

Postby FJCC » Thu Sep 19, 2013 7:51 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7790
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro for converting text to calc with specific format

Postby Villeroy » Thu Sep 19, 2013 12:04 pm

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.
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: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 11 guests