Excel External Data Query

From Fxp Wiki

External Data is great to import data into a worksheet. Nevertheless a few options must be triggered to overcome some minor bugs or difficulties.

Contents 1 Cell text not appearing : Mixed column type problem 1.1 Solution 2 Read Only 2.1 Solution 3 Other options

Cell text not appearing : Mixed column type problem

When importing, only one type is guessed: if there is date and text or numbers, only data from the major type are taken into account, the other receive the "null" empty.

Solution

insert the IMEX=1 option inside the query

Extended Properties="HDR=NO;"

becomes

Extended Properties="IMEX=1;HDR=NO;"

Read Only

The file containing the source data might be impossible to edit, due to a share deny write mode inside the query.

Solution

Change the "Mode" to "Read

Mode=Share Deny Write

Mode=Read

Some other mode could be :

• Mode='Read';
• Mode='Write';
• Mode='ReadWrite';
• Mode='Share Deny None';
• Mode='Share Deny Read';
• Mode='Share Deny Write';
• Mode='Share Exclusive';

Other options

Inside the "query properties"

• uncheck "include field names" otherwise you get an additional line at the top of the sheet
• check "keep cell format"
• choose "erase the cell data" if necessary