I am importing 5 different reports from payment sites (paypal, worldpay, etc)
It works like a charm using csv!
BULK
INSERT Report_Table
FROM '\\Files_Server\departments\finance\Paypal.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a'
)
But there is this one site that gives us an .xls file (Excel 97-2003 Worksheet).
I can’t use SSIS because in the server I don’t have excel installed and I don’t have permission to install it… My question is, if I convert it myself from xls to csv, will I be able to BULK INSERT it easlly, or it will contain garbage remaning from the xls to csv conversion?!
Options I see:
1- ssis (I can’t install excel on the server).
2- convert xls to csv and then bulk insert? (can I?)
What other options do you see/recommend?
Best Answer
You don't need to install Excel on the server to import with SSIS, you only need the "Access Database Engine", which is simply the data access layer:
https://www.microsoft.com/en-us/download/details.aspx?id=13255
We definitely don't have Excel installed on our database server, and Excel imports work just fine with this.