Data Type control when downloading into Excel

[Guest]

Hi guys,

I do a scheduled dump via task scheduler from Access to Excel.

One of the columns of data is the Zip Code.

Is there a way I could dump it so that the numerical zip codes are numbers,
but the non-number zip-codes like Canadian Zip Codes or the Zip Codes with
hyphen and 4 digit extensions are OK?

The data is in text in access.

Thanks!

 

[Dirk Goldgar]

In

Hi guys,

I do a scheduled dump via task scheduler from Access to Excel.

One of the columns of data is the Zip Code.

Is there a way I could dump it so that the numerical zip codes are
numbers, but the non-number zip-codes like Canadian Zip Codes or the
Zip Codes with hyphen and 4 digit extensions are OK?

The data is in text in access.

Hmm. That's complicated, because to Access the entire column must be
either text or number.

I'm not sure, but I think you could probably do this by exporting the
data to a text file in which you put quotes around the non-numeric
values and not around the numeric ones, and then automating Excel to
import the text file. Your export to text from Access would have to
involve using an export specification to tell Acc not to surround text
fields with quotes. You would then export a query with a calculated
field that puts quotes around the non-numeric zip codes, and doesn't put
them around the numeric ones.

It seems strange to me that you would want to do this, since even
numeric zip codes aren't really numbers in any meaningful sense, but
that's the only way I can think of to do it.

 

[Guest]

Hi Dirk,

I spoke with the analyst about leaving it in text, and the reason not to is
because that file is later used to generate an upload file, and the
apostrophe to indicate the number is text would cause the upload to crash.

 

[Dirk Goldgar]

In

Hi Dirk,

I spoke with the analyst about leaving it in text, and the reason not
to is because that file is later used to generate an upload file, and
the apostrophe to indicate the number is text would cause the upload
to crash.

If you gotta, you gotta. Did I give you enough to go on?

 

[Guest]

Actually, we reached a compromise.

I will truncate any zip codes that has the hyphenated extension. Since we
only do business in the US now, we don't need the Canadian zipcodes anyhow.
That way, it'll end up in number.

Thanks again!

 

[Dirk Goldgar]

In

Actually, we reached a compromise.

I will truncate any zip codes that has the hyphenated extension.
Since we only do business in the US now, we don't need the Canadian
zipcodes anyhow. That way, it'll end up in number.

Thanks again!

You're welcome.