Import CSV data into Dataverse

Problem

• You have a bunch of data that you need to import into Dataverse (either the “free” Teams version or the Power Platform version)
• Said data is in CSV/TXT format or can be exported into that format
• The damn Microsoft import tool ain’t working right and keeps erroring out on import

Solution

Note: First off sorry for the poor picture quality, below. I had these screenshots embedded in a Word doc and this is the best I can do without the originals.

• Find the correct upload option in Teams / Power Platform:
  • First, you should make sure that you upload the data via the tab or screen dataflows and not via the “Create new table” function. Only under Dataflows can you see the status of the upload and be informed about errors.

• Convert CSV to XLSX and clean out illegal characters:
  • You will need to use the Excel import feature and not struggle with the CSV-Wizard in Teams/Dataverse.  Using the CSV import method, you can run into a hidden limit of 100 characters per cell, which caused problems with my German language data set.  
  • Thus convert your .csv to a .xlsx either by saving into the xlsx format or copying the data out of the CSV to a blank Excel file.  You can also avoid the comma / semicolon problem (aka the default delimiter that is different in en-us vs de-de or even en-gb from the Region settings in Windows) that can occur with unclean data when using Excel instead of CSV. Make sure that the Excel file does not contain “=” characters and that the columns with the phone numbers in Excel are set to the format “number” without decimal places.
  • You can upload the file or point the Excel Import Wizard to it via SPO link (you must generate a link and remove all query strings (everything after “?”)) or “Search OneDrive”, uploading the file to your OneDrive account.
  • I used the OneDrive method.  See the screenshot below:

• Step 3 – Skip Power Query:
  • After uploading the Excel file, you’ll see a Power Query overview of the spreadsheet. You can’t make any changes here, so click “Next.”

• Step 4 – Change the format of the columns
  • You will now be confronted with a “real” Power Query window in which you can adjust the data. I recommend setting each column to “Any” format, because if you have a column with 1000 numbers and 1 text character and the column is set to “Integer” in Power Query, the whole damn import will fail!
  • You can change the format of the column by clicking on it individually or by changing the function in Power Query to “type any”. For example, {“Tel”, Int64.Type}, {“Tel”, type any}.

• Step 5 – Finalize the import and create a Primary Key column
  • Next while still in Power Query, you can give the table a name, a description, and a primary key, which by default has the rather stupid name “Name”.
  • One can also see whether the columns are imported as text or not, based on your changes to the Power Query in the last screen.
  • I suggest changing the “Text” value here to “Multi-line text” because there is a limit to the length of “Text” fields. (Maybe that’s where the 100-character limit comes from?)

• Step 6 – Done; time to monitor the import and deal with any errors
  • Finally, you can monitor the status of your import on the “Dataflow” screen. If there is a problem, the import will be canceled within 30 seconds. If not, it will be “published” in a few minutes.

Details

I am hoping that Microsoft will eventually fix their import tool. Maybe they already have but this is the process I had to use when I tried to import an Excel spreadsheet with 27000 rows into Dataverse for a Power App project for a customer.  The import wizard offered by Microsoft for this purpose continually failed, so I had to find a workaround.  I hope you find this info helpful, if you are also having trouble importing this sort of data.