A field was missing a closing quote in record xxx - Tab Delimited File

[EvolveKev]

Hi everyone!

I've looked around but I can't seem to find what I want - I'm close (but no cigar). Oh, go easy on me - I am an absolute noob.

I'm trying to import a 71 field .txt file. The data doesn't have headers (which is okay since I can union it with a separate header file), and it is in a tab-delimited format. 

Upon import I get the error message, "a field was missing a closing quote in record 51130." My conclusion, one of those 71 fields in row 51130 has a quote and is causing problems.

My challenge is how to remove that quote from that row?  

I've tried importing the file and removing the columns (\0) and then using the text-to-columns function to re-assemble the file structure, but I'm not sure what kind of delimiter to use to separate things back out (I was going off another example here in the forums). Maybe that's not the right way to go. 

Any ideas?

[Claje · Accepted solution]

I'm making an assumption that there are no tab characters in your txt file which are INSIDE of an existing field.

As such, can you try changing Option 9 in the Input Data Configuration from "Ignore Delimiters in Quotes" to "Ignore Delimiters in None" and see what happens?

What is likely happening here is Alteryx is seeing the opening quote, and then never sees a closing quote, so it thinks everything after this quote character is part of the same field, causing your error.

There are other ways to resolve this - using the method you wrote down of reading in non-delimited and parsing the file, you could then pick any delimiter you want - I personally really like the Pipe character |.

[EvolveKev]

OMG - that was the fastest reply ever - thank you! Lemme try!

[EvolveKev]

@Claje - that was it! Worked like a charm!

So, the "ignore delimiters in none" worked for me - I logically would have expected the "ignore quotes" option to be the one to go for, but I guess not. 

This is great.  BTW, it was the Alteryx community that sold me on Designer - it's such a good group.

Thanks again!

[EmilioB]

Hello,

I tried to opened a .CSV file and I got the same message "A field was missing a closing quote in record xxx ", then I changed option 9 and I'm getting error "Too many fields in record "X". Could you please share any Tip or trick for this one?

Thank you

[abdoul]

Hi @EmilioB ,

I'm getting the same error as you and changing option 9 cannot solve it. 

Did you find any alternative solution ?

Thanks!

[Amadeu_gustavo]

Now in version 2022.1 also there is an option configuration in line 13"AMP Only: Allow Newlines in Quoted Fields", enable this as true and it will work.

[AkisM]

Tick the "treat errors as warnings" option. You'll be able to see if there's an issue a lot easier if you can actually read what alteryx is reading. I was getting similar errors and none of the other settings were helping. After i ticked "treat errors as warnings" I noticed that the delimiter used in the file was different than the delimiter I was using in my input settings. Fixed that and no issues.

[Ozzy_Campos]

@Amadeu_gustavo  Thank you, that indeed worked for me.