View Single Post
  #1   Report Post  
Night Owl
 
Posts: n/a
Default Formatting of Data from .CSV file

Hi,
I've got a database that exports data to a CSV file that I would like to
import into an Excel spreadsheet, which I can do, but there are one or two
anomalies in that the data is in text format rather than numbers, which I
need to resolve.

For example, a date string is exported in the format
"2004-04-01-03.29.00.000000" which I would like to extract into separate
date (dd/mm/yyyy) and time (hh:mm:ss) fields.

Also, the same spreadsheet has an address field, with underscores as
separators. Is the a (simple) method that can be used to strip out spurious
text (e.g. specific symbols), insert a comma and space, then reformat the
text to sentence case rather than a mixup of upper and lower case.
Obviously (?) the addresses are nearly all different, but the reason I want
to do this is to count the number of times an address occurs in the
database.

Would it better to run an import macro into the spreadsheet to append the
data (approximately 25-50 records a week on top of an existing database of
some 25000 records) or should I import the data directly into Access and
format it from there?

Any help would be appreciated.