How to put Text to Columns with Excel VBO

[EdritFranquiz]

Hi,  I have been trying to create a Code Stage in order to put text of a CSV file delimited by a "^" in Columns.  Initially I tried using Import CSV, replacing the delimiter and putting it in a Collection,  but the files are too big and it gives Memory Errors.    Has anyone done anything like this. I did a code but it´s not working.  Dim wb, ws, range, excel as Object wb = GetInstance(handle) ws = GetWorkbook(handle, WorkbookName).ActiveSheet.Range(cellref,cellref).Activate() excel= ws.Application range = ws().Selection.TextToColumns(OtherChar, Other)   I would really appreciate if anyone has some inputs here. Thanks!

[RakeshReddy · Accepted solution]

Try This   Dim RR As Object Dim excel As Object Try RR = GetWorksheet(Handle, WorkBookName, WorkSheetName) RR.Activate() excel = RR.Application RR.UsedRange.Select() excel.Selection.TextToColumns (DataType:=1, ConsecutiveDelimiter:=True,Other:=True,OtherChar:=Delimiter) Success = True Catch e As Exception Success = False Message = e.Message End Try

[SREEKANTHGTR]

Hi Rakesj Reddy,

How to use Fieldinfo in your code, I need to convert as as text or general.

Thanks in Advance

------------------------------
SREEKANTH GTR
Junior software engineer
Wipro
Indian/Maldives
------------------------------

[MiguelCarrillo]

Create an intenger matrix
Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Then you could use it as parameter for FieldInfo
FieldInfo:=matrix

Full Code

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application

excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

------------------------------
Miguel Carrillo
------------------------------

[SasikanthMachav]

Hi @Miguel Carrillo,

I wanted to convert text or custom columns to date columns using a code stage. But when i declare the array as you mentioned above its not working. Its throwing an error



I have recorded the manual operation using record macro. PFB
​
Could you please help me in declaring the array for the above code in such a way that i can use it in my code stage!!!!!!


------------------------------
Sasikanth Machavarapu
------------------------------

[ewilson]

Hi @SasikanthMachav,

If you're trying to change the format of a column have you tried using the Format Cell action and passing in the format that you want? The action is designed to work on a single cell at a time, but you could create a custom version that handles an actual range by adding an extra input value for the end of the range. Then you would change the single line Code stage to include the end of range value.


Cheers,
​

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[MiguelCarrillo]

Hi the following code should work:

In this link you will find the integer for the type of format you need it. https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype
Remember that the The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim matrix = New Integer(2, 1) {{1, 4}, {2, 4}}

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application

excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try​

Let me know if you have any questions. And I could try to explain it to you in a deeper way.
Have a nice day.

------------------------------
Miguel Carrillo
------------------------------

[IrinaMihalache]

Hello,

I am trying to use this code to convert  AG column (33th column from an Excel file with mutiple columns) into a DATE format column but I think I am doing something wrong because I get the "The given key was not present in the dictionary" error.

Maybe the input is not ok? Can you please help me ?




Thank you,
Irina

Have a nice day!

------------------------------
Irina Mihalache
------------------------------

[ManpreetKaur1]

Hi @IrinaMihalache,

​The error "The given key was not present in the dictionary" generally comes when you have created the instance (i.e. handle) and keep on making changes in the code while in debug mode. In such scenario, the handle which you have created, lost its value from the memory and you get the error. The best way to resolve the issue is reset the code and again run it. The issue won't appear. Please let me know if that is not the scenario.

Note- I am assuming that there is only single handle in the process

------------------------------
Manpreet Kaur
Manager
Deloitte
------------------------------

[IrinaMihalache]

Hello Manpreet Kaur,

Thank you so much for your response! I resolved the "The given key was not present in the dictionary" error, now the message is "Success", but not working as expected.

I need to make AG column (column number 33) from 'General' to 'Date' (DMY option) and I am using this code below, but nothings happens, although I do not receive any error. I also tried with DataType 4, and TextQualifier 1, but nothing changes.

Dim wb, ws As Object

Dim excel, sheet, range As Object

Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1},{3, 9},{4, 1},{5, 1},{6, 1},{7,1}}

Try

wb = GetWorkbook(Handle, Workbook)

ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

excel.Columns("AG:AG").Select

excel.Selection.TextToColumns (Destination:=excel.Range("AG1"), DataType:=1, _

TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=matrix, TrailingMinusNumbers:=True)

Success = True

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

excel = Nothing

sheet = Nothing

range = Nothing

End Try

------------------------------
Irina Mihalache
------------------------------