Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Accessing Excel spread sheet with FireDAC and forcing text only data


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


Permlink Replies: 0
Mike Collins

Posts: 46
Registered: 9/23/05
Accessing Excel spread sheet with FireDAC and forcing text only data  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 3, 2017 1:58 PM
Hi all,

First off - just want to congratulate everyone involved in the FireDAC development - what an amazing, cross-platform, flexible framework. Well done.

So, I have a 32 bit Windows application built in C++Builder / RAD Studio 10 Seattle (FireDAC = 13.0.1 (Build 82709) ) that includes an import wizard that allows the user to import data from an Excel spreadsheet.

I've use the TFDConnection, TFDMetaInfoQuery and TFDQuery connect to the spreadsheet using the ODBC Driver: {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} and all works well.

However, I noticed the other day that when I used the wizard, with a particular spreadsheet, only a fraction of the records were read in / retrieved. The spreadsheet format looked the same as all others I had successfully use but the system (not blaming FireDAC) was seeing certain records as being NULL.

Long story short, if I modified the spread sheet so all cells were set as TEXT (as opposed to the default GENERAL), everything work fine.

After some searching, I stumbled up this link: http://www.leansoftware.net/Help/Excel-Database-Tasks/Reference/Connection-strings/Excel-Connection-Strings.aspx

- which suggests that by HDR=YES;IMEX=1; additional connection parameters, you can force all data to be treated as Text. This is exactly what I want.

So my question is - a) Can I use these parameters with the Microsoft Excel Driver driver and b) how do I go about specifying them with the TFDConnection?

I have tried adding them to the ODBCAdvanced parameter like this:

fdbImportExcel->Params->Values["ODBCAdvanced"] = "HDR=YES;IMEX=1;ReadOnly=0;"  ;


or

fdbImportExcel->Params->Values["ODBCAdvanced"] = "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";ReadOnly=0;" ;

but neither works.

Any help or advice would be gratefully received.

Mike

Edited by: Mike Collins on Oct 3, 2017 1:59 PM

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02