Import External Data does not import all data

[gordon3d]

I cannot understand why "Data/Import external Data/Import Data" does not import all data from another Excel spreadsheet when I execute it.

I cannot understand why some data is imported and others are not (seemed random...I cannot pick any pattern anyway). I keep refreshing and it keeps giving me the same data.

 

[XLGibbs]

How is it that you are getting the data? via an external list in excel or are you using MS Query to use excel as a database?

 

[gordon3d]

The "Data/Import external Data/Import Data" creates a MS Query for me and that is executed. I open up the Query:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=\\laboratory1\SharedDocs\Phu Bia Gold Mine\RGT Daily Process Report.xls;Mode=Share Deny Write;Extended Properties="HDR=NO;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Command Type: Table; Command Text: Data_Ore

I don't know if there is something I need to do here.



PS: I am still new to "List". Is this a better alernative route?

 

[XLGibbs]

I would try and use External Data > New Database Query

the menus will guide you on picking an Excel file, then you would pick the sheet...then you will see the "columns" available in the sheet and you can create a query (similar to an Access style query)...

You can click the double arrow to bring back all fields ...

Not sure why the import data method you are using is not bring in new data unless the source file is not changing.

 

[gordon3d]

The import is not bringing in new data because it has opened a copy of the original file and kept it open. When it ipfates, ot keeps upfating form the opened copy and not from the original source (which is being ipfted all the time).

I will try 'External Data > New Database Query".

 

[XLGibbs]

I sent you a PM, I can help you with this off board if need be. Please keep the topics to one thread or the moderators will start locking one of the two...

 

[gordon3d]

Does anyone come across this where some data are imported and some are not? (using "Data > Import External Data > Import Data" route). I does not need to selective import data from a Named Range or utilise a query to filter data to import. I just need everything imported (within a Named Range in a source Excel File)

What am I doing wrong.

 

[gordon3d]

I just tried 'External Data > New Database Query". It is giving me the same data importation (missing data). For example, one Named Range has 42 columns of data. All data in column 22 is missing (Column Name is given "Column 22" automatically when importing)? Other data are also missing but that moght be too confusing is I list them.

This happens using both methods of importation:

'Data > Import External Data > New Database Query'
'Data > Import External Data > Import Data'

I checked the source file and the data is there.

 

[XLGibbs]

When you import the named range, does it give you the preview of the columns?

It sounds now as if the problem is an extra delimiter in the named range being interpreted as a new / or blank column.

My guess is column 21 ends in a comma or space and the import is reading that as it's own column.

Can you check the source files named range to makes sure each column has a header? It will also assign the numerical sequence column number when it comes across "blank" header row data.

 

[gordon3d]

When you import the named range, does it give you the preview of the columns? Yes it does. I select a bunch of column and selected ones does not import. The result is exactly the same as 'Data > Import External Data > Import Data'
It sounds now as if the problem is an extra delimiter in the named range being interpreted as a new / or blank column. The Name Ranges do not include headers. Yes I do have D/S, N/S and A/S. I tried to change to DS, NS and AS but made no difference.

My guess is column 21 ends in a comma or space and the import is reading that as it's own column. Maybe not relevant and my Named Ranges do not include headers.

Can you check the source files named range to makes sure each column has a header? It will also assign the numerical sequence column number when it comes across "blank" header row data. Named Ranges do not include headers

No progress