External Data - Mixed Data Types
This is a newsgroup post from OneDayWhen. He’s an expert (in my opinion) on External Data. The issue he discusses is that of mixed data types. If you have, for instance, both strings and numerics in one external data column, you can get unexpected results - like some of your data doesn’t show up.
This is a common problem in the newsgroups and this is the best post I’ve ever seen on the subject. As OneDayWhen says himself, it needs looking after. Therefore, I’m posting it here in its entirety for all time.
Here my notes on the subject: (look after them; as my granddad used to
say, ‘I won’t be around forever, there’ll be one day when you’ll have
to fend for yourself, sonny’):The relevant registry keys (for Jet 4.0) are in:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
The ImportMixedTypes registry key is always read (whether it is
honored is discussed later). You can test this by changing the key to
ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the
error, ‘Invalid setting in Excel key of the Engines section of the
Windows Registry.’ The only valid values are:ImportMixedTypes=Text
ImportMixedTypes=Majority TypeData type is determined column by column. ‘Majority Type’ means a
certain number of rows (more on this later) in each column are scanned
and the data types are counted. Both a cell’s value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
There’s a bias in favor os numeric in the event of a tie. Rows from
any minority data types found that can’t be cast as the majority data
type will be returned with a null value.For ImportMixedTypes=Text, the data type for the whole column will be:
Jet (MS Access UI): ‘Text’ data type
DDL: VARCHAR(255)
ADO: adWChar (’a null-terminated Unicode character string’)Note that this is distinct from:
Jet (MS Access UI): ‘Memo’ data type
DDL: N/A
ADO: adLongVarWChar (’a long null-terminated Unicode string value’)ImportMixedTypes=Text will curtail text at 255 characters as ‘Memo’ is
cast as ‘Text’. For a column to be recognized as ‘Memo’, majority type
must be detected, meaning the majority of rows detected must contain
256 or more characters.But how many rows are scanned for each column before is decided that
mixed and/or what the majority type is? There is a second registry
Key, TypeGuessRows. This can be a value from 0-16 (decimal). A value
from 1 to 16 inclusive is the number of rows to scan. A value of zero
means all rows will be scanned.There is one final twist. A setting of IMEX=1 in the connection
string’s extended property determines whether the ImportMixedTypes
value is honored. IMEX refers to IMport EXport mode. There are three
possible values. IMEX=0 and IMEX=2 result in ImportMixedTypes being
ignored and the default value of ‘Majority Types’ is used. IMEX=1 is
the only way to ensure ImportMixedTypes=Text is honored. The resulting
connection string might look like this:Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\ db.xls;
Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′Finally, although it is mentioned in MSDN articles that MAXSCANROWS
can be used in the extended properties of the connection string to
override the TypeGuessRows registry keys, this seems to be a fallacy.
Using MAXSCANROWS=0 in this way never does anything under any
circumstances. Put another way, is has just the same effect as putting
ONEDAYWHEN=0 in the extended properties, being none (not even an
error!). The same applied to ImportMixedTypes i.e. can’t be used in
the connection string to override the registry setting.In summary, use TypeGuessRows to get Jet to detect whether a ‘mixed
types’ situation exists or use it to ‘trick’ Jet into detecting a
certaint data type as being the majority type. In the event of a
‘mixed types’ situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as ‘Text’
(max 255 characters).
OneDayWhen is a bit of a legend in this area, he once gave me a rosting when i gave some bad advice to a post in a news group. I was talking rubbish mind.
onedaywhen AKA Jamie Collins:
http://groups.google.com/groups?&selm=2ed66b75.0406030305.76ce4cf9%40posting.google.com
re: Excellent resource on Excel
Can you set the TypeGuessRows without going to the registry? I have tried to put it in the extended properties without any luck.
This KB article also gives this type of relevant information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;257819
How To Use ADO with Excel Data from Visual Basic or VBA
–
Regards,
Tom Ogilvy
Nice article….
But my problem is,
in my database field, the datatype is Text. but in excel ordinary users may enter numeric values also.. because a number is a text…
the users are started data entry…. so i can’t set the datatype of a column in excel….
what i do in such a situation….
I have to implement a similar strategy to what excel does in determining the datatypes.
i have implemented it similarly to excel but it
is not as fast as excel. i am always considering 25% of total rows to determine the type.
Does excel consider whole rows when i change the registry key or not to zero.
If there is some alternate strategy do let me know.
“Does excel consider whole rows when i change the registry key”
No, it looks at each column individually e.g. the formatting and values of column A never have any affect in determining the data type of column B.
Jamie.
–
“my problem is, in my database field, the datatype is Text. but in excel ordinary users may enter numeric values”
Bijoy,
You would set your registry keys to GuessTypeRows=0 (zero) and ImportMixedTypes=Text. In your query, cast the values as text e.g.
SELECT CSTR(MyExcelCol) AS MyTextCol FROM [A:A];
If all the values are numeric, the data type will be determined as Double, no numeric values will be lost as nulls and the query will convert them to strings.
If there is a single Text value anywhere in the column, the data type will be determined as Text and all numeric values will be cast as Text on the Excel side as well as in the query. This will add overhead but should ensure your resulting column is always of type Text.
Jamie.
–
“Can you set the TypeGuessRows without going to the registry?”
Sadly, no:
it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy.
Jamie.
–
AAAHH
I am a complete novice how do I change the registry to allow me to return the mied data type values. I have encountered the problems listed in this article but have no idea how to correct it. Any assistance (prefferably in a format that an idiot can follow) would be greatly appreciated
“in a format that an idiot can follow”
Windows Start button, Run, type ‘regedit’ (no quotes) in the box and hit OK.
In Registry Editor, collapse the treeview so you can just see the HKEYs that hang off My Computer. Expand the following (each / separated item is a new level in the tree):
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
It the pane on the right you should now see a list, including TypeGuessRowss. Double click this name, which should bring up a dialog. Change the value to 0 (zero). Hit OK to confirm the change.
Check the list again for ImportMixedTypes. Check that the value reads ‘Text’ (in the unlikely event the value is anything else, double click and amend as before).
In your app, ensure you are using IMEX=1 (or equivalent) in your connection string. If you are using MS Access (e.g. import specs), this setting is automatic.
Test you app so see if this has improved your situation. Note that the resulting column will be curtailed at 255 characters, assuming a mixed type situation is actually detected.
I hope this helps. Please post back with any specific issues you may have.
Jamie.
–
Jamie
A great help thanks just one thing how do I ensure I am using IMEX=1 in my app> Step by step as above would be greatly appreciated.
Cheers
“how do I ensure I am using IMEX=1 in my app”
How is you app accessing Excel data: ADO, MSQuery, linked table, etc?
Jamie.
–
I am using MS query to access data from multiple excel files to one master workbook
I assume you are using the ODBC driver. I’ve yet to find a way to get IMEX=1 to take effect when used in the connection string for the ODBC driver.
There are a couple of workarounds.
First, you could change your connection from ODBC to OLEDB. Being a code monkey myself, I only know how to do this in code.
With a copy of you workbook open, navigate to the Visual Basic Editor (VBE): from the main Excel menu, choose: Tools, Macro, Visual Basic Editor (or just press alt+F11).
In the VBE, ensure the Immediate Window is visible: from the VBE menu, choose: View, Immediate Window (or just press ctrl+G). By default, the Immediate Window is docked to the bottom right of the VBE. Important note: an Excel workbook is in permanent Debug mode, so any code you execute in the Immediate Window will have immediate effect. Always keep a (closed) copy of your workbook as a backup.
Take a look at you current connection string: type something like this in the Immediate Window (? means Print) then, with the cursor on the same line, hit return to execute it:
? ThisWorkbook.Worksheets(”Sheet1″).QueryTables(1).Connection
You may need a different sheet name and/or index number to the QueryTables collection.
Change the connection string by executing something like this:
ThisWorkbook.Worksheets(”Sheet1″).QueryTables(1).Connection = “OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Tempo\db.xls;Extended Properties=’Excel 8.0;HDR=YES;IMEX=1′;”
That should be all on one line, with a space between the words Extended Properties.
Now refresh your query:
ThisWorkbook.Worksheets(”Sheet1″).QueryTables(1).Refresh
If yours is anything like mine, this will have no effect. It seems to be something to do with my query text being in MSQuery’s own style e.g.
? ThisWorkbook.Worksheets(”Sheet1″).QueryTables(1).CommandText
SELECT `Sheet1$`.MyMixedCol
FROM `C:\Tempo\db`.`Sheet1$` `Sheet1$`
Change it to something more like a human would write:
ThisWorkbook.Worksheets(”Sheet1″).QueryTables(1).CommandText = “SELECT MyMixedCol FROM [Sheet1$];”
As ever, that should be all on one line. After a further refresh, I have my mixed column converted to text as expected.
If for some reason you want to stick with ODBC, there is a second workaround. It is to change (e.g. manually in MSQuery) you SQL text to contain ODBCDirect connection strings, where IMEX=1 may be specified and noticed e.g.
SELECT MyMixedCol
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Tempo\db.xls;].[Sheet1$];
Note that for both workarounds, MSQuery can no longer display the query ‘graphically’ (whatever that means) and therefore you will lose in-build support for parameters.
Jamie.
–
Thanks for the help Jamie unfortunatley I am having no success with either when I enter the code in Visual basic I get a runtime 9 error. And I can’t seem to get the command accepted in sql herer is a copy of the sql statment for your info any assistance is greatly appreciated.SELECT `’Current Activities$’`.`Pob’s`, `’Current Activities$’`.Area, `’Current Activities$’`.Ward, `’Current Activities$’`.`Service Area`, `’Current Activities$’`.`Officer/Contact Person`, `’Current Activities$’`.Venue, `’Current Activities$’`.Sport, `’Current Activities$’`.Category, `’Current Activities$’`.`Coach(es)`, `’Current Activities$’`.`Start Date`, `’Current Activities$’`.`Finish Date`, `’Current Activities$’`.Day, `’Current Activities$’`.`Start Time`, `’Current Activities$’`.`Finish Time`, `’Current Activities$’`.`Age-group`, `’Current Activities$’`.`Avg Att (Current Period)`, `’Current Activities$’`.Cost, `’Current Activities$’`.`Known Development of Activity`, `’Current Activities$’`.Observations, `’Current Activities$’`.`Work To Cease (Reason)`, `’Current Activities$’`.`Work To Continue (Reason)`, `’Current Activities$’`.`Action (Obs)`, `’Current Activities$’`.Recommendation, `’Current Activities$’`.`Action (Rec)`, `’Current Activities$’`.Lead, `’Current Activities$’`.Timescale
FROM `C:\Audit\Audit update\Active Living Audit`.`’Current Activities$’` `’Current Activities$’`
WHERE (`’Current Activities$’`.`Pob’s` Is Not Null)
ORDER BY `’Current Activities$’`.Sport, `’Current Activities$’`.Category, `’Current Activities$’`.Venue
Try the following:
SELECT [Pob’s], Area, Ward, [Service Area],
[Officer/Contact Person], Venue, Sport, Category,
[Coach(es)], [Start Date], [Finish Date], [Day],
[Start Time], [Finish Time], [Age-group],
[Avg Att (Current Period)], Cost,
[Known Development of Activity], Observations,
[Work To Cease (Reason)],
[Work To Continue (Reason)], [Action (Obs)],
Recommendation, [Action (Rec)], Lead, Timescale
FROM [Current Activities$]
WHERE [Pob’s] IS NOT NULL
ORDER BY Sport, Category, Venue;
Best to avoid tricky characters (quotes, spaces, etc) in names for columns, tables, etc. Check you have the correct character for [Pob’s]; I can’t decide whether you need Chr$(39) or Chr$(146).
Jamie.
–
Hello!
Impressive knowledge on Excel I must say. Let me get to the point:
Problem: I am trying to import an excel sheet into a SQL database. I have set GuessTypeRows=0 (zero) and ImportMixedTypes=Text in the registry. When I put IMEX=1 in my connect string OPENDATASOURCE(”Microsoft.Jet.OLEDB.4.0”, ”Data Source=C:\’ + rtrim(@FileName) + ‘.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1”)…[Sheet1$]‘
I get the following error: Could not find installable ISAM.
The latest DLL is out there and everything else looks fine to me. However, the server that SQL is on does not have Office installed. The Office package on my local machine is Office 2003. Do you think either of these could be a problem?
The other option I will be working on would be to create a linked server to the excel sheet on the fly and drop it after my import is done.
Any input on this would be appreciated.
Thanks!
Ronjoy.
’Data Source=C:\’ + rtrim(@FileName) + ‘.xls
I suspect this part is the culprit i.e. I don’t think you can use a parameter value here. I usually use a SQL script for this kind of thing and use code (e.g. VBA) to change the filename/path in the script.
Jamie.
–
I’d like to import some information from Excel to a Database, but my problem is that the numbers in the worksheet are formatted for example like this:
-5000001.987 —> -5,000,002
in the worksheet I have mixed data, text and numbers.
When I use IMEX=1, all the numbers that I get are the formatted and not the real ones, and It loses meaning.
In other cases, I lose all the text or all the numbers.
Please, help me
Well, I discovered something new today: when the column, with NumberFormat = “#,##0″, is ’seen’ as FLOAT (numeric), the value is -5000001.987, but when ’seen’ as TEXT, it becomes -5,000,002 i.e. the NumberFormat is applied. This would cause problems if you wanted to see text values such as ‘Hello world!’, which remain unaffected by the NumberFormat, and numeric values without the NumberFormat being applied. There is no reliable way of deriving -5000001.987 from ‘-5,000,002′
The only solution I can see is to remove the column’s NumberFormat. It’s obviously too much of a luxury to have different formats for the Excel UI view and the Jet view respectively
Jamie.
–
I need to suppress the Jet row scan on all non-database imports - such as text and Excel, Lotus, etc., and force all columns to text.
Can anyone think of a way to do this without setting the registry on each machine? Since Microsoft allowed IMEX=1, could there be another hidden keyword to set the rowscan to 0 and the default data type to Text?
Obviously, I’d like to do this programmatically in the connection string.
Any ideas?
“Since Microsoft allowed IMEX=1, could there be another hidden keyword to set the rowscan to 0 …”
I covered this already:
although it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy.
Wishful thinking, then
“… and the default data type to Text”
Considering the only other legal value is ‘Majority Type’, you can be fairly certain this setting will be ‘Text’ for a given machine.
Jamie.
–
Hi Dick,
I have an excel sheet where one of the columns has mixed values. I am using the Jet4.0 driver.
1. I have IMEX=1 in the connection string
2. ImportMixedTypes = Text
3. I initially had TypeGuessRows = 8. In the excel sheet, the first 6 rows out of first 8 rows are numeric and the remaining 2000 rows are text. So with this setting, it seemed the first 8 rows were scanned and the datatype was determined to be numeric, hence only the first 6 rows got loaded. Rest returned null.
4. Then I got this changed to TypeGuessRows = 0. Now the situation got reversed. The driver scanned the entire sheet, determined that the majority datatype is text and loaded those, and returned null for numeric value. This doesnt seem to make sense with what is described above. In this situation shouldnt the driver try to cast the numeric rows as text instead of returning null ?
Will greatly appreciate, if you can reply to my email address. Does OneDayWhen has a site of his own ?
Thanks,
Sunil
Sunil: “shouldnt the driver try to cast the numeric rows as text instead of returning null?”
Correct. Here’s a quick test to demonstrate the point. If you are getting NULL values in the results set then you are seeing something that I’m not:
Sub TextMixedTypes()
Dim Con As Object
Set Con = CreateObject(”ADODB.Connection”)
With Con
‘ Create new xls file and sheet
.ConnectionString = _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Extended Properties=’Excel 8.0;HDR=NO’;” & _
“Data Source=TestMixedTypes.xls”
.Open
.Execute _
“CREATE TABLE TestMixedTypes (mixed_col FLOAT NULL);”
‘ Create mixed types data
.Execute _
“UPDATE [TestMixedTypes$A2:A2] SET F1 = 1.234;”
.Execute _
“UPDATE [TestMixedTypes$A3:A3] SET F1 = ‘Hello’;”
.Execute _
“UPDATE [TestMixedTypes$A4:A4] SET F1 = 2.345;”
.Execute _
“UPDATE [TestMixedTypes$A5:A5] SET F1 = ‘world!’;”
.Execute _
“UPDATE [TestMixedTypes$A6:A6] SET F1 = 3.456;”
‘ Re-open connection with IMEX=1
.Close
.ConnectionString = _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Extended Properties=’Excel 8.0;IMEX=1′;” & _
“Data Source=TestMixedTypes.xls”
.Open
‘ Query mixed types column
Dim rs As Object
Set rs = .Execute( _
“SELECT mixed_col, TYPENAME(mixed_col) FROM [TestMixedTypes$];”)
MsgBox rs.GetString
End With
End Sub
Jamie.
–
Hi ,
I have one problem regarding format of mixed data columns in excel. I am using ADO.NET to retrieve data from excel sheet.
I am able to retreive all data (numaric and text) , but for few cells I am getting wrong values.
excel sheet ado.net converts to
15106749310-> 1.51067e+010
70400672657-> 7.04007e+010
48110753143-> 4.81108e+010
Format is text in excel sheet.
registry setting are “text and 8″. I am also using IMAX=1 in connection string.
above data is in 3rd ,18th and 34th rows respectively.
I have numeric and text data both in same column,
eg:
70400503549
70400672657
KF1080420908
KF1260370908
KF1260880908
KF1260960908
Could you please help me to get the correct value from excel .
thanks in advance ,
Ajaya
Ajaya, Sorry but I can’t reproduce you results in ADO classic using your data as posted. Whether the column is seen as FLOAT and TEXT, I am not getting scientific notation.
Jamie.
—
Hi Jamie,
thanks a lot for responding so fast.
I am using the ADO.NET to get the data.
I found that following values were showing
15106749310.00 , 70400672657.00 ,48110753143.00 in number format and showing 15106749310, 70400672657 , 48110753143 in text format . I think this is causing problem while getting data from excel sheet.
All other values in same column are showing same values for both [number and text] formats in excel and ADO.NET is also picking these values correctly.
I am surprised , ado.net is picking all data correcty
after just double click on the shells those were showing different values for number and text formats [15106749310.00 , 15106749310 ]
but I am still looking for a good solution since users can not double click each cells.
I will be highly thankful to you for suggestting me a better solution :-).
thanks a lot.
Ajaya
Ajaya, There are a number of things in this scenario that I am not clear on:
1) What is the raw data value in the Excel cell e.g. 15106749310 or ‘15106749310 or ‘15106749310.00 or something else?
2) What is the number format of the column and individual cells in question?
3) What is the majority type?
4) What ADO.NET objects are you using (a dataset?) and are they formatting the data post-Excel in any way (I don’t think they would but I can’t rule it out)?
Perhaps this is just too tricky to reproduce i.e. you’d have to post each cell value and format, your column format and the relevant .NET code
As a suggestion, you could try casting your numeric values to the Jet DECIMAL data type. The wacky world of Jet doesn’t have an explicit way of doing so (!!), but you can force it by adding and subtracting a decimal value (e.g. 0.1):
IIF(IsNumeric(mixed_col), mixed_col, -999) + 0.1 - 0.1
AS decimal_col
FROM [MySheet$];
Jamie.
–
Hi Jamie
Thanks a lot for giving me time.
I just append ” ” string at the end of all cells and works fine.
Thanks again.
Ajaya
Jamie’s Tip above was very useful for me. It seemed the only way to make IMEX take effect. Including it in the connection string itself didn’t seem to make any difference.
SELECT MyMixedCol
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Tempo\db.xls;].[Sheet1$];
Thanks Jamie!
Hi I have this code…
StringBuilder sbConn = new StringBuilder();
sbConn.Append(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+file);
sbConn.Append(”;Extended Properties=”);
sbConn.Append(Convert.ToChar(34));
sbConn.Append(”Excel 8.0;IMEX=1″);
sbConn.Append(Convert.ToChar(34));
//
// Open the spreadsheet and query the data.
//
cnnDb = new OleDbConnection(sbConn.ToString());
//string stringconn=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + file + “;”+”Extended Properties=”+”‘Excel 8.0;IMEX=1;’”+”";
//cnnDb = new OleDbConnection(stringconn);
cnnDb.Open();
excelConnected = true;
StringBuilder colString = new StringBuilder(20);
string select = “SELECT DISTINCT [PART ID] ROM ['520 (N)$']“;
int r=0;
OleDbCommand cmdExcel = new OleDbCommand(select, cnnDb);
The values of the register are
ImportMixedTypes:Text
TypeGuessRows=0
and and the result is that I still get records of just a type (in this case the records I have are the following
520.3125
520.3438
520.3750
520.4062
520.4375
520.4688
520.4844
520.5000
520.5312
520.5625
520.5938
520.6250
520.6562
520.6875
520.7188
520.7500
520.7812
520.8125
520.8750
520.9062
520.9375
520_1.0000
520_1.0156
520_1.0625
520_1.1250
520_1.1875
520_1.2500
and I just getting the numbers.. and I want also the numbers with the “_” character…
I really need your Help.
Thanks a lot
sorry the query is
string select = “SELECT DISTINCT [PART ID] FROM [’520 (N)$’]”;
I missed the F in FROM
Thanks again
Emmanuel
Emmanuel,
Sorry but I can’t reproduce. I’m getting the full row set typed as TEXT. Check your registry settings: I suspect TypeGuessRows is not zero. Ensure you are looking at the correct reg key i.e.
should be:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
and not either of
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
Jamie.
–
What if you have a column that is mostly number but at some point you get 041 all the field with some 0 in front just don’t come out. Even if I have the IMEX=1
When you open the xls spreadsheet it give a warning saying number stored as text ! for each of the line the number starts with a 0… Anoying problem
Any though on the subject ?
Martin,
In addition to using IMEX=1, you need to set the TypeGuessRows registry key to zero. This should select all you numbers, even those stored as text, but will convert all values to text. Best practise would be to convert values to numeric (Double, Integer, etc) via your recordset, however you could try casting in the the SQL e.g. to convert to FLOAT (Double):
The above assumes all values can be cast as FLOAT; is not, an error will occur. To be on the safe side, take a two step approach: first, filter out the non-numeric values; second, cast as FLOAT e.g.
Jamie.
–
Thank you Jamie for this forum about TransferSpreadsheet data type control!
It has solved a problem I have wrestled with for ages.
SW
I have a problem to read Excel File.
The Connection string is
FCon.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & FilePath & “;Extended Properties=”"Excel 8.0;HDR=YES;IMAX=1;ImportMixedTypes=Text;”"”
The problem is that When I try to read a column from excel containing Phone numbers having different formates like 123456789 , 12 333 4569745 , 12-333-456789
This excel file is being uploaded by user from different regions, thats why no restriction is on format is there. When I try to read data from it ( Phone No.) It shows some records which are sipmly like 123456789 , but rest of the recoreds return NULL. Kindly helpme in this regard.
Mansoor,
It sounds to me that your TypeGuessRows reg key is something other than zero. See above, “But how many rows are scanned…?”
Jamie.
–
It’s the great blog.
Thank OneDayWhen so much
“IMEX=1 in connect string” is so simple. But It help me so much in my work
thank again
I came across this link (http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/#comment-6670) when googling for ‘TypeGuessRows’. I found the article very helpful. Thanks.
I noticed one name in the blog in particular - Ian Johnston. I think I worked with him at Chartered Trust in Cardiff, Wales 5 years ago and have lost touch. Is it possible for you to forward him my email address so that he can contact me if he wants? I know this is an unusual request and understand if you are not able to do this. Thanks!
Kathy
I am using VC++ (6.0) ODBC to access excel worksheet.
I have similar problem of above. Do all the info above applie to me also (Because you guys are discussing about ADO, while i am only using simple CDatabase class provided in MFC)?
My problem involving one column where the first 50+ rows are empty, then it come text. Due to the data type detection thingy, all the rows that have content (text) become NULL.
Changing MAXSCANROWS if of cuz no use as you guys (and MS site) mentioned, changing TypeGuessRows to 0 seems to fix the problem. However, my issue is that I cannot rely on changing registry to solve this problem. I am writing a piece of software for my clients that need to access excel worksheet. Thus, I cannot change every client’s window registry to achieve the desired effect.
Any alternative? I tried putting IMEX=1 when I set up the database but it seems no effect, i.e.
m_sDsn.Format(”DRIVER={Microsoft Excel Driver (*.xls)};DSN=”;IMEX=1;MAXSCANROWS=0;FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\”mydata.xls\”;DBQ=mydata.xls”, m_sExcelDriver);
m_Database->OpenEx(sDsn, CDatabase::noOdbcDialog);
I also try to modify contents of the sheet with SQL commands in the program (content, text or 0) in the first row of the sheet, and reopen the database so that it have something in the first row, but that doesn’t seems to have any effect at all. Any clue?
when making queryto excel by oledb, it returned null instead of the correct values. The simple IMEX=1 made it work. Thank you for you great help.
If you run across this error thru googling (or whatever search engine).
The ISAM error could (and probably) is caused by a misformed connection string.
String connectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’C:\Sales.xls’;Extended Properties=’Excel 8.0′”;
Here is a C# version.
Notice the single quotes around the filename…AND around the Excel 8.0.
There is a space between Data and Source for “Data Source”
There is a space between Excel and 8.0 for “Excel 8.0″.
With VB.NET, experiment with the single quotes vs. the “”"” (to get one double quote).
This code works in C#… (a very bad example for Data Access Code, but good for the connection string info:
String connectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’C:\Sales.xls’;Extended Properties=’Excel 8.0′”;
OleDbConnection newCon = new OleDbConnection(connectionString);
DataSet myDataSet = new DataSet();
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = newCon;
OleDbDataAdapter myAdapter = new OleDbDataAdapter();
myAdapter.SelectCommand = myCommand;
myCommand.CommandText = “SELECT * FROM [Sheet1$]“;
try
{
newCon.Open();
myAdapter.Fill(myDataSet);
newCon.Close();
}
catch (Exception ex)
{
MessageBox.Show (ex.Message );
}
FYI,
Also:
see
http://support.microsoft.com/default.aspx?scid=kb;EN-US;209805
and if you’re ok here, then I strongly suggest my hints about the double/single quotes.
i am an inexperianced and exasperated programmer trying to fathom out how to get mixed content to show in a query.
i have office 2003 and i have not been able to find the registry string IMEX= ??? so that i can change the value to IMEX=1.
can any one help me
alex Said: “i have not been able to find the registry string IMEX= ???”
IMEX is not a registry setting. It is an extended property of the connection string. See above, “A setting of IMEX=1 in the connection string’s extended property determines whether the ImportMixedTypes [registry key] value is honored.”
Just thought I’d share this. I had an Excel Spreadsheet I was importing that contained a column “Loan Number” which had either a numeric value “35673455 or likewise” or a text value “AE3542 or likewise”… unfortunately, with IMEX=1 the numeric values were coming accross as “35673455e-001″ or some similar abomination. To fix this, I ran each number through a function like this:
Private Function DoubleConvert(ByVal vsLoanNum As String) As String
Try
Return Format(CDbl(vsLoanNum), “#”)
Catch ex As Exception
‘ Loan is not a double
Return vsLoanNum
End Try
End Function
And all worked fine.
This problem has chewed up about 3 hours of my life that I will never get back.
We have similar problem. On some workstation, MS Access 2000 doesn’t recognized the data type form the linked excel table. Even if in the excel file, the column is set to number, in access we see the data type “text”. But this is not the case on every computer. half of the computer see the data type in access like it is in excel “number”
Marc
I have a problem where I use OPENROWSET to insert records from SQLServer into an Excel file. However, the values are always entered as text no matter what the datatype from SQL and even though the Excel file is set to use numberic formatting in all cells. Is there a jet or reg setting that I can use to get Excel to have it entered as text? Thanks.
OpenRowSet (’Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;HDR=Yes; IMEX=1; DATABASE=…
Stu,
How exactly are you using OPENROWSET to insert data into an Excel workbook e.g. INSERT INTO, SELECT..INTO..FROM, etc? Please post your complete SQL query/statement or, better still, post an example which uses pubs or northwind so I can try to reproduce the problem.
Thanks,
Jamie.
–
I have the same problem, and all that is published in this discussion group has not worked yet, here is may code, first I tried:
MyConnection = New System.Data.OleDb.OleDbConnection(”Provider=Microsoft.Jet.OLEDB.4.0;” _
& “data source=” & PrmPathExcelFile _
& “;” & “Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′”)
MyCommand = New System.Data.OleDb.OleDbDataAdapter(”select * from [sheet1$]“, MyConnection)
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
MyConnection.Close()
then I used:
MyConnection = New System.Data.OleDb.OleDbConnection(”Provider=Microsoft.Jet.OLEDB.4.0;” _
& “data source=” & PrmPathExcelFile _
& “;” & “Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′”)
MyCommand = New System.Data.OleDb.OleDbDataAdapter(”select cstr(Facility) as Facility,cstr(Orden) as Orden ,cstr(Estilo) as Estilo,cstr(Color) as Color,cstr(Copa) as Copa,cstr(Talla) as Talla,cstr(Caja) as Caja,cstr(BundleSize) as BundleSize,cstr(QTY) as QTY,cstr(Mdate) as Mdate,cstr(Manifiesto) as Manifiesto,cstr(LP) as LP from [sheet1$]“, MyConnection)
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
MyConnection.Close()
and it still doesn’t work, I have mixed values in one of the columns, so if anybody can help thanx a lot.
Dave
Dave,
Have you check whether the TypeGuessRows reg key is zero? See above, “But how many rows are scanned…?”
Jamie.
–
Isn’t there a way to program an excel file (VBA) to edit the register in the required way?
In my humble opinion: I would not want the data-refresh of my files to be depending on manual overwritten registrysettings. Especially if I cannot check them by code and correct the reg if nescessary… I mean, that would limit the functionality of my files to a few dedicated machines and some lucky machines that for some reason already did have the correct reg settings…
Next to that, I would expect other (existing) files to behave differently after these changes too… I would not want my customer to have trouble with other apps because I needed the reg to be changed for mine… so I think it would be decent to return the reg to the original situation upon exiting your app. That way you won’t do harm to the way other programs work…
How do you guys handle this?
Quandan,
Short answer: Win32 API calls to read the reg keys.
Personally, I think that because my apps do not ‘own’ the reg settings they should not change them (what if my app crashed before I could restore them?); rather, my apps will read the keys and take appropriate action based on the settings the user/administrator has chosen/imposed.
Jamie.
–
Ceci m’a fait le rire:
http://groups.google.com/group/microsoft.public.fr.sqlserver/msg/bd045784862f768b
J’aime.
–
Hey all,
The easiest way I tackled the problem is by creating a macro that will re-sort the data on the problem field descending. This way when Excel guesses on the field type, it will see text first.
Just ensure in the registry that your registry “TypeGuessRows” is low enough that it will assume that the text is the majority field.
L8 All
Rob
Hi,
I came across some problem while opeing a CSV file in Excel.
In CSV file I have following data.
———–
234d23, 1
2342e1, 1
———–
When I open this file in Excel it make first row first column as Text data, where as the second row first column as 2.34E+04
It’s showing it in the scientific data type.
I tried solutions like setting TypeGuessRows=0 and ImportMixedTypes=Text.
But it didn’t work.
I just tried to add few (?) spaces to the data to make it 256 characters long.
And now it’s showing me the data as text.
Found it strange, but workied for.
Can someone tell me why this happed. and is there any better solution?
Because making a column from 10 chars to 256 chars is going to increse my data size too much.
Thanks,
Abhijit Bhosale
Abhijit Bhosale: “I came across some problem while opeing a CSV file in Excel.”
Jamie Collins: The info in this article relates to querying Excel (workbook) data with Jet (incl. Access). The registry settings are not consulted when opening a cvs file in the Excel interface.
A potential solution to your problem is to *import* the data into Excel, rather than opening the csv file directly. With a new/blank workbook open, from the menu choose: Data, Import External Data, Import Data, change the ‘Files of type’ drop-down to Text Files, navigate your csv file and click Open. In the Text Import Wizard, Choose Delimited (Next), check Comma (Next), and on the third screen you have the opportunity to select a column and assign a data type to each. Choosing a data type of Text for your column should resolve the issue.
HTH,
Jamie.
–
I have ran into a problem where another program is changing the format of the text driver in the registry for jet 4.0. The base format is suppose to be CSVDelimited but they change it to Delimited(;). This makes my inserts from text to database no longer work because my text files are using a comma for a seperator. It took long enough to figure out what the other program was doing to mess mine up, but now that I know it I am trying to change it back. I really cant break the other program because then the company would have to make a choice between the two. I am also trying to change it in code by writing to the registry but that does not seem to be working for some reason. So my question is am I coding wrong or does Microsoft protect the registry entries? The other program changes it during the install.
I know the above post isnt Excel but it is Jet and I thought that you would probably be able to help with this one also.
In a VBA function of an Excel workbook I have this (I made it recording and modifying a macro):
Set QT = Sh.QueryTables.Add(Connection:=”ODBC;DSN=Excel Files;DBQ=N:\Tools\ConfigurationsDB.xls;DefaultDir=N:\Tools;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”, Destination:=Range(”A1″))
Everything works fine but the mixed data type.
I tried to add the IMEX parameter like this:
Set QT = Sh.QueryTables.Add(Connection:=”ODBC;DSN=Excel Files;DBQ=N:\Tools\ConfigurationsDB.xls;DefaultDir=N:\Tools;DriverId=790;MaxBufferSize=2048;PageTimeout=5;IMEX=1;”, Destination:=Range(”A1″))
but with no success.
In the registry there was ImportMixedTypes=Text and TypeGuessRows=8.
If I set TypeGuessRows=0 the time required for the query increases by ~30%.
If I set TypeGuessRows=1 it decreases.
I think my error is in the position of the IMEX.
I couldn’t find “Extended Properties” on any QT property.
Can you please help me?
Thanks,
Stefano
PS: This is the first time I use SQL, and everything works fine but the mixed data type.
Hi!
I’m want to read a csv file using ADO and VB. I use:
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & file_path & “;” & _
“Extended Properties=”"Text;HDR=” & hdr & “;FMT=CSVDelimited”"”
objRecordSet.Open “SELECT * FROM [" & file_name & "]“, _
objConnection, adOpenStatic, adLockBatchOptimistic, adCmdText
File can have any count of columns and rows, and i want to read each column as TEXT, I dont want driver to “guess” column types. How can i do that?? Please hepl!!!
Regards
Dena
I’m just amazed - I came here by googling for a simmilar problem and moreover my result, I’m just amazed on how collaborative this guy “Jamie Collins” is… he’s answering all your damn question dudes!!!!!
Tnx buddy, you make the world a better place - way2go
Mauro
Very useful! But I still can’t find solution for my problem.
My column may contain short text(less then 255 chars) or long text(more then 255 chars).
When I set TypeGuessRows to 0 and IMEX=1 it reads texts correctly only in case when there is no numeric values in the column - otherwise it truncates everything to 255 chars. However, if I set datatype for this column in Excel to Text, and retype all “looks like numeric” values(left upper corner of the cell will become green) - it works fine. But I can’t make my clients to update their files, which content 2 000-10 000 records. If somebody see any solution here…
Galina,
S#!%, you’re right! Using:
ImportMixedTypes=Majority Type
TypeGuessRows=0
IMEX=1
I would expect that ‘long text’ values mixed with a single numeric value would choose ‘long text’, being the majority type; however, to my surprise, the result is numeric. Only ‘long text’ seems to exhibit this behaviour, though.
Thanks for posting this anomaly but, sorry, I cannot provide a solution (other than, ‘Don’t use SQL’ ).
Jamie.
–
Hi Jamie,
Great article! It helped me out a lot! We have clients who have a list of their products in Excel sheets and some clients have both numeric and non-numeric values as product numbers. We had the problem that we always seemed to loose either the non-numerics or all the numerics while importing their files.
Now, I’ve just finished some testing and been able to import all the product numbers by setting “TypeGuessRows” and “ImportMixedTypes” in the registry to respectively “0″ and “Text” and by supplying “IMEX=1″ within the query.
However, I do have a question. You explained in the article that “ImportMixedTypes” set to “Text” will truncate texts longer than 255 characters. However, one of the Excel columns holds the product description that has texts longer and shorter than 255 characters. The majority is shorter than 255 characters. So, this column is actually a mixed type of Memo and Text, but since the “ImportMixedTypes” option is set to “Text”, I kind of expected the results to be truncated at 255 characters. I imported the data and found out that the results are defined as Memo, so nothing is truncated. Of course I’m very happy to see this happening, but is not what I expected to happen.
Do you have an explination for this behaviour?
I just did some more testing that may explain this behaviour…
It seems that the Jet Excel ISAM does not treat mixed Text and Memo as different types, so the “ImportMixedTypes” setting is probably not being used, since EVERY row of the product description had non-numeric data. I changed only ONE row to hold a numeric value (123456) and tried to import it again. Now, all my lines who had more than 255 characters were read as NULL values. Now, I kind of expected the ISAM to truncate the values. In stead, it completely “removed” them. Can this be explained then?
How can i use ‘Select Sheet1$.f1,Sheet2$… From [Sheet1$] Inner Join [Sheet1$]
On Sheet1$.f1=Sheet1$.f1……’
I’d like to…
Recieve an error.
Hey,
Great Great article. Helped a lot.
But still have a problem:
I am using ADO.NET to import CUSIPs from excel and then inserting into SQL Server.
I have modified the registery setting and appended IMEX to my query string.
The problem is some CUSIPs are only numbers and some are alphanumeric.
I am getting records for both the cases now but numeric ones are represented scientifically.
Example:
989703202 in excel shows up as 9.89703e+008 in SQL
160908109 in excel shows up as 1.60908e+008 in SQL
I tried to append ” ” but it did not work, tried to add 0.1 if numeric and it showed up as 989703000.1. Its a CUSIP and I want the exact text.
Please help.
Thanks.
I am trying to import data from Excel as a data source using ADO.NET into a data set. All the tables show up except the fact that I am missing in some of the worksheets data - a whole column in one in particular. Any advice where should I look for my problem?
Thanks.
Appreciate your doses
I have an adp project and I am importing an excel file using acCmdImport. This works fine but I have the mixed type issue and I get some null values in my database. I have read the setting the IMEX value to ‘1′ in the connection string should sort me out, my question is which connection string? Thanks
Hi,
I have a specific problem with refreshing external data from FileMaker Pro 5.5 into Excel X for Mac OSX.
Under the ‘Data’ menu in Excel X, then ‘Get external data’ there is a specific option for ‘Import data from FileMaker Pro server’ which has been how we have imported data successfully for nearly 7 months. Then suddenly, yesterday, we could not get a new data range to refresh.
What is curious is that the data ranges set up 5-6 months ago still refresh by clicking ‘Refresh data’ but the ranges setup recently do not - though the data did import in the first instance when the range was established. However, now, the range cannot be refreshed.
I have scoured all forums looking for an answer to this. There have been no software changes whatsoever between the original ranges (which still refresh and work perfectly) and the new range that does not.
Has anyone got any ideas? How could a data refresh work on some ranges but not on others? As you can tell, I’m completely flummoxed!
Thanks for any help.
Hi
With out changing the regedit value TypeGuessRows=0 how can i read the excel file whic h contins intermixed data.
How can i read if my excel column contains both string and numeric values
Thanks for any help
This is really a wonderful site ! Thank you very much !
I am facing some problems as mentioned below:
I have am xls file which I am trying to open in VB as given below:
Dim rec As Recordset
Set db = OpenDatabase("c:\temp\1.xls", False, False, "Excel 8.0;IMEX=1")
Set rec = db.OpenRecordset("SELECT *,CSTR(Extension) from [rampi-q$] ORDER BY UserName")
In the xls, the field Extension has numeric values like 2283 or 5674 (but the cell doesn’t have any number formats).
When I try to get the value as given below:
MsgBox rec.Fields(”Extension”).Value
then I am getting the error:
‘Run-time error 3349′ Numeric field overflow.
Initially I have used the query as given below:
Set rec = db.OpenRecordset(”SELECT * from [rampi-q$] ORDER BY UserName”)
And i was getting the same error when I try to access the corresponding field as:
MsgBox rec.Fields(9).Value
In the xls file all other columns have text values like:
Pradeep,pt0243,+91 34565,+919323232,ac@dsds.com etc.
only this field has pure numeric value.
Hello Jamie, if you can provide me some help then it would be very helpful.
OMG!! I have dealing with this problem for soooo… long.
Thanks sooo… much!!
Greetings,
I figured out the Scientific Notation issue.
If you change your select to..
C#
string sqlStatement = “Select Format([myfieldname], \”#####\”) As [myfieldname] From [Sheet1$]“;
VB
Dim sqlStatement As String = “Select Format([myfieldname], “”#####”") As [myfieldname] From [Sheet1$]”
This will return your field as a string without the Scientific notation.
Greetings…again,
Someone asked “What if I have leading zeroes (leading 0)
Then use the following…
C#
string sqlStatement = “Select Format([MyFieldName], String(Len([myfieldname]), \”0\”)) As MyFieldName From [Sheet1$]”
VB
Dim sqlStatement As String = “Select Format([MyFieldName], String(Len([myfieldname]), “”0″”)) As MyFieldName From [Sheet1$]”
Enjoy!
John Hewitt, In both the above cases, why not just use:
Select Format([MyFieldName], ‘0′) As MyFieldName From [Sheet1$];
The String(Len(… variation certainly formats negative values in a strange way!
Thanks,
Jamie.
–
Hi,
I have been trying to get the string from a column which is already guessed as numeric column. I cannot change the regedit value TypeGuessRows=0 ‘coz i dont get the permission to access the registry. I have tried to update(append a character) the first record but it is throuwing a datatype missmatch exception. I have changed all the possible values of HLD , MAXSCANROWS and IMEX but my problem is not solved.
Please help me if anybody have the solution
Sometimes your data doesn’t have any values in a column that you need and if it was sorted at some point in it life the there is probably a lot of nulls / blank at the top of the list.
I used to like this method but I lost all faith in it when I had numerous problems with blank cells at the first 100 odd records. The system would default all sorts of things and I even found mixed columns. by that I mean I got data from completely different columns. I have been using SQL in SQL Server & Access going right back to 6.5 so it wasn’t just a simple error. I tried all sorts of casting as formats and unions with predefined rows that could later be masked out but none of them worked. This method is fine for non business critical analysis but I no longer trust it.
can anyone help me how to get text data from a column which already guessed as numeric column. As i have no access to the regedit Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ because i dont have permission access the registry.
Example: ExcelFile
ColumnName
111
222
333
444
555
666
777
888
999
100
200
xxx
yyy
zzz
As all numeric values are getting but those text values (xxx, yyy, zzz) are retrieved as dbNULL (’coz still the TypeGuessRows in the regedit is 8(default value))
As i am importing the excel from a web application i cannot change the value of regedit.
Jan: “I used to like this method but I lost all faith in it… This method is fine for non business critical analysis but I no longer trust it.”
I concur. If you want to use SQL then put the data into a SQL DBMS.
Jamie.
–
I have used some tricks to get mixed data from excel file:
In the Extended Properties use HDR=No;IMEX=1
Which will return recordset including the first record(HDR=No)
The columns will be named as F1, F2, F3….. by default
After Getting the DataTable (RecordSet) Replace the column names(F1,F2,F3,…..) with the First Row’s Values (Table.Rows[0][0], Table[0][1],Table[0][2],…..)
Delete the First Record from the returned Recordset(Table)
all mixed datatype columns will be retrieved as columns are guessed as Text Datatype(HDR=No;IMEX=1)
HDR=No Indicates the excel file contains Data Only (No Headers)
IMEX=1 indicates Columns contains mixed datatypes
Assumption the First Row in the Excel file contains Headers( Normally headers are written in Text Format)
Enjou…..
Now no need to change the regedit “TypeGuessRows”
Naorem Ranjan Singh: “I have used some tricks to get mixed data from excel file (snipped)… no need to change the regedit ‘TypeGuessRows’”
Ah, if it were only that simple! Assuming the column headings are text, using HDR=No will merely add an extra text value into the mix when majority type is being determined, so the cases where it will make a difference are very limited.
If you want to test this, create a column consisting of a text heading and 100 numeric values then query it using HDR=No. Unless TypeGuessRows=1 — a rare situation IMO — the 100 numeric values will always be picked as the majority type over the single text value of the heading.
Jamie.
–
Hi Jamie,
Even after the default value of TypeGuessRows (=
in the regedit,
if there is a string found within the first 8 rows, the datatype of the column will be guessed as a Text Column and thus all types (numeric, text, others) in the column will be retrieved as Text.
Normally when we importing from excel file we consider the first Record of the file as Header and Always header contains String Values and we make it constant. If the user intensionally put some numeric values in the header let them suffer.
Always there is some rule user has to follow for importing from excel, csv, tab delimited files.
He’s right it will guess that it is a Text field however there is much less that you can do with a text field, and it will also require filtering out each time. My problem was that I wanted numerics. Note that the maximum amount of data that you can retrieve from these queries is 255 characters. Somehow you are unable to retrieve memo field style information.
Also it is very useful to be able to use the column headers as often the shape of the data changes or is added to.
Naorem Ranjan Singh,
Apologies. Being as I am an administrator of my own machine (!!), I’ve changed the settings so many times I’d forgotten which value is default for ImportMixedTypes (someone should write an article about this stuff…) Mine’s set to Majority Type because I only deal with strongly-typed data
I hope your application checks that ImportMixedTypes=Text rather than ‘let them suffer’ if it is Majority Type.
Jamie.
–
As my application is web application i cant access the regedit. The value of ImportMixedTypes is “Majority Type” in the registry but when i read the value of ImportMixedTypes it returns “Text”.
Both GetValue and SetValue throws no exception but it does not get the actual registry key value or set the registry key value, it just returns the Default value for GetValue and in case of SetValue the previous Key value does not change.
So if the Administrator of the Server Changes the value of ImportMixedTypes to “Majority Type” then data type will be decided as per the majority.
Any luck, solution then it would be very great.
Hi, I have a utility which converts XML data to EXCEL.
My text column is truncating at 255.
I tried all the different settings in registry with different extended connection string properties.
Is there any other way to overcome this problem? As the column which facing this problem is a description column which is supposed to have maxlenght more than 255.
Please help.
Naorem Ranjan Singh:
My problem is even when i say header is NO, I get all the long values in scientific notation??? any reason. im using ado.net. I read some where on top some oen added empty string to end of cells to make it string. but i cannot tell users to add emty string to all values.
John Hewitt:
I figured out the Scientific Notation issue.
If you change your select to..
C#
string sqlStatement = “Select Format([myfieldname], \”#####\”) As [myfieldname] From [Sheet1$]“;
John, your idea looks good ,but the problem is iam not getting the actual value entered by the user. the value gets rounded off.
sreekumar: “your idea looks good ,but the problem is iam not getting the actual value entered by the user. the value gets rounded off.”
Try:
Select Format([myfieldname], ‘0′) As [myfieldname] From [Sheet1$]“;
Jamie.
–
Jamie: the same with this case also. it gets rounded off…
Sree
Oh dude… I have been looking for this for years, thank you… thank you. I am tired of loosing leading zeros in phone numbers…. not any more
Horray!!
What if I am using Linux and OpenOffice? Linux doesn’t have a registry, so where would the “TypeGuessRows” variable be held?
I have been trying to use the Microsoft.Jet.OLEDB.4.0 for retrieving data from excel spread sheet and display in a grid view control.
The connection string that i use is as follows:
“Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1; ImportMixedTypes=Text; MAXSCANROWS=0′; Data Source=” + strDataFilePath + “;”
I dont have any control on the data that will be there in the spread sheet and i dont know how many columns will be there in the spreadsheet. I found that the jet driver is retrieving null values when it came accross mixed type data. Later i overcame this problem by adding IMEX=1. Even then the result is not consistent. Sometimes it takes out leading zeroes. Since the application is a smart client I cant make any changes to the Jet registry settings to modify typeguessrows=0. Also, When i use IMEX=1 it convert numeric data into scientific format 70400672657-> 7.04007e+010 . I tried using Format(col, ‘#####’) . But the value gets rounded off.
Can anyone give a solution for getting the data in proper format instead of 7.04007e+010. Or how to use the Microsoft.Jet.OLEDB.4.0 to get consistent results.
Regards,
Siju Paul
Hi!
I have the same problem as the ones posted above.I have in the excel file a value like: 87987845 and in the SQL table the value is : 8.79878e+007.How do I solve this?
Thanks!
can anyone help me how to get text data from a column which already guessed as numeric column. As i have no access to the regedit Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ because i dont have permission access the registry.
Example: ExcelFile
ColumnName
111
222
333
444
555
666
777
888
999
100
200
xxx
yyy
zzz
if i am trying to retrieve above values then i am getting system.dbnull for xxx,yyy, and zzz.As well as getting the empty value instead og xxx,yyy and zzz.So Plese help me out.give me easy solution in csharp.
i am facing a problem when reading dat from an excel file using ado.net.I hv an excel file that contains just dta, means it doed not contain any column name.When i m trying to fetch dat from this excel file,all records are show correctly, but first row of data contains some strange value like f2,f7 e.t.c.plz help me to solve my problem
Hello everybody,
thanks for all the help in this great blog.
I still have the problem mentioned before here like:
989703202 in excel shows up as 9.89703e+008 in SQL
I hope someone can help me out… Thanks for all who spend some time on this.
Hi All,
I have a web application where in I import data from an excel sheet into an sql table. I am using a dataset as an intermediary. I have four columns where in I can have values greater than 255 characters, but the problem is in the first 8 rows the data is less than 255 characters. As a result of which the data of the other columns is also getting truncated as the value of typeguessrows is 8 by default(I suppose). I cannot change the registry settings.
is there any way where in I can solve this problem.
Thanks,
Soujanya
hi,
i do have the same problem addressed here i.e. problem while reading data from a column in excel having mixed data types. but i am using ODBC driver and the database is MS Access.
if it finds numeric value first then expects all column values to be numeric and throws exception when a text value is encountered, same is the case with text also.
changing registry values did not helped me. below is the code for getting connection to excel.i dont know where i can put AMEX=1. hope some one can help me out with this.
c = DriverManager.getConnection( “jdbc:odbc:recxls1″, “”, “” );
stmnt = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String query = “select * from [Joined$] where DU=’CRM’;”;
ResultSet rs = stmnt.executeQuery( query );
here ‘rcxls1′ is the datasource name and ‘Joined’ is the sheet name.
Cheers
Ashok
Hi All,
I get data from a source in the form of text files. These text files happens to be the feed for my stored porcedures.However I need to change the first two lines of the text files and then save them(again as text files).I am trying to do this in EXCEL, wherein I use the “Webquery” option and point to this text file. Make the necessary changes and then save the contents of first column as a text file. I am able to do this… but in the new text file i get some junk characters…. not sure what these are… Could you please help me on how can i make changes to the original text file and make a new text file.
Thanks a ton.
With regards,
NoviceeUser
I HAVE SOLVED THE SCIENTIFIC NOTATION ISSUE:
USE THE FOLLOWING IN THE FOLLOWING:
Format([COLUMN NAME], ‘@’) As [COLUMN NAME]
ENJOY…..
Hi All,
I have tried all the things to get upload MIX Type of Data
1. IMPORTMIXEDTYPE (Text/Majority)
2. TYPEGUESSROW (0/16)
3. CSTR
and many more, but i could not find the appropriate solution. I have a column in my sheet of Mixed type, Data somewhat looks like in the given format.
5A
4
4A
3
3A
Pleae help me to uplaod the data.
i must be missing something ??!!
After reading through all the threads/comments i am stil lhaving issues?
ive tried every seting i possibly can in connection string/extended properties but get either a Null or ‘blank’ ( “” )
im trying to retrieve all data in particular columns of named range (no headers). column (F1) contains mixed data types, numeric and text ie 123456 & 123456(dup1)
this is my test sub…
Sub rghaghareh()
Dim rs As New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strExcelConnectionString As String
Dim strFileName As String
strFileName = “[filename.xls]”
strExcelConnectionString = “” & _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“data source=” & strFileName & “;” & _
“Extended Properties=”"Excel 8.0;HDR=NO;IMEX=1;MAXSCANROWS=0;TypeGuessRows=1″”" ‘;ImportMixedTypes=Text”"”
‘”Extended Properties=”"Excel 8.0;HDR=NO;IMEX=1;”"” ‘;ImportMixedTypes=Text”"”
‘”Extended Properties=”"Excel 8.0;HDR=NO;IMEX=1;MAXSCANROWS=0;TypeGuessRows=1;ImportMixedTypes=Text”"”
cn.ConnectionString = strExcelConnectionString
‘cn.CursorLocation = adUseServer
‘cn.CursorLocation = adUseclient
cn.Open
rs.Open “select [F1],[F2],[F3],[F4],[F5] From AmendRefDB”, cn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
If (rs.Fields(0) > 57) And (rs.Fields(0)
part 2 of sub….
rs.Open “select [F1],[F2],[F3],[F4],[F5] From AmendRefDB”, cn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
If (rs.Fields(0) > 57) And (rs.Fields(0)
part 3 of question,
My ADO connection knowledge isnt great, getting most ideas from various groups on net?
although a ‘developer’ where i am employed, we dont have admin rights to pc. will this effect the properties in Extended properties, workign with registry ?
another thing.. rs.type always gives a 5, which i’m assumin is a numeric of some kind, where field 4 is 202, text ?? varchar??
help.. anyone, thanks
nicholas
hi Jamie,
I have the same problem discussed here.I am using ODBC driver and the database is Oracle.
I have changed the registry values.I dont know how to set IMEX=1. Can you please help me out with this.
My Java Code:-
conn = DriverManager.getConnection( “jdbc:odbc:ExcelFiles”, “”, “” );
stmnt = c.createStatement();
String query = “select * from [Divisions$]“;
ResultSet rs = stmnt.executeQuery( query );
Regards,
Githa.R.S
Thanks to Jamie for all the answers that were ready for me when I first found this site. You’ve been a huge help.
I have another question for you. In your post from 2 February 2005 you said:
“If for some reason you want to stick with ODBC, there is a second workaround. It is to change (e.g. manually in MSQuery) you SQL text to contain ODBCDirect connection strings, where IMEX=1 may be specified and noticed e.g.
SELECT MyMixedCol
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Tempo\db.xls;].[Sheet1$];
This tip works for me, but strangely it only works on one machine. When I take this file home or to another PC at work, I get a “General ODBC Error”. If I remove the ODBCDirect part ([Excel 8.0;... etc.]), then I get no error.
So my question is, what would make the SQL/VBA work on one PC but not another? The registry settings in HKLM\Software\Microsoft\Jet\4.0\Engines\Excel are identical on all PCs, as are the settings in the “ODBC” registry key under Jet\4.0, as is the version of the Jet file itself(Windows\System32\msjet40.dll), as are the references attached to the file (under Tools menu in the VB Editor), as is the version of Windows.
The only thing I see that is obviously not the same is the exact build number of Excel: on the working PC it is 11.8105.8107 (SP2), and on one of the non-working PCs it is 11.8146.8132 (SP2). It looks like the non-working PC has later Excel updates installed, and if that’s the problem, I don’t want to update the good PC. Is there a way just to get the specific files (jet-related?) so that ODBCDirect works on all PCs?
Thanks again for all your help before. I wouldn’t have come nearly this far without it.
Mike Kenny
Couple of notes about IMEX. We had an issue where leading 0’s were being dropped in columns with a numeric header. When IMEX is included in the connection string with value of 1, attempting to insert or update a cell causes oledb to throw a ‘not an updateable query’ exception. Without IMEX, the update crashes with an invalid type update attempt error if you try to insert or update text in a numeric field determined by the schema. Never did figure out how to update every cell with a ‘ to force a text type on numerics.
I did find a post by DaberElay that suggested adding TypeGuessRows=1 along with HDR=no in the connection string. Since we have a business rule that all headers must be text, this has worked. Oledb adds F1, F2, F3… as headers, and the user’s headers are used to determine the type, in our case with the business rule, text.
_connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + excelFilePath + “;Extended Properties=\”Excel 8.0;HDR=No;IMEX=1;TypeGuessRows=1\”";
So i use a reader to get the schema and see if there are any types not = system.string, and send error back, otherwise load dataset and rename columns with user defined names instead of f1, f2, f3.
What a hassle! I’m all for changing the registry to get something to work on my box, but when we’re talking about production code that’s pushed to multiple environments, test, qa, prod… it’s really not a feasable option.
Thanks, Naorem Ranjan Singh
Your comment fixed my issue with the SCIENTIFIC NOTATION problem.
Hello Could anybody please guide with SCIENTIFIC NOTATION problem solution .989703202 in excel shows up as 9.89703e+008 in SQL. Can anybody please guide me to dig out the soltuion for this in SQL SERVER …or if any solution there just let me know in which language it is ..
Thanks
Regarding the comment from Naorem Ranjan Singh, this won’t work if the column is formatted as “text”, it has to be “general”.
Here’s another example.
select *
from OPENROWSET(’Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;IMEX=1;DATABASE=c:\xfer\itemcosts.xls’,
‘Select Format(ItemCode,”@”),Price from [Sheet1$]‘)
If the itemcode, in my example, is 1314995 it would show up as 1.31499E+06 with a straight select or if the column is formatted as text, and 1314990 (loss of precision) if formatted with “0″ or “#” characters.
Hi Bill Faulk,
It is working perfect when the column is formatted as both “text” and “general”
Can u send me the section of whole code to connect to excel file.
Thanks.
Hi,
I am encountering the same problem described in the article (some of my data comes up missing). However I am using a .txt file as my data source. Here is my connection string:
dbConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & path & “;Extended Properties=”"Text;FMT=Delimited;HDR=No;IMEX=1″”;”
Is there a IMEX=1 version for text drivers? Can anyone suggest anything I can change to make my queries return accurate data? opening the .txt file in excel is not an option as it has too many records.
Thanks for any help
David >>Is there a IMEX=1 version for text drivers?
David, You can use a schema.ini file to specify explicit data types (or specify MaxScanRows if you happen to like Excel’s ‘Guess my data types’ design). See http://msdn.microsoft.com/en-us/library/ms709353.aspx
Jamie.
–
Hi,
I am trying force all columns to be Text datatype by specifying HDR=No;IMEX=1; in the connection string. (I always have a header row and I can’t modify the registry on users’ PCs). What’s strange is that the columns which contain numbers with 15 decimal places (Excel maximum), get rounded off to 11 decimal places. For example, 176263.673145496 would be rounded off to 176263.67315. Any idea why and is there a way to get all 15? (This doesn’t happen if I don’t force everything to Text).
Using the above approach, columns with mixed number and string types seem to import consistently as strings. However, if I have #N/A in a column, sometimes it gets imported as Null and sometimes as #N/A string. Any idea why?
Coincidently (or not), when it gets uploaded as #N/A, numbers is all other columns get imported as integers, even if they had many decimal places. Bizarre…
I have read through the entire thread but still seem to be seeking an answer.
I have an Excel Speadsheet that I am trying to query against using:
With adoCon
.Provider = “Microsoft.Jet.OLEDB.4.0″
.Properties(”Extended Properties”).Value = “Excel 8.0;HDR=Yes;IMEX=1″
The problem that I am having is that some of the data elements returned are larger thatn 255 and are therefore trimmed. I have no access to the RegEdit as this has been locked by our administrators. Does anyone have a suggestion as to how to return query elements/fields back in their full length (ie in excess of 255 chars) I have already tried setting IMEX = 0,1 and 2 but to no avail. I have also tried testing with all cells in the first 7 rows set to strings in excess of 260 Chars again to no avail.
Help would be appreciated,
Rob
Ottawa, Canada
Hi,
We are working on SSIS. We use Excel connection manager to read data from EXCEL and the use OLEDB destination to load it into DB. The problem is simple, due to mixed data types that is majority text and remaining numeric. The numeric values are getting ignored. We tried setting all the registry settings. But when we add IMEX=1 in the EXCEL connection string, the error coming as “Could not find installable ISAM”.
Please help out inorder to read the mixed data types from the EXCEL source. Thanks in advance
I’m trying to export rows from SQL Server to Excel. I created a template XLS file with ‘COL1′ in cell A1 and ‘COL2′ in cell B1. When I export data without the IMEX flag, the numbers are considered text and do not sort or behave as they should. However, when I try adding the IMEX flag, I get an error.
– THIS WORKS:
INSERT INTO OPENDATASOURCE (’Microsoft.Jet.OleDB.4.0′, ‘Data Source=”C:\Test_Sql2Xls.xls”; Extended Properties=”Excel 8.0;HDR=Yes”‘)…[Sheet1$] (COL1, COL2)
SELECT 1, ‘A’ UNION SELECT 2, ‘B’
– THIS DOES NOT:
INSERT INTO OPENDATASOURCE (’Microsoft.Jet.OleDB.4.0′, ‘Data Source=”C:\Test_Sql2Xls.xls”; Extended Properties=”Excel 8.0;HDR=Yes;IMEX=1″‘)…[Sheet1$] (COL1, COL2)
SELECT 1, ‘A’ UNION SELECT 2, ‘B’
– HERE IS THE ERROR:
OLE DB provider “Microsoft.Jet.OleDB.4.0″ for linked server “(null)” returned message “Cannot update. Database or object is read-only.”.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OleDB.4.0″ for linked server “(null)” reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Line 2
The OLE DB provider “Microsoft.Jet.OleDB.4.0″ for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OleDB.4.0]“.
I’d love anybody who can help. I know this is going to be the first complaint I get from the users.
The only solution i found to the scientific issue ( reading numbers as 7504521e+10 ) was to read the excel file with StringConnection properties Hdr=No;IMEX=1;
This way, the excel Header seems to be part of the data, and all rows are determined to be “string” so the numbers appear correctly. You can load a dataSet with this information and delete the first row so you can keep manipulating your data.
Hello,
Has anybody solved Dan’s problem ?
I’m experiencing the same problem with .CSV files
e.g.
This column in the CSV file
abc
def
123
456
789
is seen after SELECT as
-
-
123
456
789
In some columns imported the CSV I see blank values
instead of strings (the driver assumes the column is numeric, while it should leave the column to be as string)
>Hi,
>I am encountering the same problem described in the >article (some of my data comes up missing).
>However I am using a .txt file as my data source. Here >is my connection string:
>dbConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & path & “;Extended Properties=”"Text;FMT=Delimited;HDR=No;IMEX=1″”;”
>Is there a IMEX=1 version for text drivers? Can anyone
>suggest anything I can change to make my queries return
>accurate data? opening the .txt file in excel is not an
>option as it has too many records.
Thanks i am very much impressed of this documents.
Thanks to Daily Dose of Excel
Hello:
I am trying to run a software that uses Microsoft access as a database or
MSQL Server, but neither one works on me, probably the Microsoft
Jet engine is not working. I tried to re-install the jet engine and
also the DOTNET 3.5, but i am not able to do so.
The following is the error message from the software, when
running access to create the database and start the main
menu. I am using Windows Vista Ultima.
Initialization Errors:
08/09/29 12:47:15 (-2147221164) CoCreateInstance for ADOConnection failed (line:993 file:’.\DataBase.cpp’)
08/09/29 12:47:15 (0) (line:1040 file:’.\DataBase.cpp’)
08/09/29 12:47:15 (-2147467259) ADO Connect string:’Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Users\roberto\Documents\6\670.mdb’ADO User:”ADO Password:” (line:1054 file:’.\DataBase.cpp’)
08/09/29 12:47:15 (-2147467259) GetFileAttributes on ‘C:\Users\roberto\Documents\6\670.mdb’ worked (line:1062 file:’.\DataBase.cpp’)
08/09/29 12:47:15 (-1) Tried 0 times (line:1066 file:’.\DataBase.cpp’)
Is there any GURU with access and ODBC experience that can help me? Please
reply to roberto@rcervantes.com
Thanks and Regards,
Initialization Errors: CoCreateInstance for ADOConnection
odbc/ole db connection for ms access
pooling microsoft data access components problems
Simply amazing! Thank you and OneDayWhen so much for doing the research on this and posting the results. After a couple of days of scratching my head in bewilderment, I stumbled across your post. “IMEX=1″ worked like a charm.
Hello,
IIF(ISNUMERIC(field),CSTR(field),field) as field
Well, I found the IMEX=1 a solution to one of my problems, but now I stuck with a new one. I need to import all of the rows as text, even “numeric” ones, since those numeric ones are for example MAC Addresses and/or serial numbers — numbers that should be treated as text. This works fine when loading the values into my application. However when I try to update the spreadsheet, I get the “non-updatable query” error. I read elsewhere that this is because IMEX=1 sets the connection to a read-only, import mode. I can remove the IMEX=1 from the connection string and the data will be updated correctly. However in that case, all of the text values in Mixed Typed rows will be sent to the application as NULL.
Some comments mention that they use HDR=No and TypeGuessRows=1 to use the headers to set the type to text; I may try this although being that this is a customer’s spreadsheet, I can’t be certain that the columns will match up with the correct headings (esp. since they have already sent us a few different versions of the spreadsheet).
Is there something else that I could try? Is there a way to explicitly state that the connection is for both import and export so that I can use IMEX=1 and still be able to run an Update query?
One other gimmick I may try is to create two DataAdapters with two different connection strings: one would include the IMEX=1 and be used to read in the data with a SELECT command and the other without the IMEX property to be used to UPDATE new data back to the spreadsheet. Unfortunately, in order to get a DataAdapter to register an UPDATE command, I’d need to create a corresponding SELECT query for the adapter and connect it to the application’s datasource / data table. Because that SELECT query wouldn’t give the correct data, I’d have to use a different gimmick: have the SELECT query return zero rows (eg “WHERE 1=0″) so it would still connect to the spreadsheet and the internal table, but just wouldn’t fill in any data.
@Pyth007: Your ‘gimmicks’ sound like potential solutions to me
Let us know how you get on.
Iam able to retreive mixedvalues.
Step 1:
I was able to change the reg value of
regedit Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ to “0″ , beign an administrator.
Step 2:
Iam using the following java snippet to retrieve the values in the resultset:
*********
String connStr = “jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=”+ fileLocation + fileName;
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(connStr);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(”SELECT * FROM [Excel 8.0;HDR=YES;IMEX=1;Database="
+ fileLocation
+ fileName
+ "].["
+ sheetName
+ "$]“);
*************
Step 3:
Retreive ..just as u do the RDBMS:
String productNumber = rs.getString(”Product Number”);
…etc
Hope it will be helpful to someone who is struggling with this.:)
cheers,
Raghu
i understand the above article treats of excel files being used as database for excel files (i can’t get the point very well with doing that, since you can still use excel files as databases by just referring to them in your formulas, but i’m not here to discuss that).
i have a similar problem when accessing informix (or postgresql) databases.
in excel i have used data / import external data / import data -> created a new query with ms query (using a system dsn pointing to my informix database). ms query will show correctly all the fields of all my records, while excel truncates the first field to 7 characters and the second to 25. first field is char(20) in the original database and second is char(50).
any suggestion?
thanks in advance.
Just wanted to thank everyone on this thread. It’s really helped alot in my work with excel. Awesome insight and thanks for the repost don’t let this thread die. This information doesn’t readily exist on the web and if it does it’s very cryptic and limited. I’ve searched high and low. I wonder why Microsoft wouldn’t like to go deeper into the inner workings of this topic, using excel as a datasource.
MY QUESTION:
Has anyone generated a excel spreadsheet or workbook using roundtrip HTML and has tried to access the same workbook through ole? Hell occurs on my computer not sure of anyone elses. Granted the format when you generate the workbook through excel is XML spreadsheet 2003 format. Is the DataSource specific to a particular format and is there any way to access the XML Spreadsheet 2003 format through OleDB connection???? Thanks for help in advance.
Hey all
Thanks a lot to Jamie for all his responses! Incredible that you support all those question over all these years!
I ran into the issue where I had to import a csv in .NET using oledb and it removed leading 0’s in a column that (mostly) contains numerical values. I needed the zeroes, though..
I could not change format of the files (client’s files) and I certainly could not change the registry values on their servers.
So your solutions were not really feasible for me.
I found a solution by creating a schema.ini for my csv file like suggested in this thread:
“How can read CSV File fields as text using OleDB Provider”:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/dda4017a-c1a7-4ee3-90dd-5bdfd31f007a
Hope this helps somebody else out there. Thanks and good luck.
Ben