Import currency in European format

[Tim Green]

I am trying to import financial data in a European format from a fixed-width
..txt file to an Access 2000 database, using Access 2002. The format is as
follows: 1.000.000,00 positive numbers and (1.000.000,00) for negative.

The wizard does not seem to allow to change the thousands separator, only
the decimal separator. Ideally I would like to save any solution as an import
specification which users of the database can use to upload their reports. So
far the only solution I have found is to change my regional settings to
Germany before importing, which is not a solution that I am comfortable
rolling out to the rest of my team!

 

[John Nurick]

Hi Tim,

I can think of two approaches to this.

1) If importing into Currency fields, I'd try using a schema.ini file
instead of an import specification. Schema.ini exposes more settings
than the import/export wizard; I'd experiment with something like this
for the currency settings in the schema.ini entry for the file:

CurrencySymbol=
CurrencyPosFormat=0
CurrencyDigits=2
CurrencyNegFormat=0
CurrencyThousandSymbol=.
CurrencyDecimalSymbol=,

though I find the documentation on CurrencyPosFormat and
CurrencyNegFormat very confusing.

For more on schema.ini, see:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090

For details of the Schema.ini settings, see the Help topic "Initializing
the Text Data Source Driver", which is also online at
http://office.microsoft.com/en-us/assistance/HP010321661033.aspx
The schema.ini stuff is a long way from the start of the document.
See also (or alternatively, I can't work out which)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


2) Import the file into a "staging" table in which these fields are
defined as text; then use an append query to move the data from the
staging table into the table where you want it. Use a calculated field
in the query to convert the text string into the currency or number type
you need, e.g. something like this:

CurrencyValue: CCur(Replace(Replace([TextValue],".",""), ",", "."))

On Fri, 28 Oct 2005 01:59:06 -0700, Tim Green <Tim