Im trying to convert a stored procedure that reads from Excel to one that reads from csv. For Excel is uses FROM OPENDATASOURCE so If possible this is the only bit I want to modify (least change = least risk). Problem is I cannot get the syntax right.
My .csv looks like this
path and name are C:\Temp\Test.csv
My query looks like this
CAST([Col1] AS VARCHAR(50)),
CAST([Col2] AS VARCHAR(50))
'Data Source="C:\Temp";User ID=Admin;Password=;Extended properties=''TEXT;HDR=YES''')..."Test.csv"
Error message is this:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Test.csv'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Test.csv'].
I have a bit of time presssure on this so I think I've missed something obvious!