Better way to open an Excel file with FireDAC

I used to import excel files to a table (MS-SQL, Oracle) using a ODBC 
Driver and ADO components. Now I'm using FireDAC with this params in the 
FDConnection:
Database=C:\Projects\CostCtrl\Documents\Labor.xls
ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
DataSource=Excel Files
DriverID=ODBC
and using this query:
SELECT * FROM [Sheet1$]
In the initial tests this works fine for xls files, but I'm asking the 
experts if there is a better way to do this.
This is not working for xlsx files.
Best Regards
Francisco Alvarado
0
Francisco
3/10/2014 5:12:33 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

7 Replies
3437 Views

Similar Articles

[PageSpeed] 14

Hello Francisco

> Database=C:\Projects\CostCtrl\Documents\Labor.xls
> ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
> DataSource=Excel Files
> DriverID=ODBC

Actually ODBCDriver and DataSource are mutually exclusive. In this
case you does not need DataSource.

> This is not working for xlsx files.

Try to remove DataSource parameter.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
3/11/2014 9:29:23 AM
On 3/11/2014 3:29 AM, Dmitry Arefiev wrote:
> Hello Francisco
>
>> Database=C:\Projects\CostCtrl\Documents\Labor.xls
>> ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
>> DataSource=Excel Files
>> DriverID=ODBC
>
> Actually ODBCDriver and DataSource are mutually exclusive. In this
> case you does not need DataSource.
>
>> This is not working for xlsx files.
>
> Try to remove DataSource parameter.
>
Thank you Dimitry!
0
Francisco
3/11/2014 3:42:33 PM
Hi Dmitry

I  have a problem when reading excel file using FireDAC with ODBCDriver.

It always treat the first line in the excel file as heading (i.e. field name). In reality, the first line could be simply just valid data rather than heading.

How can I read the excel file from the first line?

Best regards

Yonghui
0
Yonghui
6/11/2014 4:18:14 PM
You need to create named ranges in your excel spreadsheet. Each named 
range will be treated as a different table.

Yonghui Song wrote:
> Hi Dmitry
>
> I  have a problem when reading excel file using FireDAC with ODBCDriver.
>
> It always treat the first line in the excel file as heading (i.e. field name). In reality, the first line could be simply just valid data rather than heading.
>
> How can I read the excel file from the first line?
>
> Best regards
>
> Yonghui
>
0
quinn
6/11/2014 5:30:06 PM
> {quote:title=quinn wildman wrote:}{quote}
> You need to create named ranges in your excel spreadsheet. Each named 
> range will be treated as a different table.
> 
> Yonghui Song wrote:


Hi Quinn,

Thank you for reply. 

When you say 'Name Ranges', do you mean Worksheets

I do have my data in different worksheets and each worksheets seems a table in FireDAC.

My problem is that the first row in in each worksheet always read as filed names. so basically I lost first row data in each of the worksheet.
0
Yonghui
6/11/2014 6:49:24 PM
<Yonghui Song> wrote
[...]
> When you say 'Name Ranges', do you mean Worksheets
[...]

A Named Range is a defined subset of cells in a Worksheet.
What you want to do is something like this:
 - connect to the worksheet
 - programmatically create a Named Range for the whole
     worksheet (ie: the the desired cell range)
 - open the Named Range as your table
Google will help you with this.

John


=================
<Yonghui Song> wrote
>> {quote:title=quinn wildman wrote:}{quote}
>> You need to create named ranges in your excel spreadsheet. Each named
>> range will be treated as a different table.
>>
>> Yonghui Song wrote:
>
>
> Hi Quinn,
>
> Thank you for reply.
>
> When you say 'Name Ranges', do you mean Worksheets
>
> I do have my data in different worksheets and each worksheets seems a 
> table in FireDAC.
>
> My problem is that the first row in in each worksheet always read as filed 
> names. so basically I lost first row data in each of the worksheet.
0
John
6/12/2014 3:45:33 PM
> {quote:title=John Gray wrote:}{quote}
> <Yonghui Song> wrote
> [...]
> > When you say 'Name Ranges', do you mean Worksheets
> [...]
> 
> A Named Range is a defined subset of cells in a Worksheet.
> What you want to do is something like this:
>  - connect to the worksheet
>  - programmatically create a Named Range for the whole
>      worksheet (ie: the the desired cell range)
>  - open the Named Range as your table
> Google will help you with this.
> 
> John
> 
> 
> =================

