How to OPENROWSET on x64?

Requirement:
- Load data from csv source file
- Join with an internal table
- Trim a column of csv
- Insert into the target table

Problem statement:
OPENROWSET query works on my dev machine, but doesn't work in test server environment, why?!

Because dev machine:
- Wass x86 machine, having 32bit software installed on it
- 32 bit SQL Server 2008
- and because you cannot run 32bit driver for JET from a 64 bit application;

And test machine:
- Was running on x64 bit, windows server 2008 R2, with SQL Server 2008 R2

There are couple of solutions that you can target according to your workload and required effort:

Solution 1: Use push technique instead of pulling from SQL Server
Write a visual studio app using CPUAll as configuration settings; and run this app on the target machine.

Solution 2: Use Microsoft Access Database Engine 2010 Redistributable

You can download x64 version.

This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported.ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

SELECT * INTO #tem FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=c:\test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

select * from #tem

drop table #tem

Solution 3: Use temp table
- Load the csv in temporary table
- Join and select
- Insert

CREATE TABLE #tmp(a float, b varchar(max), c varchar(max));

BULK INSERT #tmp FROM 'c:\test.csv' 
WITH (FIELDTERMINATOR = ',' , FIRSTROW = 2 );

--If you want to see anything from the temp table, uncomment following loc
--SELECT * FROM #tmp

--Insert into select *
INSERT INTO TARGET_TABLE ([C1],[C2],[C3])

SELECT name=SUBSTRING(A.b,0,30)
,trimmedPhoneNumber= SUBSTRING(CONVERT(VARCHAR, A.c,0), 4,5)
,code=b
FROM #tmp A
 INNER JOIN OTHER_TABLE B ON REPLACE(STR(A.a, 3), SPACE(1), '0') = CONVERT(VARCHAR,B.CODE,0)
ORDER BY B.ID;

TRUNCATE TABLE #tmp;
DROP TABLE #tmp;