Excel- Import External Data-missing data problem on refresh

[whes]

Dear All,

I've been troubleshooting this problem for 2 weeks now and still can't find the root cause. Anybody's help is much appreciated. Thanks in advanced!

Setup:
- I got 1 master excel file with 13 worksheets representing 13 cities
- Each city have it's own excel file which is updated by the person responsible on that city.
- The excel files are located in 1 server where I do the consolidation on my master excel file.
- Basically, each worksheet is linked to the excel file of one city via "Import External Data" function.
- Each worksheet contains a table (6 columns and xx rows). The data are formatted numerically.

What happened:
- Some worksheets works perfectly when refreshing the data (all data needed in columns 1 to 6 are being refreshed).

Problem:
- In some of the worksheets, only column 1 to 4 and 6 are being refreshed properly. The data on column 5 are still empty. However, if I checked the source file (data), column 5 have a value. (I used "define name" function to identify the range of data i need)

What i tried:
- I recreated all the links, etc.. It worked in some of the worksheets but didnt work for others
- I made sure that all formats and queries of working worksheets are identical with the non-working ones.
- I search this forum, google and MS KB but no solution found.
- I tried on excel 2000, XP and 2003 and it has the same problem.
- I also tried to switch the query of working sheets and the non-working and the result seems to suggest that the problem is not on the query but on the data source.

Please help. Thanks!

 

[Mark W.]

Did you check for hidden columns in the source data files?

 

[whes]

Hi Mark,

Thanks for quick reply. I do have 2 hidden columns but I did not include it in my "data range" because I dont need that data. Column 5 is also NOT hidden.

I have re-checked my data range and 100% sure that it captures what I need.

Thanks.

 

[devalentin]

I have exactly the same problem no data in column T gets imported. It's really weird I cannot find any solution on web. Did you ever get to the bottom of this? Thanks

 

[IKEA-John]

I also have the same issue - in Excel 2002. I have 38 worksheets I am trying to import, and about 75% of them come in OK... but 25% are missing just one column's data. There are no differences between the 38 worksheets... they are formatted identically.... and the data that is missing is not in a hidden column.

 

[Bere]

I was having what appears to be the same problem. Here is how I fixed it. I made sure that the entire column of the source table was identically formatted. For one column I had to select the format "text" from among the number formatting options. Another a formatted as "numeric". I also have some columns with date formatting. After ensuring that the problem columns were formatted identically from top to bottom, it began working.

 

[Fazza]

The symptons sound like mixed data type in the source fields.

For example this article http://support.microsoft.com/kb/257819

Considerations That Apply to Both OLE DB Providers

<SCRIPT type=text/javascript> loadTOCNode(3, 'moreinformation'); </SCRIPT>A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.


For example:

• In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
• In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
• In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.

As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode.

Some good info here too
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

 

[thinksomid]

i have a problem a little like this, im not expert in excel.
some simple columns containing formulas on external files are empty when i open master file, but when i open external source files, calculated values appears suddenly.
some body can help me?
tnx