Category: CSV

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

SET NOCOUNT ON

EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo [Test.txt] > C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo ColNameHeader=True >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Format=Delimited(^|) >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo MaxScanRows=50 >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo CharacterSet=OEM >> C:\Temp\Schema.ini'

EXEC master..xp_cmdshell 'echo Col1=ID TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col2=LastName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col3=FirstName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col4=MiddleName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col5=MailingAddressLine1 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col6=MailingAddressline2 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col7=MailingAddressCity TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col8=MailingAddressState TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col9=MailingAddressZIP TEXT >> C:\Temp\Schema.ini'

EXEC ('INSERT INTO ImportTest (ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP)
SELECT ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\Temp\;HDR=YES'',''select * from person.txt'')
')
EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'