david.leyden (9/21/2016)
I import a lot of flat text and I would use BULK INSERT to do it from the server side and BCP to do it from client side. BCP doesn't like excel files and it doesn't like text qualifiers. So I always convert either of these file types in to tab separated values which is the default delimiter for both BCP and BULK INSERT. This simplifies things and removes requirement (in most cases) for text qualifiers.BULK INSERT [schema].
FROM 'pathToFile' WITH (
FIELDTERMINATOR = '\t'
,ROWTERMINATOR = ''
);
Just a couple of notes here...
BCP and BULK INSERT handle so-said text qualifies exactly the same way. In your example above, both will leave a leading double quote if the first field in the file is double quoted. Neither will import a real .xls file. The best thing for Excel spreadsheet imports is the ACE driver and OPENROWSET.
Consistency is one of the most important things when importing a TSV or CSV exported from EXCEL. The blooding thing will only quote-delimit those things that contain a tab or a comma and the throws a monkey-wrench into both types of imports.
A work around is to quote-delimit everything. The ImportCSV and ExportCSV bits of Powershell are really good for that as a pre-processor and fairly decent for performance, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.