Export Zip Codes with Leading Zeros

[sardine53]

I have built several SQL Queries using the contact1.zip and contsupp.zip. We need to export the query results to Excel (by right clicking -> Output to -> Excel)for mailing lists. Unfortunately, even though the zips display with the leading zero "08034" the result is "8034"--very annoying as we are in NJ most of our clients have zips with leading zeros. In our database there are 5 and 9 digit zips.

Does anyone know if there is a way to modify the SQL statement to accomodate this?

We currently have GoldMine Premium Edition but this happened in our old version as well.

Thanks in advance for any assistance.

 

[DJHunt]

That is the nature of Excel. Highlight the colum in Excel and change it to:

Category: Special
Type: ZipCode or ZipCode+4

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )
http://www.DJHunt.US/Default.html

 

[sardine53]

Thanks DJ -

I thought there would be a way to to convert the field in SQL, but this is an easy solution. I'm just hoping it retains the zero when we go to do the mailing.

 

[sardine53]

It didn't retain the leading zero in the mail merge. Any other suggestions?

Thanks

 

[DJHunt]

After you set the column to zipcode, copy the column and paste special as Values.

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )
http://www.DJHunt.US/Default.html

 

[sardine53]

Tried that, the Zip now is "8034". Is there nothing you can do in SQL?

 

[DJHunt]

This has nothing to do with SQL, and is strictly the way that Excel is interpreting the data.

Couldn't you simply Export the data from GoldMine as a csv file?

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )
http://www.DJHunt.US/Default.html

 

[sardine53]

From the SQL Query in GoldMine, the only options are Output to: Excel, Word, Clipboard.

 

[DJHunt]

What information are you trying to get into Excel? Could it not be done more simply via a Filtered/Grouped Esport?

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )
http://www.DJHunt.US/Default.html

 

[sardine53]

I don't think so, but here is the SQL Query:

select contact1.company Carrier, contsupp.contact,contsupp.title,contsupp.address1,
contsupp.address2, contsupp.city, contsupp.state, contsupp.zip, contsupp.mergecodes
FROM contsupp, contact1
WHERE contsupp.accountno=contact1.accountno and
contsupp.rectype='c'and
contsupp.mergecodes like '%Carrier%'
ORDER BY contact1.company ASC, contact1.Contact ASC

 

[DJHunt]

Nope, not with GoldMine. You are going to have to learn how to manipulate columns in Excel.

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )
http://www.DJHunt.US/Default.html