Solution: error on input tool "too many fields in record"

[KRexroad24]

Hi Everyone,

I wanted to create a post and share an easy standard and data cleaning routine that may be helpful for the "too many fields in record" error. Additionally, if you've ever encountered a file that also has quotes in the input, this also addresses eliminating them.

Input tool: read in as a delimited text file, delimiter: none, uncheck "first row contains field names", field length: 254000000000000000000000 (yes this is ginormous, and for some data sets it may be larger to ensure that every character is appropriately brought in and not cut off)

Text to Columns tool: Column to split: field_1, delimiter: , (or whichever is applicable), select split to columns with number of columns however many is appropriate to ensure all your data is being accounted for (for a medium-large set I entered 100)

Data Cleansing tool: check "remove null columns" (this eliminates the extra columns that text to columns may have added), uncheck Field_1 from "fields to cleanse" since this data is now split up, select other cleansing options as desired. For this workflow, I did not select remove punctuation. While quotations are something I want to remove as initially stated, other punctuation may be needed (example the "-" in a date format as 2020-10-10)

Select tool: this is where I uncheck Field_1 to remove the original input that we later split up.

Dynamic Rename tool: this is where I bring up the first row as column names by selecting "take field names from first row of data" in the rename mode options.

Multi-Field Formula tool: Select text fields, check all fields, uncheck copy output fields, in the expression box type StripQuotes([_currentfield_])

I've attached a screenshot to visualize the logic. Please note that with my import, the header names did not have quotes, but tools may be moved around as needed in the process.

My final piece of advice that I learned was that if you are running a large dataset through a cleaning process, separate this into a separate workflow then export the cleaned files into a new workflow. It will save you time if a workflow needs to be run more than once on the same files for whatever reason.

Let me know if there is anything that needs clarified or you have feedback on! Thanks!