Importing Data from an EXCEL File
This is a discussion on Importing Data from an EXCEL File within the MS SQL ODBC forums, part of the Microsoft SQL Server category; --> John, Glad to hear you've worked this out. I'm curious - did you leave the registry tweak in after ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 09:42 PM
| ||||
| ||||
 Re: Importing Data from an EXCEL File John, Glad to hear you've worked this out. I'm curious - did you leave the registry tweak in after all? It appears the IMEX=1 setting makes the provider actually obey the registry setting (see http://support.microsoft.com/?id=194124 for some info on IMEX - thanks to oj for first finding this link for me.) SK John Smith wrote: > hi steve, > thanks for your input. > i had made the registry tweaks as you indicated (according to > microsoft's kb-article), but it didn't make any difference. however, > your tip on adding IMEX=1 to the connection string along with > installing office 2003 seem to do the trick. all is well. > > thank you again for all your input. > > sincerely, > ~js > > > Steve Kass wrote: > >> Is the first 255+ character row not near the top of the Excel file? >> >> Check the registry values >> >> HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes >> >> HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows >> >> >> The first should be set to 'text' and the second should be set to a >> number at least as great as >> the row number of the first more-than-256-character row. >> >> Alternatively, you can put a dummy long value at the top of the Excel >> file. >> >> Best I can tell, if the first TypeGuessRows-many values in a column >> are 255 characters or less, >> the column is imported as nvarchar(255). If there is a longer value >> in the column, >> it is imported as ntext. >> >> If it doesn't work the first time, try also adding IMEX=1 to your >> connection string. >> >> SK >> >> John Smith wrote: >> >>> The result of that max(len(ltrim(rtrim(..)))) operation was 255. >>> Sorry, I didn't answer your question. >>> My Query Analyzer was set at 8000 characters per column, from the >>> following settings Options => Results => Maximum characters per >>> column: 8000. >>> >>> Is there any other Excel string identifier for Excel version 97-2000 >>> other than the string "Excel 8.0"? >>> >>> >>> Steve Kass wrote: >>> >>>> See inline. -SK >>>> >>>> John Smith wrote: >>>> >>>>> >>>>> i also had used a similar method (as you suggested) to check the >>>>> data, for example: >>>>> select max(len(ltrim(rtrim([ReallyLongString])))) stringLength >>>>> from [raw.importedData] >>>> >>>> >>>> >>>> >>>> >>>> And what is the result when you do this? >>>> >>>>> >>>>> also, >>>>> select [ReallyLongString] >>>>> from [raw.importedData] >>>>> where len(ltrim(rtrim([ReallyLongString]))) = ( >>>>> select max(len(ltrim(rtrim([ReallyLongString])))) stringLength >>>>> from [raw.importedData] >>>>> ) >>>>> >>>>> then eye-balled the resulting string from the original string in >>>>> the EXCEL file, and sure enough it's truncated. >>>>> >>>> If you eyeballed it in Query Analyzer, my question is still >>>> unanswered. Did you try >>>> changing the Query Analyzer setting I mentioned? >>>> >>>>> >>>>> i guess my next attempt is to reinstall office? >>>>> >>>> Not until you post some actual results that prove the data is being >>>> truncated, I wouldn't. >>>> >>>> I would be surprised if reinstalling office turns out to be the >>>> solution. >>>> >>>>> ~ js. >>>>> >>>>> >>>>> Steve Kass wrote: >>>>> >>>>>> John, >>>>>> >>>>>> Are you certain the data is truncated? If you are looking at it in >>>>>> Query Analyzer, perhaps you have never increased the >>>>>> value of "Maximum Characters per Column" in Tools|Options? >>>>>> The default value for that setting, which governs Query Analyzer >>>>>> output only, is 256. >>>>>> >>>>>> The code you posted here does not import anything, nor does >>>>>> it demonstrate that the values are truncated. Just to be sure, >>>>>> can you post the results of this? >>>>>> >>>>>> select * >>>>>> into #tempfile >>>>>> from OpenRowSet... >>>>>> >>>>>> select top 10 datalength(ReallyLongString), ReallyLongString >>>>>> from #tempfile >>>>>> order by datalength(ReallyLongString) desc >>>>>> >>>>>> >>>>>> Steve Kass >>>>>> Drew University >>>>>> >>>>>> John Smith wrote: >>>>>> >>>>>>> hi hari, >>>>>>> >>>>>>> thank you for your input. >>>>>>> unfortunately, i had already tried that prior to posting my >>>>>>> question and still was not successful (i have office xp, which i >>>>>>> thought should've taken care of that). but i will definitely try >>>>>>> again again to see if i missed something during my checks. >>>>>>> >>>>>>> secondly, i had also followed the method (registry tweak) >>>>>>> outlined in that kb-article from the link you included in your >>>>>>> reply, that is kb-article what i was referring to in my earlier >>>>>>> post (i apologize i wasn't being clear on my question). >>>>>>> >>>>>>> and last but not least, while i will certain try the dts method >>>>>>> again to see if i missed anything, i am looking for a more >>>>>>> programmatic solution using the OPENROWSET or OPENDATASOURCE >>>>>>> functions than the DTS. although, understandably if the DTS >>>>>>> portion works, the function-use should also work. >>>>>>> >>>>>>> the following is what i used in my import code, perhaps you can >>>>>>> point out the error if you see any? >>>>>>> >>>>>>> OPENROWSET attempt: >>>>>>> select * >>>>>>> from OpenRowSet ( >>>>>>> 'Microsoft.Jet.OLEDB.4.0' >>>>>>> , 'Excel 8.0; DATABASE=c:\DataSourceFile.xls' >>>>>>> , 'select * from [Sheet1$]' >>>>>>> ) >>>>>>> /* result: still truncated; >>>>>>> perhaps instead of 'Excel 8.0' I should use a different string >>>>>>> for Excel 97-2000 version? >>>>>>> */ >>>>>>> >>>>>>> OPENDATASOURCE attempt: >>>>>>> select >>>>>>> cast(RowId as int) RowId >>>>>>> , cast([ReallyLongString] as nvarchar(3900)) ReallyLongString >>>>>>> from OpenDataSource( >>>>>>> 'Microsoft.Jet.OLEDB.4.0', >>>>>>> 'Data Source="c:\DataSourceFile.xls";Extended >>>>>>> properties=Excel 8.0' >>>>>>> )...[Sheet1$] >>>>>>> /* result: still truncated; >>>>>>> same deal above, plus OPENDATASOURCE other issues. >>>>>>> */ >>>>>>> >>>>>>> thank you again, >>>>>>> ~js >>>>>>> >>>>>>> >>>>>>> Hari Prasad wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> >>>>>>>> Steps:- >>>>>>>> >>>>>>>> 1. store Excel sheet in 97-2000 format >>>>>>>> >>>>>>>> 2. Select in DTS import Wizard Excel 97/200 as data source >>>>>>>> >>>>>>>> 3. Check "transform" - "create a destination table" - "Edit SQL". >>>>>>>> >>>>>>>> 4. Change CREATE TABLE [dbname].[dbo].[sheet1] ([col1] nvarchar >>>>>>>> (255)) >>>>>>>> >>>>>>>> 5. Change it to nvarchar (1000) or so and click ok and verify. >>>>>>>> >>>>>>>> 6. If you have pure Excel 2000 format you shouldnt have any issues >>>>>>>> >>>>>>>> If you still have issues then ; have a look into the below KB >>>>>>>> article:- >>>>>>>> >>>>>>>> http://support.microsoft.com/default...;EN-US;Q189897 >>>>>>>> >>>>>>>> Thanks >>>>>>>> Hari >>>>>>>> SQL Server MVP >>>>>>>> >>>>>>>> >>>>>>>> "John Smith" <.@.> wrote in message >>>>>>>> news:z1Dae.5207$JB.821@tornado.socal.rr.com... >>>>>>>> >>>>>>>>> hi all, >>>>>>>>> >>>>>>>>> first of all, i apologize for posting this in multiple forums; >>>>>>>>> i don't know which is the most appropriate to direct this >>>>>>>>> question to. >>>>>>>>> >>>>>>>>> on to business... i am trying to import data from an excel >>>>>>>>> file using the OPENROWSET function in sql server. >>>>>>>>> unfortunately, the results of some fields were truncated to >>>>>>>>> 255 character-length. >>>>>>>>> >>>>>>>>> here's what i've done so far: >>>>>>>>> 1. tried using other function such as OPENDATASOURCE instead, >>>>>>>>> but that function not only gives the same problem, it has its >>>>>>>>> own set of issues too (like when the field was cast to a sql >>>>>>>>> server's datatype). so i threw out this option. >>>>>>>>> 2. tried using dts (same problem, plus i'd like to automate it >>>>>>>>> as a t-sql procedure instead). >>>>>>>>> 3. tweaking the registry setting for >>>>>>>>> HKEY..MACHINE/Software/..Jet/Excel etc. as suggested from a >>>>>>>>> Microsoft knowledge-base article. >>>>>>>>> >>>>>>>>> no-dice. >>>>>>>>> do you have an answer to this problem? or, perhaps a different >>>>>>>>> approach to importing data from excel? >>>>>>>>> >>>>>>>>> >>>>>>>>> thank you very much, >>>>>>>>> ~ js >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>      |
|
02-27-2008, 09:42 PM
| |||
| |||
| Re: Importing Data from an EXCEL File yes steve, i left them in the registry ImportMixedTypes: Text TypeGuessRow: 0 (hex) 0 is to indicate that it needs to scan all fields, i think. i notice your email is drew.edu; are you an instructor/professor? ~ js Steve Kass wrote: > John, > > Glad to hear you've worked this out. I'm curious - did you leave the > registry tweak in after all? It appears the IMEX=1 setting makes the > provider actually obey the registry setting > > (see http://support.microsoft.com/?id=194124 for some info on IMEX - > thanks to oj for first finding this link for me.) > > SK > > John Smith wrote: > >> hi steve, >> thanks for your input. >> i had made the registry tweaks as you indicated (according to >> microsoft's kb-article), but it didn't make any difference. however, >> your tip on adding IMEX=1 to the connection string along with >> installing office 2003 seem to do the trick. all is well. >> >> thank you again for all your input. >> >> sincerely, >> ~js >> >> >> Steve Kass wrote: >> >>> Is the first 255+ character row not near the top of the Excel file? >>> >>> Check the registry values >>> >>> HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes >>> >>> HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows >>> >>> >>> The first should be set to 'text' and the second should be set to a >>> number at least as great as >>> the row number of the first more-than-256-character row. >>> >>> Alternatively, you can put a dummy long value at the top of the Excel >>> file. >>> >>> Best I can tell, if the first TypeGuessRows-many values in a column >>> are 255 characters or less, >>> the column is imported as nvarchar(255). If there is a longer value >>> in the column, >>> it is imported as ntext. >>> >>> If it doesn't work the first time, try also adding IMEX=1 to your >>> connection string. >>> >>> SK >>> >>> John Smith wrote: >>> >>>> The result of that max(len(ltrim(rtrim(..)))) operation was 255. >>>> Sorry, I didn't answer your question. >>>> My Query Analyzer was set at 8000 characters per column, from the >>>> following settings Options => Results => Maximum characters per >>>> column: 8000. >>>> >>>> Is there any other Excel string identifier for Excel version 97-2000 >>>> other than the string "Excel 8.0"? >>>> >>>> >>>> Steve Kass wrote: >>>> >>>>> See inline. -SK >>>>> >>>>> John Smith wrote: >>>>> >>>>>> >>>>>> i also had used a similar method (as you suggested) to check the >>>>>> data, for example: >>>>>> select max(len(ltrim(rtrim([ReallyLongString])))) stringLength >>>>>> from [raw.importedData] >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> And what is the result when you do this? >>>>> >>>>>> >>>>>> also, >>>>>> select [ReallyLongString] >>>>>> from [raw.importedData] >>>>>> where len(ltrim(rtrim([ReallyLongString]))) = ( >>>>>> select max(len(ltrim(rtrim([ReallyLongString])))) stringLength >>>>>> from [raw.importedData] >>>>>> ) >>>>>> >>>>>> then eye-balled the resulting string from the original string in >>>>>> the EXCEL file, and sure enough it's truncated. >>>>>> >>>>> If you eyeballed it in Query Analyzer, my question is still >>>>> unanswered. Did you try >>>>> changing the Query Analyzer setting I mentioned? >>>>> >>>>>> >>>>>> i guess my next attempt is to reinstall office? >>>>>> >>>>> Not until you post some actual results that prove the data is being >>>>> truncated, I wouldn't. >>>>> >>>>> I would be surprised if reinstalling office turns out to be the >>>>> solution. >>>>> >>>>>> ~ js. >>>>>> >>>>>> >>>>>> Steve Kass wrote: >>>>>> >>>>>>> John, >>>>>>> >>>>>>> Are you certain the data is truncated? If you are looking at it in >>>>>>> Query Analyzer, perhaps you have never increased the >>>>>>> value of "Maximum Characters per Column" in Tools|Options? >>>>>>> The default value for that setting, which governs Query Analyzer >>>>>>> output only, is 256. >>>>>>> >>>>>>> The code you posted here does not import anything, nor does >>>>>>> it demonstrate that the values are truncated. Just to be sure, >>>>>>> can you post the results of this? >>>>>>> >>>>>>> select * >>>>>>> into #tempfile >>>>>>> from OpenRowSet... >>>>>>> >>>>>>> select top 10 datalength(ReallyLongString), ReallyLongString >>>>>>> from #tempfile >>>>>>> order by datalength(ReallyLongString) desc >>>>>>> >>>>>>> >>>>>>> Steve Kass >>>>>>> Drew University >>>>>>> >>>>>>> John Smith wrote: >>>>>>> >>>>>>>> hi hari, >>>>>>>> >>>>>>>> thank you for your input. >>>>>>>> unfortunately, i had already tried that prior to posting my >>>>>>>> question and still was not successful (i have office xp, which i >>>>>>>> thought should've taken care of that). but i will definitely try >>>>>>>> again again to see if i missed something during my checks. >>>>>>>> >>>>>>>> secondly, i had also followed the method (registry tweak) >>>>>>>> outlined in that kb-article from the link you included in your >>>>>>>> reply, that is kb-article what i was referring to in my earlier >>>>>>>> post (i apologize i wasn't being clear on my question). >>>>>>>> >>>>>>>> and last but not least, while i will certain try the dts method >>>>>>>> again to see if i missed anything, i am looking for a more >>>>>>>> programmatic solution using the OPENROWSET or OPENDATASOURCE >>>>>>>> functions than the DTS. although, understandably if the DTS >>>>>>>> portion works, the function-use should also work. >>>>>>>> >>>>>>>> the following is what i used in my import code, perhaps you can >>>>>>>> point out the error if you see any? >>>>>>>> >>>>>>>> OPENROWSET attempt: >>>>>>>> select * >>>>>>>> from OpenRowSet ( >>>>>>>> 'Microsoft.Jet.OLEDB.4.0' >>>>>>>> , 'Excel 8.0; DATABASE=c:\DataSourceFile.xls' >>>>>>>> , 'select * from [Sheet1$]' >>>>>>>> ) >>>>>>>> /* result: still truncated; >>>>>>>> perhaps instead of 'Excel 8.0' I should use a different string >>>>>>>> for Excel 97-2000 version? >>>>>>>> */ >>>>>>>> >>>>>>>> OPENDATASOURCE attempt: >>>>>>>> select >>>>>>>> cast(RowId as int) RowId >>>>>>>> , cast([ReallyLongString] as nvarchar(3900)) ReallyLongString >>>>>>>> from OpenDataSource( >>>>>>>> 'Microsoft.Jet.OLEDB.4.0', >>>>>>>> 'Data Source="c:\DataSourceFile.xls";Extended >>>>>>>> properties=Excel 8.0' >>>>>>>> )...[Sheet1$] >>>>>>>> /* result: still truncated; >>>>>>>> same deal above, plus OPENDATASOURCE other issues. >>>>>>>> */ >>>>>>>> >>>>>>>> thank you again, >>>>>>>> ~js >>>>>>>> >>>>>>>> >>>>>>>> Hari Prasad wrote: >>>>>>>> >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> >>>>>>>>> Steps:- >>>>>>>>> >>>>>>>>> 1. store Excel sheet in 97-2000 format >>>>>>>>> >>>>>>>>> 2. Select in DTS import Wizard Excel 97/200 as data source >>>>>>>>> >>>>>>>>> 3. Check "transform" - "create a destination table" - "Edit SQL". >>>>>>>>> >>>>>>>>> 4. Change CREATE TABLE [dbname].[dbo].[sheet1] ([col1] nvarchar >>>>>>>>> (255)) >>>>>>>>> >>>>>>>>> 5. Change it to nvarchar (1000) or so and click ok and verify. >>>>>>>>> >>>>>>>>> 6. If you have pure Excel 2000 format you shouldnt have any issues >>>>>>>>> >>>>>>>>> If you still have issues then ; have a look into the below KB >>>>>>>>> article:- >>>>>>>>> >>>>>>>>> http://support.microsoft.com/default...;EN-US;Q189897 >>>>>>>>> >>>>>>>>> Thanks >>>>>>>>> Hari >>>>>>>>> SQL Server MVP >>>>>>>>> >>>>>>>>> >>>>>>>>> "John Smith" <.@.> wrote in message >>>>>>>>> news:z1Dae.5207$JB.821@tornado.socal.rr.com... >>>>>>>>> >>>>>>>>>> hi all, >>>>>>>>>> >>>>>>>>>> first of all, i apologize for posting this in multiple forums; >>>>>>>>>> i don't know which is the most appropriate to direct this >>>>>>>>>> question to. >>>>>>>>>> >>>>>>>>>> on to business... i am trying to import data from an excel >>>>>>>>>> file using the OPENROWSET function in sql server. >>>>>>>>>> unfortunately, the results of some fields were truncated to >>>>>>>>>> 255 character-length. >>>>>>>>>> >>>>>>>>>> here's what i've done so far: >>>>>>>>>> 1. tried using other function such as OPENDATASOURCE instead, >>>>>>>>>> but that function not only gives the same problem, it has its >>>>>>>>>> own set of issues too (like when the field was cast to a sql >>>>>>>>>> server's datatype). so i threw out this option. >>>>>>>>>> 2. tried using dts (same problem, plus i'd like to automate it >>>>>>>>>> as a t-sql procedure instead). >>>>>>>>>> 3. tweaking the registry setting for >>>>>>>>>> HKEY..MACHINE/Software/..Jet/Excel etc. as suggested from a >>>>>>>>>> Microsoft knowledge-base article. >>>>>>>>>> >>>>>>>>>> no-dice. >>>>>>>>>> do you have an answer to this problem? or, perhaps a different >>>>>>>>>> approach to importing data from excel? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> thank you very much, >>>>>>>>>> ~ js >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> |
|
02-27-2008, 09:42 PM
| ||||
| ||||
| Re: Importing Data from an EXCEL File oh yeah... http://www.drew.edu/directory/people...ss&dept=MA TH -- -oj "John Smith" <.@.> wrote in message news:3Okbe.5978$CH.3306@news-wrt-01.socal.rr.com... > yes steve, i left them in the registry > ImportMixedTypes: Text > TypeGuessRow: 0 (hex) > 0 is to indicate that it needs to scan all fields, i think. > > i notice your email is drew.edu; are you an instructor/professor? > > ~ js > |
Linear Mode