> > My problem is that the first row in in each worksheet always read as filed 
> > names. so basically I lost first row data in each of the worksheet.


Hi John, 

Thanks for explaining what is name range to me. I although have a lot relational DB (SQL) experience, but actually know nothing about Excel, except knowing it very powerful to create and work on tabular data set. 

I did a bit Googling, my understand is to use name range is like to use a pair of coordinates to locate whatever cell you want! is that right?

If my understanding is right, I can implement it that way, but just felt it need quite some extra code that seems not necessary, if the FireDAC can simply treat the first row in an Excel worksheet as data rather than field name!

Thank you.
0
Yonghui
6/12/2014 6:07:46 PM
Reply:

Similar Artilces:

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

Got error after open excel file from server..During Export Excel File
this error appears if you have tried to save a workbook on a drive and you do not have Modify and Delete permissions to the location where the file is being saved. It is also possible that another user has a read lock on the file.Excel saves a file using three steps (if the file already exists in the target location):Creates a temporary file with a randomly generated name, such as 4CCA4100.Deletes the original file.Renames the temporary file to the original file name.If Excel cannot delete the original file or cannot rename the temporary file, this error message appears.To temporarily work a...

XML to Excel files too large
Using the below code sample I am able to allow my ASP.Net users to download reports to Excel. Response.ContentType = "application/vnd.ms-excel" One problem I have discovered is that the native XML format results in a large file when compared to a true XLS document. For example, we have one report that results in an 8 mb file when saved to disk. But if you open the file and do a "save-as" and change the file type from XML to XLS the file is compressed to 1 mb. For remote users the delay in downloading an 8 mb file is long. This problem only gets worse when we started looking at Reporting Services. The reports are generated very quickly but the export to Excel option also saves the file as an XML document. This results in the same problem of a file that is much larger than it needs to be and creates a situation where export reports is a long tedious process. Does anyone know of a way around this large XML file size problem? My first reaction is to have the users save as CSV or TXT format. Many managers do not like this solution since it requires constant reformatting. My second idea was to save in a "true" XLS format from the server (either web server or Report Services server) but I think that would mean installing Excel on the server. I could be wrong but I think that is the only way to save a file as XLS. I'm stumped for a better solution. Saving reports in XML format is great for being able to send it anywhere and ...

Creating Database Files using Firedac (Delphi XE7)
I have been able to get Firedac to display and edit the following file types in a simple app using FDConnection, FDTable, FDPhysSQLiteDriverLink (and others according to file type) : sdb, gdb, mdb and xls (with ODBC link and drivers). I have been able to create a SQLLite File (.sdb) from scratch and add tables using roughly: FDTable1.Connection = FDConnection1; (set in form) FDConnection1.Params.Clear; FDConnection1.Params.Add('DriverID=SQLite'); FDConnection1.Params.Add('Database='+DATABASE_FILE); FDConnection1.ResourceOptions.DefaultParamType := ptOutput; ...

Newbie Question-How do I open excel file with Delphi 2007
I'm completely new to Delphi Programming, I got a little experience with VB but this is completely different. All I want to do is open a Excel 2003 document. Edited by: Terrance MacDonald on Jan 28, 2009 7:48 AM Terrance MacDonald wrote: > I'm completely new to Delphi Programming, I got a little experience > with VB but this is completely different. All I want to do is open a > Excel 2003 document. > > Edited by: Terrance MacDonald on Jan 28, 2009 7:48 AM You can find a lot of information on OLE automation with Delphi on this site: http://www.djpate.fr...

