Question Why my imported sql server 2000 data become null

[newdbo]

i'm trying to import xlsx into sql server 2000 table, but there are null values ALTHOUGH all the data have values. in xlsx there are general data format, no restriction values, but i already used "IMEX=1". Why does it's still happened?
this is my connection string,

Dim CnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & txtfName.Text & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES; IMEX=1;"""

any idea will be appreciated..
Thanks in advance

 

[jmcilhinney]

The Jet OLEDB provider only checks the first 16 rows by default to determine the data type. I'm guessing ACE does the same. You can change that behaviour for Jet in the Registry. Again, ACE is probably the same, although I'm not sure. I'll check it out and get back to you.

 

[newdbo]

Yes, i heard about set the registry, but i'm not really sure which registry values that i have to change, another option is change xlsx/xls format into csv , and then convert it into sql server format. All columns will be varchar formatted and i have to update all the columns and data type. I'd prefer change it directly from xlsx/xls into sql server table. Any idea will be appreciated.

 

[jmcilhinney]

www.connectionstrings.com tells you where to look in the Registry for the Jet OLEDB provider:

Excel Connection String Samples - ConnectionStrings.com

The equivalent page for the ACE OLEDB provider has no equivalent information:

Excel 2007 Connection String Samples - ConnectionStrings.com

Using a bit of logic, I searched my Registry for the value named on that first page, i.e. TypeGuessRows. I found it under the following keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Lotus
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Lotus

Obviously the first three are irrelevant because they relate to Jet and not ACE, and the last relates to Lotus and not Excel. The fourth key looks promising though. Keep in mind that I'm on Win7 x64, so your Registry may not be exactly the same as mine. On a 32-bit system the key of interest would presumably be:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

but you should probably search your Registry anyway. I haven't tested it but I would guess that setting the appropriate value under that key will do the trick.

 

[newdbo]

I'm trying all your suggestions, still it doesnt solved the error. I found many null values, but i really appreciate your help.