You can do this by creating the table in SQL first with the appropriate data types. You can do this by importing into SQL first and then write your script to update the table. You would need to create a .bat file in order to run the SQL scripts.
The command for the .bat file would be something similar to this:
sqlcmd -S ServerName -U UserName -P Password -i "C:\newfolder\update.sql" -o "C:\newfolder\output.txt"
This creates an output file that will specify errors
If you are using a .csv file you would write into a text file and change the extension to .sql
You can use the following command:
BULK INSERT DBName.dbo.TableName
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
or this command
INSERT INTO DBName.dbo.TableName ([Employee_ID], [FirstN], [LastN], [PhoneN])
SELECT A.[Employee_ID], A.[FirstN], A.[LastN], A.[PhoneN]
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\sqlscript\FileName.csv', 'select * from [TableName]') AS A
ORDER BY [Employee_ID];
You can set the .bat file to be scheduled within windows task scheduler.