Simple way to read Excel or Access data in SQL Server

-- list of available providers
exec master..xp_enum_oledb_providers

-- Three ways to read in Access data (file: c:\db1.mdb, tabel: TEST)

1a) Using openrowset with older MS Access version:
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM TEST')

1b) For 2010 or more recent (*.accdb)
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM MAANDEN')

2) Using opendatasource:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\db1.mdb;')...[TEST]

3) Using a linked server called TEST for 2010 or more recent:

EXEC sp_addlinkedserver
@server= N'TEST',
@srvproduct = N'MSDASQL',
@provider= N'MSDASQL',
@provstr= N'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;'

-- Read in Excel (file: c:\employee.xls, worksheet: TEST)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\employee.xls;Uid=;pwd=;','SELECT * FROM [TEST$]')

-- Insert into text file
insert openrowset( 'Microsoft.ACE.OLEDB.12.0', 'Text;HDR=yes;Database=c:\tmp\', 'select * from test.csv') (testname, testaddress) select testname, testaddress from vw_test;

Note:
  • MS Access needs to be installed on the same server where SQL Server is installed
  • Make sure that the use of ad hoc names is allowed:
    exec sp_configure 'show advanced options', 1;
    RECONFIGURE;
    exec sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
Possible error messages and solutions:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

Give read/write access to "everyone" on the server to the following folder:
C:\Users\<SQL Server Service Account>\AppData\Local\Temp

Make sure this is set on the server where the code is running, i.e. on the client PC where the code is executed within Management Studio.

Converting WAV DTS to MP3

A WAV DTS file contains a multi-channel encoded digital format called DTS and has .wav as extention. Playing such a file in Zoomplayer or WinAmp typically produces a static sound. This is because a filter and/or decoder are not installed.

For Zoomplayer a filter can be installed (Gabest AC3), but a separate DTS decoder is still needed. Ffdshow contains such a decoder, but setting up this mapping in advanced options -> customized media playback -> source filters and splitters didn't help. Zoomplayer keeps displaying the "An ERROR has occured!" messagebox when trying to play the WAV DTS file.

Foobar2000 used to have an additional component, foo_dts.dll, that could be downloaded from the site, but it is no longer available. I was unable to find out why it is no longer supplied. After searching the internet I found a version of the library, but foobar2000 0.9.5 refused to load it, reporting that it was an outdated version.

A player that can play them out-of-the-box is VLC player. This player has a built-in filter and decoder to play WAV DTS files. It can also transcode files, e.g. to MP3 format, but when I use it on a WAV DTS file, the program terminates unexpectedly.

So, in the end, I was unable to find a "one-click" solution. Here is how I finally managed to convert a WAV DTS file into a MP3 file:

For the example, I use a WAV DTS file named c:\wavdts.wav

  1. Download BeSplit (http://www.afterdawn.com/software/audio_software/audio_tools/besplit.cfm). I used version 0.9b6.2.
  2. Extract the zip file to c:\
  3. Start BeSliced.exe
  4. Drag and drop wavdts.wav onto the besliced canvas
  5. Select "DTS" when asked to select a format
    BeSplit is now ripping the DTS stream from the WAV DTS file.
    To use BeSplit without BeSliced, execute this command at the command prompt:
    BeSplit.exe -core( -input c:\wavdts.wav -fix -logfile C:\BeSliced.txt -type dtswav -output c:\wavdts_Fixed.dts ) -profile( BeSliced v0.3 )
    A file called c:\wavdts_Fixed.dts is created.
  6. Download DTSDec (http://www.rarewares.org/others.php). I used version 0.0.2.7.
  7. Extract the zip file to c:\
  8. Execute this command at the command prompt:
    dtsdec -o wav > regularwav.wav wavdts_Fixed.dts.
    DTSDec is now transcoding multi-channel DTS to a regular two-channel WAV file called regularwav.wav
  9. Use your favorite MP3 converting tools, e.g. AudioGrabber with LAME, to convert this WAV file to a MP3 file.