Text to column activity

Hello Kind Strangers,

I have a bit of a tricky issue.

I have a excel file that is broken in the way that the columns are not where they are supposed to be, meaning that all of the column names are in the first row and they are seperated by a comma like here:

image

I would like to use text to column activity so that my expected result would be:

image

I need to do this only for the first row (as the rest of data is alright)

The issue is that I’m doing it for the first time and I have no clue what data should I provide there:

Do you have any tips?
image

I understand that YELLOW is name of my sheet but what should I type in the destination if I want this to be A1 -A 6?

@JennZabba

try this

Excel.Sheet(“Sheet1”).Range(“A1”)

cheers

I get an error stating that no data to split has been selected

23.8.0-beta.13817+Branch.release-v23.8.0.Sha.120cd3ec54f082511b48105e2cfb52f8897346a8

Source: Text to Columns

Message: No data to split has been selected.

Exception Type: System.Runtime.InteropServices.COMException

System.Runtime.InteropServices.COMException: Nie zaznaczono żadnych danych do rozdzielenia. at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Object aArgs, Boolean aArgsIsByRef, Int32 aArgsWrapperTypes, Type aArgsTypes, Type retType)
at Microsoft.Office.Interop.Excel.Range.TextToColumns(Object Destination, XlTextParsingType DataType, XlTextQualifier TextQualifier, Object ConsecutiveDelimiter, Object Tab, Object Semicolon, Object Comma, Object Space, Object Other, Object OtherChar, Object FieldInfo, Object DecimalSeparator, Object ThousandsSeparator, Object TrailingMinusNumbers)
at UiPath.Excel.DelimitedTextToColumnsSeparator.SplitTextToColumns(Range source, Range destination)
at UiPath.Excel.ExtendedWorkbookApplication.TextToColumns(String sheet, String sourceRange, String destinationRange, ITextToColumnsSeparator separator)
at UiPath.Excel.Activities.Business.TextToColumnsX.<>c__DisplayClass53_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
— End of stack trace from previous location —
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
— End of stack trace from previous location —
at UiPath.Excel.Activities.Business.TextToColumnsX.ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
at UiPath.Shared.Activities.AsyncTaskCodeActivityImplementation.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.Shared.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

@JennZabba

Can you see once in which cell the values are present like A1 Or B1

and pass that cell in the above mentioned expression

or indicated it as below

cheers

Hi @JennZabba ,
It is a string 1,2,3,4,5,6
→ you can use split String
eg:
input is text = 1,2,3,4,5,6
Split(text,“,”)
→ you have array of String {1,2,3,4,5,6}
you can use add data row
add this array to data table
or get it a data table have 1 row
-write range in file start A1
→ read range again to get data have new header
regards,

Simple way


image
regards,