Q: importing Excel data with zipcodes into an Access table

[Guest_imported]

Hi, I am trying to import Excel data into an access database, from a VB program. This code works:
msa.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblName", "bookname", True, sRange

but, if i have any zipcode fields in the Excel sheet, they come in as numbers, stripped of leading zeroes(a big problem in New Jersey, since there are many zipcodes with leading zeroes) This seems to be the case even when I format the zipcodes in Excel as text fields or ZipCode fields.

Any responses(including "too bad. can't do it. don't waste your time&quot would be greatly appreciated.

thank you

jim regan
jimregan79@hotmail.com

 

[mistervague]

I ran in to this problem using the Import command within Access, and nearly tore my hair out. Since you're using VB, the source of the problem might be something else entirely, but it's worth a shot.

Here's something to try:
In Access, you probably don't need to have zip codes stored as numbers. So you can change that field to a small text field. Then in Excel, change the zip codes to text format (ignoring errors) and do the import.

I remember having to do something crazy like adding quotes around the zip codes in Excel, importing them to text fields, then doing a Find/Replace in Access to remove all the "

Hope that helps.

 

[jimregan79]

thanks for your tip, but I was hoping to avoid a workaround like that!!
What I'm currently doing is importing the data, then execute a command that checks for any zipcode with length < 5, and if that record is in a state that does have leading zeroes in their zipcodes (mostly New England states) I will update the zipcode field, setting it to &quot;0&quot; & zipCode, until the length is 5.

I can't really do alot to the Excel sheet, since the user will choose the excel sheet from a fileOpen dialog or click and drag or onto my application, kicking off my importing and subsequent processing of the data.

thanks again
jim