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

[Daniel_MarianoR]

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:


Y este es el archvo cuando lo abro con BP:


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

[michaeloneil]

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

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

[Daniel_MarianoR]

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?


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



Gracias.


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

[michaeloneil]

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]

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?



Thanks again.

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

[Neel1]

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