[AccessD] Automating Excel imports

A somewhat clunky solution (which I've used before) is be to have your
users put a dummy line in as the first line of the spreadsheet, and
you can use this to force the format of the input fields.

Doug

On Wed, Jun 22, 2011 at 6:10 PM, Darrell Burns <dhb at flsi.com> wrote:
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>