I am working on an import script to import 6 excel files, all the same format with 4 tabs each into 4 different tables. I would like to include the name of the excel file as a column. So, I set up my tables first with a final column called “File Name”, nvarchar(max), and in the import I add a new column and select the file name option:
But when I run the script it doesn’t import any rows for this table. I get an error “encoding ‘System.Text.SBCSCodePageEncoding’ does not match the first file encoding ‘System.Text.UnicodeEncoding’. Import may fail.” in the log, but I also get that error for some other files without the file name column and all rows import correctly. Does anyone have any ideas as to why the file path would cause the data to not import? I removed this field to test and everything worked fine. Does this need to be a different format? I would think that nvarchar would work just fine.