[Solved] Macro to convert Text To Column

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
nelkasse
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

[Solved] Macro to convert Text To Column

Post by nelkasse »

Hi,

I would like to ask if anybody knows how to use the TextToColumns functionality to import or convert Text to Columns. I know how it works manually but I need it to work within a Macro. The user has a very big plain file with "|" as a delimiter. So, with only one click, he needs to import the file and apply all the required changes, formulas, colores, etc.

I have tried this with the next Macro (a simplified one), but it does not work. The import dialog pops up where I want the task to be done silently and without the intervention of the user. I wanted to pass the arguments as Others = "|", etc. by modifying the properties before calling the function, but it does not take any parameter into account.

Do you know if this can be implemented.

Regards,

Nemer

Code: Select all

sub quitarPipe2
dim oDocFrame as object
dim oDispatchHelper as object
oDocFrame = ThisComponent.CurrentController.Frame
oDispatchHelper = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatchHelper.executeDispatch( oDocFrame,".uno:TextToColumns", "_self", 0,Array(MakePropertyValue( "Otros", "|" ) ) 
DispatchHelper.executeDispatch( oDocFrame,".uno:TextToColumns", "_self", 0,Array(MakePropertyValue( "Ohers", "|" ) ) 
end sub

Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue 
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" ) 
If Not IsMissing( cName ) Then 
oPropertyValue.Name = cName 
EndIf 
If Not IsMissing( uValue ) Then 
oPropertyValue.Value = uValue 
EndIf 
MakePropertyValue() = oPropertyValue 
End Function
Last edited by nelkasse on Thu Mar 07, 2013 4:10 pm, edited 1 time in total.
OpenOffice 3.4.1 on Windows 7
Top
FJCC
Moderator
Posts: 9274
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to convert Text To Column

Post by FJCC »

Rather than use the Text To Columns function, I would just open the text file directly with code like this

Code: Select all

Sub CSVImport

Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="124,34,0,1,1"   'ASCII  124 = |
NombreArchivo = "C:\Documents and Settings\username\Desktop\Archivo.txt"  
ArchivoURL = convertToURL(NombreArchivo)
oCSV = StarDesktop.loadComponentFromURL(ArchivoURL, "_blank", 0, Propval())

End Sub
The meaning of the line

Code: Select all

Propval(1).Value ="124,34,0,1,1"
is explained in the CSV Filter section of this web page
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Top
nelkasse
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Post by nelkasse »

Hi,

Thank you very much for the prompt and Very useful help.
I have tested it, and although it does not exactly do what I wish to do, it is a very good Wrok-Around. In my previous tries, I opened the file and read it line by line and the used the split function to separate the values in columns which resulted to be very time consuming. So, this is extremely more effective.

Using your code, I suppose I have to add some code to convert the the format of some imported columns to Numbers and the same for the dates. And that will be the definite Solution.

However, I am wondering if you can pass some parameter in the [Propval(1).Value ="124,34,0,1,1" ] to tell it to import directly some columns as numeric and others as Dates so no more code is needed for importation task. The type of the content of the columns are already defined. I.e. we know beforehand which columns are numbers and which ones are dates. There is even a first line in the file we try to import which is a sort of template (see below) to tell if the column is going to be a text(2), number(1) or a date(4).

2|2|1|1|2|2|2|2|2|2|2|1|1|1|1|2|4|2|2|4|1|2|4|2|2|2|2|2|2

I am attaching an example of what I am trying to import, perhaps this clarifies my concerns.

I did not find the clip to attach the document, so I am copying it in here (there are 30 columns).

One more question: : If it is not possible to enhance the import process by passing some more parameters to it, is it possible to construct some type of template and hence import the data according to it?

Thanks in advance,
Nemer

Code: Select all

DATOS DE PEDIDOS   -  68/06/6013   16:56
Proye.|Descripción|Orden|Posición|Tipo orden|Almacén|Proveedor|Nombre Proveedor|Artículo|Edición|Descripción|Cant. pedida|Cant. recibida|Cant. rechazada|Retro-orden|UM|Plazo|Familia|Comp.Costo|Fecha orden|Línea compra|CCN|Fecha última recepción|Pedido transformación|Estatus Plazo|País Proveedor|Código postal|Gama|Ed.Gama
  61733|60 GMUs GIR                   |630000|0001|KAG|ALZ| 16113|MHECSA - MATERIALES Y HERRAMIEGTAS |CE06390066      |     0|*AREGA                        |       600,00|       600,00|         0,00|         0,00|KG |16/10/2011|    38|E63|18/10/2011|000013|   FGE06|17/10/2011|GO|Estimado| ES FRANCIA                        |50013     |           |  
  64773|7 AGGgies Repuesto     Giesel  |630001|0001|GRP|GOL| 10067|ABC, S.L.                      |MA40501601      |     0|SUPLEMEGGK                    |       688,00|       688,00|         0,00|         0,00|UG |08/10/2013|    09|E05|11/10/2011|000043|        |10/09/2012|GO|Estimado| ES FRANCIA                        |01015     |           |  
  64747|7 AGGgies Repuesto Reg.Giesel  |630001|0067|GRP|GOL| 10067|ABC, S.L.                      |MA48403801      |     0|*UOSE CAJA (PIGTAGO)          |         6,00|         6,00|         0,00|         0,00|UG |18/06/2013|    09|E84|11/10/2011|000043|        |14/06/2012|GO|Estimado| ES FRANCIA                        |01015     |           |  
  64747|7 AGGgies Repuesto Reg.Giesel  |630001|0068|GRP|GOL| 10067|RETES, S.L.                      |MA48403801      |     0|*UOSE CAJA (PIGTAGO)          |         1,00|         1,00|         0,00|         0,00|UG |17/09/2013|    09|E84|11/10/2011|000043|        |03/09/2012|GO|Estimado| ES FRANCIA                        |01015     |           |  
  64747|7 AGGgies Repuesto Reg.Giesel  |630006|0001|GRP|GOL| 53678|TEST, S.L.            |MA44300600      |     A|*COGJUGGK ESTRIBO (PIGTAGO)   |         3,00|         6,00|         3,00|         0,00|UG |09/11/2013|    09|E43|11/10/2011|000043|        |63/01/6013|GO|Estimado| ES FRANCIA                        |60616     |           |  
  647367|7 AGGgies Repuesto Reg.Giesel  |630003|0001|GRP|GOL| 60456|LASER TEST, S.L.                 |MA46606601      |     0|ABRAZAGERA                    |        16,00|        16,00|         0,00|         0,00|UG |14/09/2013|    09|E66|11/10/2011|000004|        |19/09/2012|GO|Estimado| ES FRANCIA                        |60613     |           |  
  64377|7 AGGgies Repuesto Reg.Giesel  |630003|0015|GRP|GOL| 60456|LASER ABC, S.L.                 |MA48100401      |     0|REFUERZO TERMIGAL             |         1,00|         1,00|         0,00|         0,00|UG |17/09/2013|    09|E81|11/10/2011|000004|        |19/09/2012|GO|Estimado| ES FRANCIA                        |60613     |           |  
  64377|7 AGGgies Repuesto Reg.Giesel  |630003|0016|GRP|GOL| 60456|ALL LASER , S.L.                 |MA48100501      |     0|REFUERZO TERMIGAL             |         1,00|         1,00|         0,00|         0,00|UG |17/09/2013|    09|E81|11/10/2011|000004|        |19/09/2012|GO|Estimado| ES FRANCIA                        |60613     |           |  
  63477|7 AGGgies Repuesto Reg.Giesel  |630005|0001|GRP|GOL| 91605|U.Z. S.COOP                        |MA48410100      |     0|COGJUGGK SOPORTE CABLE        |         3,00|         3,00|         0,00|         0,00|UG |19/10/2013|    09|E84|11/10/2011|000043|        |14/10/2013|GO|Estimado| ES FRANCIA                        |60700     |           |  
  6468|656 AGGgies Metro Chile        |630006|0006|GRP|GOL| 63965|MELATLICA, S.L.                     |MC00201301      |     0|ASIEGGK OSCILAGTE             |     1.156,00|     1.156,00|         0,00|         0,00|UF |07/06/2013|08.1BG|E06|11/10/2011|000006|        |04/06/2013|GO|Estimado| ES FRANCIA                        |60700     |           |  
  6468|656 AGGgies Metro Chile        |630006|0003|GRP|GOL| 63965|Heavy Metal, S.L.                     |MC00201301      |     0|ASIEGGK OSCILAGTE             |     1.156,00|     1.156,00|         0,00|         0,00|UF |17/08/2013|08.1BG|E06|11/10/2011|000006|        |01/08/2013|GO|Estimado| ES FRANCIA                        |60700     |           |  
  6405|30 UTE EuskotreG              |630007|0014|GRP|AL6| 64017|CHampaG, S.A.                     |G568300500      |     B|*COGJUGGK COFRE UOTERIAS      |         6,00|         0,00|         0,00|         0,00|UG |03/06/6013|    09|E83|11/10/2011|000004|        ||SI|Estimado| ES FRANCIA                        |01015     |           |  
  1405|30 UTE EuskotreG              |630007|0015|GRP|AL6| 64017|Chapas, S.A.                     |G568300500      |     B|*COGJUGGK COFRE UOTERIAS      |         6,00|         0,00|         0,00|         0,00|UG |04/07/6013|    09|E83|11/10/2011|000004|        ||SI|Estimado| ES FRANCIA                        |01015     |           |  
  1405|30 UTE EuskotreG              |630007|0016|GRP|AL6| 64017|CHAPAS, S.A.                     |G568300500      |     B|*COGJUGGK COFRE UOTERIAS      |         6,00|         0,00|         0,00|         0,00|UG |06/09/6013|    09|E83|11/10/2011|000004|        ||SI|Estimado| ES FRANCIA                        |01015     |           |  
  6661|Prototipo TreG 300 K/h        |630008|0001|GRP|GOL| 71568|CAFE SPAIG, S.A.U.              |CC77916100*     |     0|*COGVERT.76/16 SPOTS PLAT.75W |        64,00|        64,00|         0,00|         0,00|UG |14/11/2011|    65|E79|11/10/2011|000007|        |07/16/2011|GO|Estimado| ES FRANCIA                        |08907     |           |  
  9150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0001|LAT|GOL| 65151|FERROCARRILES S.L.        |BOGOTG101       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|13/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  1150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0066|LAT|GOL| 65151|FERROCARRILES  S.L.        |BOGOTG166       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|14/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  9150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0063|LAT|GOL| 65151|FERROCARRILES  S.L.        |BOGOTG163       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|14/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  9150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0038|LAT|GOL| 65151|cIGEAS S.L.        |BOGOTG138       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|14/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  91150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0039|LAT|GOL| 65151|CIGEAS S.L S.L.        |BOGOTG139       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|14/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  1150|PIECERIO GIVERSO GIV.COMPOGEG.|630009|0041|LAT|GOL| 65151|CIGEAS S.L  S.L.        |BOGOTG141       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|    99|GEG|14/10/2011|000008|        |18/10/2011|GO|Estimado| ES FRANCIA                        |60400     |           |  
  0150|MATER. MAGUOGIM. COMPOGEGTES  |630010|0001|MAG|GOL| 60400|IGFOR                 |9780009104      |     0|EMPAQ.MERKEL 1160-014.609     |         4,00|         4,00|         0,00|         0,00|UF |11/10/2011|   GEG|E00|11/10/2011|000039|        |14/10/2011|GO|Estimado| ES FRANCIA                        |60018     |           |  
  01150|MATER. MAGUOGIM. COMPOGEGTES  |630011|0001|MAG|GOL| 50088|GEGERALITAT, S.L                      |9714101115      ||Acop. BK 6 060/83 36/68H7     |         6,00|         6,00|         0,00|         0,00|UF |11/10/2011|    77|E00|11/10/2011|000039|        |06/11/2011|GO|Estimado| ES FRANCIA                        |20115     |           |  
  96036|48+6 Reductoras VeGecia6438   |630016|0001|GRP|ALR| 34347|EMMMA, S.A.                      |THTH50060101      |     0|VISOR TAPA IGSPECCIOG 100X130 |         1,00|         1,00|         0,00|         0,00|UG |11/10/2011|    09|E11|11/10/2011|000044|        |07/11/2011|GO|Estimado| ES FRANCIA                        |48480     |           |  
  96306|48+6 Reductoras VeGecia6438   |630016|0006|GRP|ALR| 34347|EMAAL, S.A.                      |THTH50060701      |     0|VISOR G180                    |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|  18.1|E11|11/10/2011|000044|        |07/11/2011|GO|Estimado| ES FRANCIA                        |48480     |           |  
  96306|48+6 Reductoras VeGecia6438   |630016|0010|GRP|ALR| 34347|MARCEL, S.A.                      |THTH50061601      |     0|VISOR 190X136                 |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|  18.1|E11|11/10/2011|000044|        |07/11/2011|GO|Estimado| ES FRANCIA                        |48480     |           |  
  93606|48+6 Reductoras VeGecia6438   |630016|0011|GRP|ALR| 34347|GEMER, S.A.                      |THTH50061301      |     0|VISOR 131X65º                 |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|  18.1|E11|11/10/2011|000044|        |07/11/2011|GO|Estimado| ES FRANCIA                        |48480     |           |  
  9606|48+6 Reductoras VeGecia6438   |630016|0016|GRP|ALR| 34347|EMACRYL, S.A.                      |THTH50061501      |     0|VISOR 113X106                 |         1,00|         1,00|         0,00|         0,00|UF |11/10/2011|  18.1|E11|11/10/2011|000044|        |07/11/2011|GO|Estimado| ES FRANCIA                        |48480     |           |  
  01350|MATER. MAGUOGIM. COMPOGEGTES  |630013|0001|LAT|GOL| 34910|EUROMASTER AUGKMOCIOG Y SEVICIOS SA|BOGOTG101       ||DEMANDS divisióG 1            |         1,00|         1,00|         0,00|         0,00|UF |16/10/2011|    99|GEG|13/10/2011|000039|        |13/10/2011|GO|Estimado| ES FRANCIA                        |60616     |           |  
  61534|30 TreGes Civia IV            |630014|0001|GRP|ALZ| 85406|IGAUXFERR, S.L.                    |C714711801      |     0|SOPORTE                       |        60,00|        60,00|         0,00|         0,00|UF |10/10/2011|    09|E47|13/10/2011|000015|        |14/10/2011|GO|Estimado| ES FRANCIA                        |50171     |           |  
  61534|30 TreGes Civia IV            |630014|0006|GRP|ALZ| 85406|IGAUXFERR, S.L.                    |C713600300      |     0|SOPORTE GIRO SUPERIOR         |         1,00|         1,00|         0,00|         0,00|UF |06/11/2011|    08|E36|19/10/2011|000015|        |14/10/2011|GO|Estimado| ES FRANCIA                        |50171     |           |  
  80130|EJES MOGTAGOS SIG CAJAS GRASA |630015|0001|KAG|GOL| 10561|SOFICOR-MAGER                      |5040000607      |     0|*PIG. PRIMARIA SC5080708      |       631,00|       631,00|         0,00|         0,00|KG |18/10/2011|    66|E00|13/10/2011|000008|  660856|16/10/2011|GO|Estimado| FR FraGce                        |          |           |  
  10034|8+6 Locomotoras Arabia Saudi  |630016|0001|GRP|ALI| 11849|FERVILOR-MICHELS, S.A.             |B175008001      |     0|MARCO                         |         1,00|         1,00|         0,00|         0,00|UG |18/10/2011|    09|E50|13/10/2011|000014|        |03/11/2011|GO|Estimado| ES FRANCIA                        |01015     |           |  
  66934|48 TreGes Metro Caracas       |630017|0001|GRP|AL6| 60456|LASER GOIKEG, S.L.                 |CG93604901      |     A|CALA E=5                      |         6,00|         6,00|         0,00|         0,00|UG |10/06/2013|    10|E36|13/10/2011|000004|        |07/06/2013|GO|Estimado| ES FRANCIA                        |60613     |           |
Last edited by nelkasse on Wed Mar 06, 2013 3:23 pm, edited 1 time in total.
OpenOffice 3.4.1 on Windows 7
Top
FJCC
Moderator
Posts: 9274
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to convert Text To Column

Post by FJCC »

Yes, it is possible to tell the import process what type of data are in each column. The last parameter of

Code: Select all

Propval(1).Value ="124,34,0,1,1"
serves that purpose, as explained at the end of the web page that in linked in my first response. For example, if I changed the line to

Code: Select all

Propval(1).Value ="124,34,0,1,1/4/2/1/3/2"
that would tell the filter that column 1 is of type 4 (a date formatted DD/MM/YY), column 2 is of type 1 (Standard) and column 3 is of type 2 (text). Please see the web page for the full description.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Top
nelkasse
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Post by nelkasse »

Thanks again for your help. I really appreciate it. I forgot to look on correctly on the first answer and I apologise for that.

I also have to rectify and tell you that even with your first version, the thing would work correctly. It was also my fault that I did not remember some days before, I had changed my current Region to UK while I am in Spain and the file has a Spanish format. So, turning back the settings to its initial one, and without making any more changes, it happily worked.

I am really very grateful for your help. I have been struggling with for more than a couple of weeks :-( and now it seems to be resolved :-)
Last edited by nelkasse on Wed Mar 06, 2013 5:34 pm, edited 1 time in total.
OpenOffice 3.4.1 on Windows 7
Top
User avatar
Villeroy
Volunteer
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to convert Text To Column

Post by Villeroy »

Import the file manually with the following options:
Character set: Unicode
Language: Spanish(Spain)
Column delimiter: |
Text delimiter: <empty>
Detect special numbers: YES

The red options are most important even though nobody takes any notice of them.
The Spanish(Spain) language will import comma decimals (1,00) and D/M/Y dates correctly.
The "special numbers" option converts all date text, time text, currency text (€), percent text and boolean text into spreadsheet numbers so the spreadsheet can actually sort, filter and calculate accurate values.

Then run the following macro with the imported document being active document:

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
The macro will show an input box from where you can copy the FilterOptions string.
Last edited by Villeroy on Thu Mar 07, 2013 6:02 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Top
nelkasse
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Post by nelkasse »

Thank you for your added value Velliroy!
OpenOffice 3.4.1 on Windows 7
Top
Post Reply

Return to “Macros and UNO API”