Validating And Mapping Excel And Sharepoint Columns For Successful Data Import

Validating Data Types to Avoid Import Errors

Ensuring that Excel column data types match up with the expected SharePoint column types is crucial for avoiding errors during import. Data type mismatches between text, numbers, dates, and other formats are a common culprit for import failures. Following best practices for validating compatibility early in the process will set your imports up for success.

Matching Excel and SharePoint Data Types

Start by auditing the Excel data types in your import file against the corresponding SharePoint columns you intend to import into. Pay special attention to any columns that will be populated into SharePoint through lookups or calculations, as the returned values may not always match. Some key data types to check for matches:

• Text columns must have strings in Excel, mismatch text and number formats can cause truncation
• Date columns expect properly formatted dates in Excel or will fail with type errors
• Number and currency columns need formatted numerical values

Common Data Type Mismatches to Check for

Some recurring data type pitfalls to double check before importing include:

• Excel dates must be real date values, formatted text strings will not import
• Extra spaces, symbols or punctuation in Excel text can lead to issues
• Numeric columns expect whole numbers by default, decimals or text cause errors

Saving your import file in .xlsx rather than .xls format can avoid some overflow issues with large number and date values. Be wary of Excel incorrectly guessing data types based on values – a column with numbers and a few empty cells may be interpreted as text by Excel.

Using Power Query to Preview Data Types

The Power Query add-in provides an excellent method to examine your source data types during the import process. As you configure your query in the Power Query editor, browse through each column to validate that the detected data types match what your SharePoint columns expect to receive.

Watch for warning symbols that may indicate type mismatches on columns. You can also right click on any column header and select Change Type to directly view and modify the data type. Pay special attention to Date and Whole Number columns, where default type detection often misses the mark.

Casting Column Types in Power Query

When mismatches occur, the Change Type option lets you forcibly cast a column to the desired type in Power Query before importing. Simple type casting options are available for:

• Text columns
• Date formatted columns
• Number columns, with precision and decimal settings

Applying proper data types within the query before importing can save tremendous heartache over trying to fix issues after faulty import. Date and numeric columns are common areas where upfront type casting is required to get usable values in SharePoint.

Troubleshooting Validation Errors During Import

Even with careful planning and type checking, reality conspires against the smoothest of imports. As imports execute, be prepared to handle warnings of validation failures or errors attempting to parse rows. Depending on import settings and external data mappings, SharePoint may report cryptic messages or silently generate errors hidden in logs. Here are some common import anomalies and fixes to watch for.

Handling Date and Number Format Errors

Regional date types like dd/MM/yyyy in Excel often don’t fly for SharePoint date columns expecting mm/dd/yyyy. Numbers formatted with commas for thousands separators may also trigger invalid character warnings. Defining date and number strings explicitly during import is preferred, but messy datasets may require transformations like:

• New calculated columns converting regional dates to ISO accepted YYYY-MM-DD in Power Query
• Replacing number commas with blank spaces

Fixing Empty Column Header Rows

SharePoint requires column headers rows to match imported data during import. But subject matter expectations don’t always align with the raw Excel data at hand. Rather than rearranging source columns, use Power Query to promote any row into the header:

1. Right-click on row you want for headers
2. Choose Use Headers as First Row from the context menu

Skipping Rows with Invalid Data

Frustrating import errors often come from invalid values choking the parser. Before trashing the whole sheet, try setting import options to skip rows with errors. In SharePoint Lists settings:

1. Check Allow invalid data to be discarded option
2. Start conservative with Max number of errors set to 5 or 10

This allows useful data to be imported while isolating any messy cells for follow up data cleansing later.

Configuring Import to Ignore Blank Rows

Default list import settings also choke on rows left intentionally blank in the middle of your datasets. Avoid scouring the sheet to clean every empty row by tuning the import settings in SharePoint Lists instead:

1. Check Ignore if all columns are empty under Import Settings

With this activated, blanks rows will simply be skipped to move onto parsing rows where meaningful data resides.

Mapping Excel Columns to SharePoint Columns

Matching up source Excel columns with destination SharePoint columns seems like an obvious piece during imports. But ambiguous headers, shifting ordinal positions, hidden helper columns, and upstream data changes frequently throw curves into clean column alignments.

Techniques for Column Mapping

Simple list imports happily map source and destination columns by directly matching on header names in order. But additional techniques like header ordinals, custom IDs, conditional includes, and Power Query may be required for advanced needs:

• Map by field header name when headers neatly match SharePoint columns
• Use column ordinal to map by physical sequence instead of names
• Add helper ID columns to provide consistent mapping points
• Filter columns conditionally via Power Query before import
• Explicitly define destination mapping in the Power Query wizard

Using Column Headers to Map Data

When Excel headers already align cleanly with SharePoint destination lists, importing becomes wonderfully painless. SharePoint imports can automatically match source to destination simply by traversing headers in order:

1. Confirm headers in first Excel row with SharePoint column names
2. Click Import Spreadsheet under List tab in SharePoint
3. Select source file from local system when prompted

Header name matching proceeds automatically, allowing file data to populate smoothly into the mapped destination columns.

Configuring Default Value Mapping

List settings also allow defining default values for any blank source columns encountered during import:

1. Browse to List Settings > Under Column default value heading
2. Add default text or numeric values to use for blank Excel fields

Having default values prepopulated ensures import consistency when Excel columns inadvertently get shifted or removed entirely.

Mapping Columns with Power Query

When source headers and SharePoint columns don’t align cleanly, Power Query provides a versatile canvas for explicitly mapping columns. After connecting your Excel table, clicking on any header in the Destination step activates a dropdown to remap to any SharePoint column needed, regardless of source name or sequence. Common adjustments like splitting or appending columns also help reshape messy source data into clean destination mappings.

Verifying Imported Data in SharePoint

With import settings tuned, data types aligned, and columns mapped, imports should now flow smoothly from external tables into SharePoint lists. But the final step remains to verify that the actual results meet expectations – meticulously checking that the imports fulfilled requirements rather than blindly assuming success.

Check for Truncated or Mismatched Data

Alongside spot checking for general data quality, scanning for truncation warnings or type mismatch icons helps identify any lingering issues from faulty imports. Despite best efforts,Large text blocks get truncated or date formats may still conflict at times. Catching any table damage early allows the errors to be addressed at the source and a fresh import attempted.

Validate Calculated Columns

Formulas connected to imported data require inspection as well. Ensure any calculations based on the arriving data properly reference the new imported columns. Data changes may shift cell references or named ranges unexpectedly.

Confirm Attachment Imports

When source workbooks include file attachments or embedded images, validating that these elements survive importing properly requires a bit more digging under the covers of SharePoint. Navigate to the Library storing attachment data for the imported list and browse for a sampling of expected documents.

Review Import Logs for Errors

SharePoint captures inherent details and errors from import jobs for inspection afterward. But accessing these status logs remains obscured from casual site users. From the site Contents page:

1. Navigate to Site Contents > Site Administration > Site Collection Audit Log Reports
2. Open Data Import activities to review timestamps, user details, source paths, row counts, and any errors

With evidence showing clean imports, data refreshing on schedule, and calculations aligned, consider import mapping requirements…complete!