[AccessD] Automating Excel imports

Here's a clarification of the problem - it's not that the zipcodes are
numeric, it's that SOME of them are alphanumeric.  For example, the first 30
records contain numeric US zipcodes, then record 31 is a Canadian
alphanumeric. Since TransferSpreadsheet determined the data type to be long
integer from the first 20 rows, it pukes when it hits row 31. 

I tried linking instead of importing, but get the same error when I do an
insert query. I also tried importing into a new table; the good news is that
it didn't throw an error...the bad news is that the alphanumeric postcodes
weren't imported at all. Kinda makes me wonder how much other data my import
routines are missing!

Looks like the only solution is the one you proposed, Mark.

-Darrell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Wednesday, June 22, 2011 5:28 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automating Excel imports

Doesn't matter...my solution still stands.
Only limitation : 65,535 rows max in Excel 2003. 1048575 rows max in Excel
2007.


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Doug Murphy
> Sent: Wednesday, June 22, 2011 7:01 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Automating Excel imports
>
> In my previous response I should have said the data we get is in comma
> delimited text files.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell
> Burns
> Sent: Wednesday, June 22, 2011 3:28 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Automating Excel imports
>
> My client needs to frequently import batches of customer data into the
> CRM
> database I built for them in A2007. I created an Excel template which
> they
> use to supply the data, and I have an automated process that reads in
> the
> batch and loads the database. This is the command I use to do the
> import:
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> BatchTable,
> Import_File, True, CellRange
>
> All of the fields in BatchTable have a data type of 'text'.  The
> problem I'm
> running into is that TransferSpreadsheet barfs if any of the zipcodes
> in the
> spreadsheet are formatted as numbers. Before A2007 I used to use an
> Import
> Spec, which would circumvent this problem. For some unfathomable
> reason,
> this feature no longer exists (that I know of). If this was a one-time
> import I could deal with it by manually importing into a new table and
> then
> copying the data into BatchTable, but this is a recurring event.
>
> Any advice on how to deal with this issue?
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com