Import CSV using “Microsoft Access Database Engine 2010” to SQL Server


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'

How to fix CLSID {00024500-0000-0000-C000-000000000046} error when using excel component in background code?

We have  a windows Service application which uses Excel Application Object. It used to work on some machines but not all, the error which I am getting is:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

After some struggling I found a solution and it works, solution is:

Solution 1:

  • Create directory C:\Windows\SysWOW64\config\systemprofile\Desktop (for 64 bit Windows) or C:\Windows\System32\config\systemprofile\Desktop (for 32 bit Windows) and give full permission
  • Run program “dcomcnfg” Go to “Console Root/Component Services/Computer/My Computer/DCOM Config/” Look up Microsoft Excel and choose properties.
  • Go to Security and Launch and activation permissions->customize->add network, network service, administrator, interactive, system and everyone and give full access
  • Now go to “Identity” and select “The interactive user”.
  • Now go to “Console Root/Component Services/Computer/My Computer”,  right click Properties of “My Computer”, give full access permission to all users.
  • Solution 2:
    <system.web>
    <identity impersonate=”true” userName=”domain\jack” password=”********” />
    </system.web>

 

How to display negative time in excel cell

While working with excel application I found that if we find difference between 2 times is negative then excel doesn’t display time, it shows “##########” instead of values. For positive difference we don’t have any issue.

There are 2 ways to handle this, using 1904 date system and other using custom formula.

To change the date system you need open Advance options of excel and enable the 1904 date system box.

Use the formula:

=IF(D2-A2<0, "-" & TEXT(ABS(D2-A2),"hh:mm"), D2-A2)