Missing library file when opening Excel in Vista64 with Delphi? [Edit]
Hello everyone, This is my first post here, I hope I have it in the correct place. I have just moved across to a new machine running vista64. Delphi 6 Pro installed fine, and runs fine. I tried to run (from within delphi) an app I wrote on a WinXP system, which is very simple, it opens an Excel 97 Spreadsheet and puts some numbers in it. When I click on the button to actually open the spreadsheet I get this error: "First chance exception at $76EEF328. Exception class EOleException with message 'One of your object libraries (stdole32.tlb) is missing or damaged. Plea...

Ways to prove Delphi is better than Delphi?!?!?!?
The subject sounds interesting isn't it? I read an article on PHP with similar title. Reading the title sparked my interest! I thought something like this should also exist for Delphi. Here is the original PHP article link: http://tinyurl.com/blbky43 Hope someone will compile such a list of existing Delphi code. I like the "Evolving the language without Waiting for New Versions" point... I have played with some ideas to do that in Delphi. 1- The latest one was "array of vars" (like array of const but to modify the values) I posted the source code...

MS Excel 2010, Delphi XE, Opening a tab delimited file
Hi, I am trying to open a tab delimited file with Excel 2010 using Delphi XE Starter Edition. I can open the file successfully using the Excel GUI, but when I try to automate the process with Delphi, it fails. The file opens successfully, but each line of the tab delimited text file is placed into the 1st cell of each row --the workbook only has 1 column. When I use the Excel GUI on the same file, each tab character creates a new cell to the right so I get multiple columns per row. What am I doing wrong? I also tried using Workbooks.OpenText to no avail. var xlApp : _Applicatio...

[FireDAC][Phys][SQLite]ERROR:unable to open database file. [Edit]
hi, I encountered a problem。 I basically created a page according to the APP.( [page address|http://docwiki.embarcadero.com/RADStudio/XE5/en/Mobile_Tutorial:_Using_FireDAC_in_Mobile_Applications_(iOS_and_Android)] ) After the deployment is complete compilation。 Running on the phone when the error message [FireDAC][Phys][SQLite]ERROR:unable to open database file. thank you Source Code: implementation {$R *.fmx} procedure TForm1.Button1Click(Sender: TObject); begin if edit1.Text='' then begin label1.Text := 'not null'; edit1.SetFocus; ...

FireDAC Query on Excel file: 'Syntax error in FROM clause'
Hi guys, Is this a bug or I have missed something? I used FireDAC TFDConnection, TFDQuery, TDataSource and TDBGrid to create a small interface to try out reading an excel file. The param is set as such: Database=C:\examples\xxxx.xls ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} DriverID=ODBC The query string in the FDQuery is simply: SELECT * FROM `Client$`, while 'Client' is a worksheet in my excel file. Now, I found a stranger problem: only if I set the *TFDConnection Connected=true* and the *TFDQuery Active=true* *at the design stage*, the quer...

Lost first row data reading Excel file using FireDAC [Edit]
Hi experts, please help I used FireDAC connection, FDQuery, DataSource and DBGrid to create a small interface to try out reading an excel file. Database=C:\examples\xxxx.xls ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} DriverID=ODBC The problem is the first row in each worksheet in that excel file is always read as column names (field names). but in my excel example, a lot of the first rows are just data rather than headings. How do I indicate the FireDAC Connection to read the first row as data, rather than field names? Thank you. > The problem is the first row in each worksheet in that excel file is always read as column names (field names). but in my excel example, a lot of the first rows are just data rather than headings. > How do I indicate the FireDAC Connection to read the first row as data, rather than field names? Try to add connection parameter: {code}ODBCAdvanced=HRD=No{code} -- With best regards, Dmitry Arefiev / FireDAC Architect ...

FireDac
Hello, I am using Delphi XE4 and facing a problem with FireDac (8.0.5) and MySQL. I'm trying to get the last 100 records from a large table (20 million rows). When I run the following SQL: {code}select * from largetable order by FId limit 100{code} in MySQL Workbench it returns in 0.2 seconds. When I run the above SQL in Delphi through an ADQuery it never returns (Delphi freezes). Any Ideas ? Thank you ...

