Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Read Excel file from Firedac gives error


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Dec 20, 2017 4:22 AM Last Post By: Mikael Lenfors
Mikael Lenfors

Posts: 99
Registered: 3/6/01
Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 15, 2017 8:56 AM
Hello!

I managed to read an Excelfile from Firedac, but I have an issue.

If i do a select as "Select * From [A1:GG10]"
I get error "'[FireDAC][Phys][ODBC][Microsoft][Drivrutin för ODBC Excel] Spill i numeriskt fält.'"
Translated something like "Overflow in numeric field".

If i do a select as "Select Top 1499 * From [A1:GG10]" it works fine
If i do a select as "Select Top 1500 * From [A1:GG10]" it get the same error

The problem is line 1499 does not differ from line 1500 (or any other line nearby).

How can I read the Excel data whatever there is an error in the data or not!?

Best regards, Mikael
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 19, 2017 7:33 AM   in response to: Mikael Lenfors in response to: Mikael Lenfors
If you will post your Excel file to attachments forum or send the file to darefiev at gmail dot com, then I will try to figure out. Also, what is your RAD Studio version ?

--
With best regards,
Dmitry
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 19, 2017 9:34 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
It seems the problem is in columns from "Product Vægt (kg)" to "Masterkasse Brutto (kg)". ODBC recongnizes them as numeric types, but at some cells there is "N/A" text, at some Excel shows warning icon "Number saved as text". Options:
* use explicit column names in SELECT and exclude above columns from the list;
* delete these columns from Excel file;
* convert all values in these columns to text;
* you can try the following, but I tried and it does not help to me:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce095b10-84a4-4ae3-8944-70a2b53daa44/mixed-data-types-in-excel-column-to-oedb-destination?forum=sqlintegrationservices

--
With best regards,
Dmitry
Mikael Lenfors

Posts: 99
Registered: 3/6/01
Re: Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 20, 2017 2:27 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Ok, thanks a lot but this is not a solution for me. This was just an example file, I don't know what files the user is going to import! I need a way to ignore any errors when reading. Maybe an event triggered when parsing an error field!?

Best regards, Mikael
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 20, 2017 3:19 AM   in response to: Mikael Lenfors in response to: Mikael Lenfors
Try to use the link.

Also, you can try to ignore failing records, for that set FDQuery.FetchOptions.RowsetSize to 1, then do:
FDQuery1.Open;
while not FDQuery1.Eof do
begin
  try
    FDQuery1.Next;
  except
  end;
end;
FDQuery1.First;


--
With best regards,
Dmitry
Mikael Lenfors

Posts: 99
Registered: 3/6/01
Re: Read Excel file from Firedac gives error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 20, 2017 4:22 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Ok, thanks Dmitry. I will try that.

Best regards, Mikael
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02