How to handle missing values when cleaning data?

[TerraEarth]

So I have a few imports that I use to funnel data into Access. I have to actually clean the data first (I use the R language with the RStudio IDE to do this) before I import it into Access because of how messy it is. There are lots of empty fields in this data of different categories. Some of it are number values that represent currency, some are emails, names, phone numbers, etc. My question is, what is the best way to represent these values in my access tables?

Nothing? Null? Empty String? 0?


Ive been using a .csv format in order to export the cleaned data to Access, however this defaults all my data to the 'string' type. Is there a easy way to set up Access or the export so that the data types are somehow preserved in the transfer, or would I have to redefine all the types in my import tables before merging it with my main data?

[CJ_London]

My question is, what is the best way to represent these values in my access tables?

depends on the outcome required. If these are genuinely 'not known' then use null which is effectively what it means. Note you cannot use an empty string to populate a numeric field

a csv file is a text file, so will return text - but '123.45' will import to a decimal field OK. however not sure how it would behave if you are exporting to access. If you were to import then you can specify the column type in the import manager. Or you can link to the file and use an append query