[Solved] Macro to convert Text To Column

Creating a macro - Writing a Script - Using the API

[Solved] Macro to convert Text To Column

Postby nelkasse » Tue Mar 05, 2013 5:02 pm

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   Expand viewCollapse view
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
nelkasse
 
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Postby FJCC » Wed Mar 06, 2013 6:04 am

Rather than use the Text To Columns function, I would just open the text file directly with code like this
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Propval(1).Value ="124,34,0,1,1"

is explained in the CSV Filter section of this web page
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: 7192
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to convert Text To Column

Postby nelkasse » Wed Mar 06, 2013 12:35 pm

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   Expand viewCollapse view
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
nelkasse
 
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Postby FJCC » Wed Mar 06, 2013 3:16 pm

Yes, it is possible to tell the import process what type of data are in each column. The last parameter of
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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.
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: 7192
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to convert Text To Column

Postby nelkasse » Wed Mar 06, 2013 3:58 pm

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
nelkasse
 
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am

Re: Macro to convert Text To Column

Postby Villeroy » Wed Mar 06, 2013 4:07 pm

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

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26858
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to convert Text To Column

Postby nelkasse » Thu Mar 07, 2013 4:09 pm

Thank you for your added value Velliroy!
OpenOffice 3.4.1 on Windows 7
nelkasse
 
Posts: 16
Joined: Mon Mar 04, 2013 10:38 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests