Excel 2010 - OPENROWSET Extracting Data Incorrectly

• BoilerJ

  Default port

  Points: 1428

  More actions

  November 22, 2011 at 10:25 am

  #260465

  Hi,

  I am using OPENROWSET to import an excel 2010 file. I have code that dynamically loops through each file in a directory and goes to the properly name worksheet. The problem I am having is that that some of the data coming back is doing the infamous excel conversion (2.35365e+009 is truly 2353651403). The frustrating part is that some spreadsheets load correctly. I compared the columns in both spreadsheets to see if they were different but both were set to "General". My next step was to try and convert it to VARCHAR in the SELECT of the insert statement but this did not work (I assume b/c it is already in the E+009 format by the time it gets there). When I try and add the CONVERT or CAST in the OPENROWSET query I get the following error:

  OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Undefined function 'convert' in expression."

  Has anyone had to deal with this before? Is there a way to force the CONVERT or CAST inside an OPENROWSET or I'm I missing something very basic?

  Below is what I am using that loads the .xlxs.

  Thanks for ANY help!

  insert into BlueForce.dbo.InvoicePaymentTrackTemp

  (PRONumber,

  InvoiceNumber,

  CustomerNumber,

  CarrierSCAC,

  Weight,

  ActualShip,

  CarrierMode,

  CarrierCharge,

  MTSCost,

  PONumber,

  OriginName,

  OriginAddress1,

  OriginAddress2,

  OriginCity,

  OriginZip,

  OriginCountry,

  DestinationName,

  DestinationAddress1,

  DestinationAddress2,

  DestinationCity,

  DestinationState,

  DestinationCountry,

  DatePaid,

  CheckNumber)

  select convert(varchar, [PRO Number])

  ,convert(varchar, [Invoice Number])

  ,[Customer #]

  ,convert(varchar, [Carrier SCAC])

  ,convert(varchar, [Weight])

  ,convert(varchar, [Actual Ship])

  ,convert(varchar, [Carrier Mode])

  ,convert(varchar, [Carrier Charge])

  ,convert(varchar, [MTS Cost])

  ,convert(varchar, [PO Number])

  ,convert(varchar, [Origin Name])

  ,convert(varchar, [Origin Addr1])

  ,convert(varchar, [Origin Addr2])

  ,convert(varchar, [Origin City])

  ,convert(varchar, [Origin Zip])

  ,convert(varchar, [Origin Ctry])

  ,convert(varchar, [Dest Name])

  ,convert(varchar, [Dest Addr1])

  ,convert(varchar, [Dest Addr2])

  ,convert(varchar, [Dest City])

  ,convert(varchar, [Dest State])

  ,convert(varchar, [Dest Ctry])

  ,null

  ,null

  from openrowset('MSDASQL',

  'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 14.0;DriverId=1046;DefaultDir=C:\dba\imports Data;DBQ=c:\dba\imports\LG22 07 2011 MTS BG Invoice.xlsx',

  'select CONVERT(VARCHAR,[PRO Number]) -- This is where it will not convert

  ,[Invoice Number]

  ,[Customer #]

  ,[Carrier SCAC]

  ,[Weight]

  ,[Actual Ship]

  ,[Carrier Mode]

  ,[Carrier Charge]

  ,[MTS Cost]

  ,[PO Number]

  ,[Origin Name]

  ,[Origin Addr1]

  ,[Origin Addr2]

  ,[Origin City]

  ,[Origin Zip]

  ,[Origin Ctry]

  ,[Dest Name]

  ,[Dest Addr1]

  ,[Dest Addr2]

  ,[Dest City]

  ,[Dest State]

  ,[Dest Ctry]

  from [LG22 Data$]

  where [Carrier SCAC] is not null')

• November 23, 2011 at 3:26 am

  This was removed by the editor as SPAM

• November 23, 2011 at 3:53 am

  This was removed by the editor as SPAM

• BoilerJ

  Default port

  Points: 1428

  More actions

  November 23, 2011 at 6:14 am

  #1412109

  Thanks for the reference!