I have spent quite a bit of time in the past trying to import CSV data to SQL Server. It's just about possible, but bcp (and its format files) are absolutely horrifying. As far as I could tell, other methods of importing data involved either having access to put files onto the server so it could read them or getting SSIS to work - both pretty much impossible in a large organisation.
For example:
bcp "SELECT blah... FROM blah" queryout C:\..\blah.csv -c -t, -T –S<servername>