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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy