Solved

adoquery Datatype decision based on 51% of the records...

Posted on 2004-08-16
4
312 Views
Last Modified: 2010-04-05
I really hope somebody can help me with the
following ;) :

I have a program reading the records from an excel file using the connection
like :

strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
              'Data Source=' + Form1.ED_filename.Text + ';' +
              'Extended Properties="Excel 8.0;HDR=YES;MaxScanRows=0;MaxBufferSize=2048";';

and getting the fields type with:

var loopint:integer;
begin
  Adoquery1.Open;
  for loopint:=0 to adoquery1.FieldCount-1 do
  begin
    showmessage(adoquery1.FieldList[loopint].DataType);
  end;
end;

The Problem I have is that in a column called 'PurchaseOrder' I have 80% of the values that are numeric (like '0008332')
and 20% that are strings (like 'M002233')

When I use datatype, it returns FLOAT. I've tested it using the default 16 rows and inserting records like 9 strings and 7 values
(it returns STRING) and 9 values and 7 strings (it return FLOAT).

The problem is that if I have a field that has both Values and Text... it should return text, not values.

Thank you in advance!

Dave.
0
Comment
Question by:dcrudo
  • 2
4 Comments
 

Assisted Solution

by:BaTy_GiRl
BaTy_GiRl earned 150 total points
ID: 11810184

>The problem is that if I have a field that has both Values and Text... it should return text, not values

Why you dont manipulate the rows which has strings, cutting the strings, maybe reviewing letter by letter...
use MidStr to cut the strings, and the function IsNumeric to review letter by letter    





0
 
LVL 1

Author Comment

by:dcrudo
ID: 11810403

Because the Program I'm trying to write should be able to automatically transfer Whole Excel worksheets data, not
always the same format, directly into a MySQL database.

I will not be using always the same format (the one above is just one example to replicate the issue).

If possible, I would like to avoid checking record by record... because some worksheets are quite big
(20 columns by 50K rows).



0
 
LVL 1

Accepted Solution

by:
aruana earned 150 total points
ID: 11842610
it may not be a good idea to use FieldList[].datatype to check for data in Excel.
What you are getting may be based on the first row only.
Also, what if a user enters numberic value in text format ? In excel, the appearance is the same as numeric value format.
i dont think you can avoid reading all of them.
50K rows for excel is certainly not a good idea for storing data.
is there any reason why you cant transfer all of them into MySQL as strings and later process them and change the data type if neccessary? this would be faster.
0
 
LVL 1

Author Comment

by:dcrudo
ID: 11879326
The answer to my problem:

Add 'IMEX=1' in the connection string will force JET to read all fields to return a Mixed field type and not
on the higher ration (51% number = number, 51% string = string)... if the field contain mixed number or
strings...it will always return string (mixed type).

Since no one of the answers helped me a lot... i will split the points for the effort!

Thank you!

Dave.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TVirtualStringTree  search using TEdit 7 121
Delphi Yen format 3 35
Unique identifier on a terminal server (rdp) 4 54
Delphi: making a BW image transparent 10 38
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question