Problem with hidden quotes on column header in excel.xml file

Have the following in a column header on a .xml file. It does not appear to have quotes around the header but when copied to a notepad it looks like this:

“Record
(Group Share Path/Mapping, Physical Record, Phone Number, Website Address)”

Have tried the code:

row.item(“Record”+ vblf + " (Group Share Path/Mapping, Physical Record, Phone Number, Website Address)").toSTring

but am unable to account for the hidden quotes - get error that column does not belong to datatable.

Any ideas?

There are a few different ways to create a new line. Try the different options: vbLf, vbCr, and vbCrLf.

You can also try logging the column name with a Log Message activity. Then in the output pane of Studio, double-click the log entry to see the detail view. If it has \n then it is vbLf, \r then vbCr, or \r\n then vbCrLf.

If you are able to post the file, then we can take a closer look and provide better guidance.

Hope that helps!

그 컬럼을 저장하고 있는 엑셀 파일을 샘플로 하나 업로드 해주시겠어요?

Actually, it comes back as \r\n but still get the same error when I use vbCrlf…cannot post the file because of security

Maybe there isn’t a space before the first (.

row("Record"+ vbCrLf + "(Group Share Path/Mapping, Physical Record, Phone Number, Website Address)").ToString()

Hard to say without seeing an actual sample. Possible to upload the headers and remove the data?

Book4.xlsx (12.3 KB)

Works for me:
ColumnHeaderExample.zip (46.3 KB)

One other suggestion, if you’re still having issues, you can also consider getting the value by column index instead of column name.

Still get this error:

image

tried the index by creating a variable index + 1 and then iterating through (0) and then (index) on two separate write lines. and I am not picking up the values…still get the same error

I think I found issue. When I printed the column header alone…i found there is an extra space before the (

You can see in the notepad file clearly…

Based on this i adjusted the code by adding space…I was able to print all the column values as shown below…

Hope this helps…

1 Like

I already have the extra space in my code…not sure why I continue to get the error

Here is my current code

row.item(“Record” + vbLf + " (Group Share Path/Mapping, Physical Record, Phone Number, Website Address)").ToString

what I get when I simply right click and copy that header to note pad is this:

As you can see, it adds quotes to both ends although those are not visible on screen. The other header Record Type when simply copied to notepad does not have quotes


If do this I can get all with an incrementing index. However, I get an error when the increment is greater than the row count…thinking out loud here, if I set an if that the index < dt.rows.count then I will only get 4? Let me ck that

@Chris_Bolin - Check this.
Header_CB.xaml (7.5 KB)

Point to the file you shared and run this…

image

actually - although the log is \n\r if I change it to vbLf it works

@Chris_Bolin - I am running against the file you shared. I am surprised that how it is erroring at your end??

If you see, I have already showed my output. So something is not correct here??

Again getting the error…when I go back to my code and do vbLf get the same error…even vbClrf no longer works

I am going to put it down for the day. Thanks everyone for the help