cancel
Showing results for 
Search instead for 
Did you mean: 

Busco solución - Datos de cada celda se juntan con ";" en una misma celda.

Daniel_MarianoR
Level 3
Estimados:

Necesito guardar en una colección los datos de un archivo excel de forma correcta.

Este es el archivo cuando lo abro de manera manual:
5483.png

Y este es el archvo cuando lo abro con BP:
5484.png

Como pueden ver, los datos de cada celda se juntan con ";" en una misma celda.

¿Cómo puedo solucionar este problema?

Gracias.



------------------------------
Daniel Mariano Ruiz
------------------------------
5 REPLIES 5

Hi Daniel
It looks like the text is being compressed to a single column delimited with a semi colon. There is an excel function text to columns that will resolve the issue but you need to create a new action in the excel VBO to do this. I've provided the code I use below and a screenshot of the inputs, hope this helps.
Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim matrix = New Integer (3, 1) {{1, 1}, {2, 1}, {3, 1}, {4, 1}}
Try
wb = GetWorkbook(handle, Source_Workbook)
ws = GetWorksheet(handle, Source_Workbook, Source_Worksheet)
Wb. Activate()
Ws. Activate()
excel = ws. Application
sheet = excel. ActiveSheet
range = sheet. Range(CellRange)
range. Select()
Excel. DisplayAlerts = False
Excel. Selection.TextToColumns (Destination:=sheet. Range(StartCell), DataType:=1, _
        TextQualifier:=Qualifier, ConsecutiveDelimiter:=Delimiter, Tab:=Tab, _
        Semicolon:=SemiColon, Comma:=Comma, Space:=Space, Other:=Other, OtherChar _
        :=Split_Char, FieldInfo:=matrix, _
        TrailingMinusNumbers:=True)
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
End Try

5462.png


------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

Hola Michael.

Muchas gracias por la respuesta 🙂

Acabo de crear un nuevo objeto de VBO Excel con una nueva Action: Separar en Columnas (y copié todo lo que me dijiste).


Tengo que poner algo en Input u output?
5465.png

Está bien que coloque esa action ahí, post recolección de info?
5466.png


Gracias.


------------------------------
Daniel Mariano Ruiz
------------------------------

Hi Daniel

The inputs for the action should match the inputs for the code stage you created. This is where you pass in all the information required for the action to complete. In your case you need to provide the the handle, workbook, worksheet, cellrange (this is where the data is you need to split for example A1:A12), StartCell which should be the first row of the data range e.g. A1. The rest are mostly optional but you may want to set the Consecutive delimiter to false for Other set this as your delimiter ";". You should also provide the outputs for the code stage and action as Success and Message so you can check if there were any errors. If you are unsure about using this action you should try out the Text to Columns option within excel itself first to get a better understanding of the inputs of the vbo as this will match what excel does/requires. Also the split text to columns action should be before your Get worksheet as colleciton action.


------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

Daniel_MarianoR
Level 3
OK Michael.
I try to solve this with all solutions provided.

You comment that: "If you are unsure about using this action, you should first try the Text to Columns option within Excel to better understand the vbo inputs, as this will match what Excel does/requires."

However, I don't see an Action called "Text to Columns" in the MS Excel VBO object. How do I get it?

5475.png


Thanks again.

------------------------------
Daniel Mariano Ruiz
------------------------------

Hello Daniel 

Michael is trying to say that Open Excel on your system Manually and try to use text to column feature so that you will understand the Code shared above.

------------------------------
Neeraj Kumar
Technical Architect
------------------------------