This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 60%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAV%3C/text%3E%3C/svg%3E)
Hi,
I'm having problems with a tab delimited file on Azure Data Factory. I have a Self-hosted Integration Runtime pointing to a drive on a On-premises server, and a Dataset that is pointing to this tab delimited file.
Here we can see the setup of a dataset that I've created to simulate the problem:
Attached goes a sample file.
20918-sample.txt
One row has a TAB in the data AND has multiple double quotes as text qualifier. This TAB should be included in the text, and not counted as a delimiter! I'm getting DelimitedTextMoreColumnsThanDefined error.
Opening this same file in Excel, it's shown as you can see:
This is the expected behavior.
Using the "Preview" button on the dataset, we receive this:
Unfortunately I'm unable to modify the source file.
I think is a bug, but, I'm accepting any other recommendation.
Thank you very much.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 9%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHM%3C/text%3E%3C/svg%3E)
Welcome to Microsoft Q&A Platform. Thanks for posting the issue.
This can be solved by passing " as the escape character as shown in below snap.
Hope this helps! Please let us know for further queries and we will be glad to assist.
Hi @HarithaMaddi-MSFT ,
Thank you by your reply.
I've tried you suggestion, but it didn't identified the columns correctly. In my case, I'm using Tab (\t) as the delimiter, with 8 spaces the columns aren't separated, they appear as a single column.
I know that you've understanded the problem, but, let me reinforce what is happening. I'll write TAB where this character is placed:
TAB"""132694 TAB 61-FDN""" TAB
The first is the delimiter from the previous column, the second is inside the text of the column, and the third is the delimiter for the next column.
I'm pretty sure it's a bug, because I tried to load this file even with SSMS, and it worked like a charm.
Thank you very much by your attention.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 97%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Does this file have to be tab delimited? Seems risky to load/import if the fields could potentially contain the delimiter. I run into this problem with commas and use a pipe "|" instead.
Thank you @Alexandre Von Mühlen for sharing more details. Its strange that it worked for me with Empty spaces as well in same way as "\t" but I got the issue now. Apologies I got distracted by another prominent issue that was giving the same error in copy activity with tab delimiter which was a bug and got fixed. I have observed that by adding " as escape character fixed this issue. Please find above answer modified to include this. Kindly check and let us know if it works!
Thanks for your patience!
Thank you @HarithaMaddi-MSFT ! It worked perfectly. Again, thank you very much.
Hi @SethWH . I agree with you. But, some times you cannot define how the files are formatted. This is my case, I'm dealing with a close formatted file.
Thank you by your time.