Import from Excel Regedit

[sekarpc]

I got a new computer with Windows 10 and Office 19. I have to import from Excel all the time and in my previous computer with Windows 10 and Office 16, I had set the typeguessrows to 0, in order that the import worked properly (sometimes the first few rows are empty for some variables). I thought that I figured where the typeguessrows was in the new one and set it to 0. Yet, the import is not working properly. I wanted to attach a screen shot, but I am unable to do so.

It was at

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

I set the TypeGuessRows to 0 there, yet the import does not work right.

Anyone can help me solve this problem?

[Reeza]

What does the 'import does not work right' mean?

Did you upgrade SAS as well?

Did you make sure to set VALIDVARNAMES to V7? If you're getting spaces in your name this is why.

[sekarpc]

In answer to what does the "import does not work right" mean:

For instance, a particular variable named "Date_Interview_Received" has certain texts-some of them are dates, but there are some that are not. For instance I know and I see in the original excel file that one entry says "Family declined Interview". By setting the typeguessrows to 0, the whole column (in my previous computer) would import as text and then I will deal with it all later. But I see that the import has decided that the column is all dates and it has not imported as text. That particular person has missing under date interview received rather than that text.

Do I make sense?

[Reeza]

Yes, but unfortunately with Excel files, SAS has to guess at types and it doesn't guarantee that the guesses will be the same. You did not answer the question if SAS updated as well. If you updated your SAS version, I would expect to see this behaviour.

[sekarpc]

I do not think that I updated my SAS. It was 9.4 before and it is 9.4 now. However, in the previous machine, it was SAS 9.4 TS Level 1M4 and in the new one it is SAS 9.4 TS Level 1M6.
I don't know if that makes a difference.
Padmini

[Reeza]

Yes, you've changed both your Excel and SAS versions so I don't think expecting the same behaviour in a guessing procedure is a reasonable expectation at this point. SAS is always updating the Excel engine so hopefully you're getting better data but relying on Excel formats is not a good idea in any language.

[PGStats]

Shouldn't you be changing the typeGuessRows value at

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\19.0\Access Connectivity Engine\Engines\Excel ?

[sekarpc]

Actually, I have solved the problem-SAS technical support helped me. Interestingly, it was Office\15.0\Access Connectivity Engine\engines\Excel that worked. There is no 19.0 (I looked for it before all this). There is no logic behind this. I am glad I got it to work, but I would not have guessed for the world.
Thanks to you all.

[Reeza]

Yeah, if you're on 365 it updates often and the version numbers do not match the 'displayed version' numbers so that doesn't help any.