Macro for converting text to calc with specific format

[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

[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).

[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

[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 

[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

[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.

[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.