Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FROM OPENDATASOURCE with csv file


FROM OPENDATASOURCE with csv file

Author
Message
allen davidson
allen davidson
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 852

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 ...


Vasc
Vasc
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 376

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




Kindest Regards,

Vasc
Alistair Warburton
Alistair Warburton
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

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
allen davidson
allen davidson
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 852

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search