EXCEL: Compare Columns Tips

\ by

Here are several ways to search for a string in one column in another. The last one worked the best for me, looking for column E in column C.

Just past in the first row of column D1 and copy down.

=NOT(ISNA(MATCH($K1,$G$1:$G$99504,0)))

=COUNTIF($K$1:$K$99504,$G1)>0

=COUNTIF($C1,”*” & $E1 & “*”)>0

Posted in: Excel

Import EXCEL data using OPENDATASOURCE

\ by

Don't forget that now that it is in the tempdb table you can script out a field descriptor or a CREATE table statement. Use that for a permanent import table elsewhere. 

USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Now use Query Analyzer to script out the table in tempdb. Here's a sample script.
--
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO
Posted in: Excel, SQL

INSERT data from SQL table to EXCEL

\ by

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\MSSQL\Reports\JobSchedules.xls;',
'SELECT JobName, ScheduleName, next_run_date FROM [Sheet1$]')

select j.name JobName, s.name ScheduleName,
 next_run_date
from msdb..sysjobs j
join msdb..sysjobschedules s
 on j.job_id = s.job_id
where j.enabled = 1
 and s.enabled = 1
ORDER BY
 j.name
GO

— Note:
—  Requires E:\MSSQL\Reports\JobSchedules.xls to already exist with the correct columns.

Posted in: Excel, SQL