open an Excel file with a particular Sheet opening
How to open an Excel file with a particular Sheet opening always. Thanks for any replies. "Trish": > How to open an Excel file with a particular Sheet opening always. http://www.personal-computer-tutor.com/excel_vba.htm ...

Error while opening an excel file : file could not be found
Hi All,   I am using the below code to export data from datagrid to excel.     dtgDailyReports.AllowSorting = false;                         DataSet ds = new DataSet();                         ds = (DataSet)ViewState["DailyReport"];                  ...

superreview granted: [Bug 363654] .url files opened via File:Open don't open/don't open properly on the 1.8.0 branch : [Attachment 248458] disables .url in file:open
Stuart Morgan <stuart.morgan@alumni.case.edu> has granted Smokey Ardisson (unreliable; no bugmail) <alqahira@ardisson.org>'s request for superreview: Bug 363654: .url files opened via File:Open don't open/don't open properly on the 1.8.0 branch https://bugzilla.mozilla.org/show_bug.cgi?id=363654 Attachment 248458: disables .url in file:open https://bugzilla.mozilla.org/attachment.cgi?id=248458&action=edit ------- Additional Comments from Stuart Morgan <stuart.morgan@alumni.case.edu> Well, that kinda sucks. ...

superreview requested: [Bug 363654] .url files opened via File:Open don't open/don't open properly on the 1.8.0 branch : [Attachment 248458] disables .url in file:open
Smokey Ardisson (unreliable; no bugmail) <alqahira@ardisson.org> has asked Stuart Morgan <stuart.morgan@alumni.case.edu> for superreview: Bug 363654: .url files opened via File:Open don't open/don't open properly on the 1.8.0 branch https://bugzilla.mozilla.org/show_bug.cgi?id=363654 Attachment 248458: disables .url in file:open https://bugzilla.mozilla.org/attachment.cgi?id=248458&action=edit ------- Additional Comments from Smokey Ardisson (unreliable; no bugmail) <alqahira@ardisson.org> Asking for sr on this 7-char patch in case we need to take it ...

Excel Export is opening in .aspx file instead of .xls file
I am facing one problem. I am using Visual Studio .NET 2003. I made a project to export datagrid into excel file. I wrote the code and when I excute it it opens in .aspx file and name of the file is same as the file in which I wrote this code. This happens when I run the application from my machine and code is deployed on different server. Please help. Code which I am using is Response.ClearContent()Response.Buffer = TrueResponse.ClearHeaders()Response.Charset = ""Response.ContentType = "application/vnd.ms-excel"lstrReportNameXls = "Reports.xls"Me.EnableViewState = FalseResponse.AddHeader("content-disposition", "attachment; filename=" + lstrReportNameXls + "") Dim oStringWriter As System.IO.StringWriterDim oHtmlTextWriter As System.Web.UI.HtmlTextWriteroStringWriter = New System.IO.StringWriteroHtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)grdReport.RenderControl(oHtmlTextWriter)Response.Write(oStringWriter.ToString)Response.End()    ...

HyperLinkField links to Excel files always open in Excel as Read-Only.
I have an ASP.net site on an IIS 6 server. On one page, I have a GridView containing a HyperLinkField. This field contains links that point to Excel files on the server, albeit on another drive. The links open properly in Excel when clicked, however the files aways open as Read-Only. I want these files to be writable after they open. The permissions on the directory containing the Excel files is set for full access to Everyone, including the IIS and ASPNET users. Also, when these files are browsed over the network (not via web broswer), they open properly (writable). Why are these files...

How to Dynamically open excel through IE without creating an excel file
Hi, Is there a way to open excel within Internet Explorer to show some data from database (Oracle),through excel without actually creating an excel file on the server in ASP.net,dynamically? Thanks, Girish. you can send html down to the client and then set the contenttype header to the excel. it needs to be atleast excel97 or higher to be able to interpret the html. Response.ContentType = "application/vnd.ms-excel" Response.AppendHeader("Content-Disposition", "filename=blah.xls;") Mike Banavige~~~~~~~~~~~~Need a site code sample in a differe...

