If you want to quickly grab the contents of a spreadsheet or csv file openrowset is a handy quick way of doing it. The examples below show you how to pull in an Excel or CSV file.
From Excel
|
1 2 |
SELECT * INTO #temp FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=c:importtest.xls','SELECT * FROM [sheet1$]') |
From CSV
|
1 2 3 |
SELECT convert(datetime, [date submitted],103) id, * into #temp FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:import;', 'SELECT * from test.csv'); |
It can be funny about long file names so if there are problems try renaming the file to something shorter.
Also be warey of missing data – it seems to look at the first however many rows of data – if there is no data in one column it will ignore the entire column so be careful!