Linked spreadsheet problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2K3 database with a link to and Excel spreadsheet. The
speadsheet has data in all 9700 rows, but when I view it through the link
several thousand are null records. Same result if I query it, do a make
table, save it as a csv, imported using SQL Server 2K DTS package. There is
data in all of the rows...I've verified several times. What could be the
problem?

Thanks
 
Hi Phill,

One likely cause is that the columns in the spreadsheet contain a
mixture of text and numeric data. Access normally assigns data types on
the basis of what it finds in the first rows. For the gory details, see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819 .

I've no experience with DTS, so don't know whether that behaves the same
way.

But when you say "save it as a csv" do you mean that you use File|Save
As in Excel to save the worksheet as a CSV file and *that* has missing
data? If so, the problem is certainly in the workbook: saving from Excel
to CSV and then importing or linking the CSV in Access is one of the
recommended ways of overcoming the mixed data type problem. Another is
to create a table in Access with fields and field names that match the
columns and column headers in Excel, and the field types you need - and
to import the Excel data into the existing table.
 
Phill: frustrating as all can be, I have no answer to your
search/frustrations; I seek a method, of searching for phone
numbers/addresses, for filling and future needs. Hope you get your answers
soon...Tarry
 
Back
Top