Error opening Excel file: "Could not decrypt file"
When trying to open an Excel file programmatically, I get the above error. But this only happens on one specific file. It opens correctly when using Excel. The file is definitely not encrypted. On another file, I get this error: 'filename.xls' cannot be accessed. The file may be corrupted, located on a server that is not responding or read-only. After I cancel the operation, the debugger displays: The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data. The file is not read-only and The file is definitely not open. The server responds with other files, and both of these errors are specific to the file. These are files I have downloaded both programmatically and by hand. The errors remain. I notice that when I view the properties for this last file, it displays only the 'General' tab. Any suggestions on how I can fix these files? Thnx Will Try to save the file with a new name and run your code on the newly created file, seems that the file you have is locked by another application or process.   That sounds reasonable. The files are public infomation and if I downloaded it while someone else had it open reading it, it could indeed have these properties set. After downloading it several times, I got a copy that worked. Normally, I will be downloading the files when no one is on-line - late at night, weeken...

Problems saving excel files and opening word files with cifs
I have a NW6SP4 cluster with cifs. Users can access shares but they are not able to open word files nor saving excel files (word and excel simply stuck!). It doesn't ahppens with other kind of files (text only, power point, acess databases). I guess it has something to do with the way excel and world work with files (using temporary files). I tried to apply the latest cifs patches available for nw6 (cifs.nlm 1.60 oct 5 2004), but it didn't solve the problem. I'm using word and excel 2000. Can someone help me ? Thanks in davance. You're having this problem when...

Reading in Excel file, Excel still open in Task Manager
Here's my code: using Microsoft.Office.Interop.Excel; /// string filename = @"C:\example3.xlsx"; object updateLinks = 0; object readOnly = true; object format = 5; object password = ""; object writeResPassword = ""; object ignoreReadOnlyRecommended = true; object origin = XlPlatform.xlWindows; object delimiter = ""; object editable = false; object notify = false; object converter = 0; object addToMRU = false; o...

Which is better, opening and closing, or a massive file opening and a massive closing?
I'm still working on the script, though it is cleaning up even better, I even have it running cleaner by dumping anything not matching some specs=2E= =20 What I am trying to figure out though is this: I have 42 places for the output to go, 1 of those is a constant dump, the others are all based on whether or not there is data in a field=2E If the= data is in the field, the data writes to a file with the same name as the data checked=2E If not then it writes to a global catch-all=2E <!-- snip --> open OUTFILE, ">/home/multifax/everyone" or die "C...

Excel experts : Filling an complex Excel file and parsing a complex Excel file
I am stuck with below mentioned problem The requirement is that, we have Excel file template(.xls) which has Macros and most of the Worksheets are either hidden OR locked. When the end user clicks on a button to download it, follows modifications has to be done to the Excel sheet. Unlock the Excel worksheet, fill some data at certain cells and then lock it back Write some data into a hidden worksheet There are two options as far as I know Excel Interop/MICROSOFT Excel 11.0 Object Library  OLEDB Driver I cannot go with option 1 becaus...

Web resources about - Better way to open an Excel file with FireDAC - embarcadero.delphi.firedac

DSP Engineer - Jobs - CFO World - Finance & Technology
CFOworld.com.au covers accounting, risk management, taxation, technology and financial news from ASX-listed companies.

Embarcadero Launches RAD Studio XE7
CSO Australia - News, Industry Blogs, Tools and Resources for Data Security Executives

Embarcadero RAD Studio XE7
Extends Windows to mobile devices, gadgets, and wearables

Embarcadero Webinars
Advanced software tools for application developers and database professionals. Cross-platform solutions for database design, development and ...

Embarcadero RAD Studio XE7 (Delphi, C++Builder): is seven the magic number?
Embarcadero has released version 7 of its XE programming suite. The main products included are Delphi and C++ Builder, RAD development tools ...

Embarcadero RAD Studio XE7 - Dr Dobb's
Extends Windows to mobile devices, gadgets, and wearables

Resources last updated: 3/20/2016 4:46:51 AM