March 28, 2006 at 2:43 am
Hi all,
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
'Col1','Col2'
'1314','0508ODK01A'
'1314','0508ODK01A'
'1314','0508ODK01A'
path and name are C:\Temp\Test.csv
My query looks like this
SELECT
CAST([Col1] AS VARCHAR(50)),
CAST([Col2] AS VARCHAR(50))
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'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!
Help ...
March 28, 2006 at 10:52 am
You ll have to use a different driver to connect to csv file.
This is new command :
select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from Test.csv')
If you want more formating you ll need to use a schema.ini file
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
schema.ini (same folder as Test.csv file!)
[test.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI
Col1=COL1 Char Width 255
Col2=COL2 Char Width 255
Regards,
Vasc
Vasc
March 28, 2006 at 6:39 pm
You might want to considder using BCP to grab each line of the file into a single column.
if you do thst you can parse the CSV first to sort out any errors before you do the actualy import.
Alistair Warburton
March 29, 2006 at 3:11 am
Hi all - thanks for the help.
I eventually bit the bullet and went down the DTS route.
Vasc - Thank for the info on changes to the driver - it looks like it just got more work!
Allen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply