Problem with Alpha Characters in Numberic Field

[mkdrep]

I have been downloading .csv files from a construction website that we use to following projects as they develop. I import the file into Excel and then want to bring it into my database. The issue is with two fields I have that are numeric, however, when downloaded into Excel, the information in the two fields now have an (')added to the beginning and end of the string, i.e. and the second field has an (') and (-) to the data.

'201400409710'
'201300697683'

The second field is a zipcode field that comes in like this:
'14063-1127'
'14222-1004'

I would like to design a query to strip the ' from each field as well as the (-) in the zipcode field.

If I have to write 2 separate queries that is fine, I just can't figure out how to do this....

Thanks in advance for the help....

Mark

 

[CJ_London]

The problem is probably to do with excel reinterpreting the values - what does the data look like if you look at the .csv file in notebook? you might be better to import the .csv file instead.

Because of the -, this will be interpreted as text. So suggest link to the file and write a query to import the data into your table

then you can use the the replace function in your query to remove the -

 

[mkdrep]

CJ, the problem is that the website I download the data from only allows me to download it in a .csv format. I can't directly import it into Access...

Thx for your reply

 

[mkdrep]

The problem is probably to do with excel reinterpreting the values - what does the data look like if you look at the .csv file in notebook? you might be better to import the .csv file instead.

Because of the -, this will be interpreted as text. So suggest link to the file and write a query to import the data into your table

then you can use the the replace function in your query to remove the -

How does one use the replace function in a query....sorry I don't have any formal training in Access.......

 

[CJ_London]

Having downloaded the file - link to it. It is generally better to have a .csv file anyway.

To use the replace function you can look it up online - here is a link

http://msdn.microsoft.com/en-us/library/bt3szac5(v=vs.90).aspx

in your case it is simply

replace(zipcode,'-','')