|
zain2
Starting Member
16 Posts |
 Posted - 2011-03-10 : 06:33:39
|
| Error: Microsoft.Jet.OLEDB.4.0" has not been registered.I am trying to import data from csv or xls file both giving me the save error. How can i find which OLEDB.4.0 registered on the server, so i could use the relevant OLEDB version?select * into Testing FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\labdata\testing.csv;HDR=YES', 'SELECT * FROM [Sheet1$]')Question Two:I got a wierd csv file which i had to import into SQLit comes with delimited and commas as follows:testing.csvFname Lname DOB Tests Address Country Scott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,",25 Avenue Texas,USScott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,,,",25 Avenue Texas,USScott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,",25 Avenue Texas,USScott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,,,",25 Avenue Texas,USScott,Treed,10/01/1950,"hhh,bbb,ccc,eeee,,",25 Avenue Texas,USScott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,,,",25 Avenue Texas,USScott,Treed,10/01/1950,"ABC,CDE,FFFF,GGGG,KKKK,,",25 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,",15 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,,",15 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,",15 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,,",15 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,",15 Avenue Texas,USXYZ,Johnson,10/01/1940,"ABC,CDE,FFFF,GGGG,KKKK,,",15 Avenue Texas,USWhen i tried to import using Management Studio, the data getting imported but not in the relevant columns, specially because of column three which requires split into predifined columns Test1,Test2,Test3,Test4,Test5 and address into address column so on...However, i created a sample data within SQL and manage to split column3 within SQL. it would have been lot easier if OLEDB works this could have resolved my problem. select Fname,dbo.fnSplitString(-1, ',', Replace(Tests,'"','')) As Test,dbo.fnSplitString(-2, ',', Tests) as Test1,dbo.fnSplitlString(-3, ',', Tests) as Test2from #TUnfortunatley, import issue is the biggest obstacle as i can't run bcp utility or Bulk insert on the server due to security restrictions put into place. The Data administrator is not willing to open this for me.Help HelpMy manager face is going yellow, greeeeeeen, red, pale and god knows what... |
|
TSQL import data