How do I correct the most common auto-import errors?

Question

Why am I getting auto-import errors?

Answer

Including comma characters in field text values

One of the most common .csv file auto-import errors are including comma characters in field text.

As .csv files use comma characters as separators between fields, including a comma in any field value can greatly distort record values. 

For example:

1. For a jobs-id.csv file, a simple file layout might commonly include four columns with these headers:
   Name, ID, Description, Status, Permissions, Notes
2. We want valid text data to look like this:
   Job 1, 12345, Clean up areas 1 and 2, Active, 30=Employee, important job for our division
3. However, if the text data for the Description field includes a comma character instead of "and," the encoding of the file will look like this:
   Name ID        Description         Status Permissions          Notes
   Job 1, 12345, Clean up areas 1,2,        Active,                  30=Employee,     important job for our division
4. In other words, the "2" in Description will be pushed into the Status column which can only import Active or Inactive as valid values.
     

Solution

Option A: Do not include comma characters in any text values

Option B: Place any text values with commas include of double-quotes

The standard protocol of a CSV file is that any entry that has commas in it (that are not delimiters but actual data) should be enclosed with Text Qualifiers, usually double-quotes.
Then when importing into most programs, there is the option of telling it what to use as Text Qualiifers (Access has this option).
This is how Access (or any other program) can differentiate commas that are part of the data from commas that are delimiters. Otherwise, there is no way for the program to know what the difference is.

So for example, let's say you have a simple file of full names and ages, but some of the full names had commas in it, here is how it might look with the Text Qualifiers.

Code:
Name,Age
John Doe,17
"Bill Smith, Jr",25
Dana Jones,34
"Tom Davis, III",9

If your data does not already have these Text Qualifiers in it, I would recommend going back to the source where the files are being created and request that they do this. If you are not sure whether or not they are already included, open the CSV in NotePad or WordPad (NOT in Excel), and see if you can find any Text Qualifiers.