Inport and Link spreadsheet to Access Table problems

[puforee]

I have tried linking and importing an excel spreadsheet to Access (2003).
I have fully analyzed the Format of the spreadsheet fields/cells and the values in the cells are all set to Number....there are no errors for any of the cells. In the spreadsheet I used the function =IsNumber() against the offending cells...it returns TRUE. But....when linked or imported to Access is comes up as "Text"

When I import or link the spreadsheet to Access about 1/2 of the numeric cells show as Text Format Fields. While doing the Import Wizard I cannot change the field format...it is blanked out. Once the data has been imported I can change the format for the offending fields to Number. This is very time consuming and not something I would want my users to do. I really need to LINK to the spreadsheet but need control over the format.


Help please.

 

[dhookom]

I typically have code that imports or links the Excel into a temporary table and then transform and append to a permanent table.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

What are the values in the first 8 rows in this column IN EXCEL. I suspect that there is at least ONE text value, maybe NUMERIC CHARACTERS rather than a number.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BJZeak]

From my experience, when attempting to transfer data between any source and destination, DATA TYPE has always been a challenge ... as such I have found the simplest solution is to ALWAYS transfer data in TEXT form. The source Application needs to covert all data types to a TEXT format and the destination Application is then responsible to covert these data fields back to their appropriate data types in whatever form the destination uses for those Types ... I use XML concepts of ELEMENT:ATTRIBUTE within the exported data to ensure non Text Types aren't ambiguous

For example: if I were to just pass a date of 01/09/11 with no context, Excel will attempt to store it as a date ... but what is the intended date? If I pass this same date as "01-09-11MY" I would just need a simple macro to covert this string to a date recoginized by Excel with no ambiguity ... dates have always been a challenge because everyone has anoter way to store dates

Bottom line: Exporting data in a "TEXT" format, gives you control over how the data will be treated in a destination application with little to no ambiguity (provided of course that you have the ability to manipulate the data in the destination)

I use xls template/macro files for exporting Access report data ... I have been using this concept for over a decade now ... I like EXCEL because it provides more flexibility with making data presentations look professional ... not saying Access reports can't be made to look professional ... I just find it easier/quicker to build a professional looking document in Excel.

For example I built a simple Invoice application which runs automatically in three steps:

1) a Build Invoice Button from an Access application, starts by making a copy of a standard (blank) xls invoice template file ... the file is copied to a common folder with a unique filename (in this particular app its ("inv-" & <inv-num> & ".xls")

2) The application then using Access's built in XLS functions, transfers specific data to what I call sub worksheets

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Invoice Header", chPath & "\Invoices\" & chFile & ".xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Invoice Items", chPath & "\Invoices\" & chFile & ".xls", False

The workbook basically has 3 worksheets; Invoice, Invoice Header and Invoice Items ... the header and items are built from simple queries in Access with header info pertaining to things like invoice date, bill-to, invoice number, invoice total, taxes, etc, and items are just details about the invoice items.

3) the Access application then shells to the EXCEL file. Excel automatically starts a MACRO which builds the final invoice based on the sub worksheet data, then deletes the sub worksheets, pass protects the file and finally does an offsite back up.

Over the years I have been able to quickly build these types of applications based on the excellent feedback from people on this site.

 

[BJZeak]

Egg on face ... Now realize you are asking about importing into ACCESS ... Sorry ... yes that is still a challenge ... I use EXCEL all the time as a quick DATA builder ... for Numbers and DATE what I do is select the entire worksheet ... clear all formats ... go to format and change entire sheet to "TEXT" ... then export as CSV file with first row as headings. This can then be imported into ACCESS with ease ... you then may have to write some module procedures to then convert DATES and or numbers into appropriate DATA TYPE fields ... I use back end tables for all my Access Apps ... the application file only contains forms and form code ... the BE file has the data and contains import routines for uploading data from XML, EXCEL etc ... these are typically loops that read and write, from-to tables. ie export csv from EXCEL and import into a temp table in ACCESS then read each record in the temp table and create actual data records in the destination table(s).

This may sound like a huge waste of effort but I have wasted more time trying to find a needle in a haystack caused by importing data Directly into a table without going through this process.

 

[SkipVought]

go to format and change entire sheet to "TEXT"

[red]
FORMAT changes NOTHING!!!
[/red]
The underlying values are UNCHANGED. All NUMERIC types on the sheet are STILL NUMBERS!!!!

You must CONVERT numbers to TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

This is very time consuming and not something I would want my users to do.

Get used to transforming data a number of times prior to display and keeping lots of versions of things. Thats what IT is all about.