Hello, i'm using Access 2010
I am now managing a database which is being used or importing text files.
The problem or question I have is, Can a spec be programmed dynamically to deal with different number formats and always change to English.
For example, if I import a German text file using my stored spec, I would like it to recognise / convert 1.000,99 to 1,000.99
6 2650
I don't think the import Specification itself can be set up in such a way. But it would be possible to import it as text, to a temporary table, then run a code to check if the third character from the right is a , or a . and react accordingly, and then finally transfer the records from the temp table into your main table.
Interesting and simple, thank you. Using your method, I woudl then append the temp table to the main table of cause, is there a function available for converting German to English for example?
you could use the Mid() function, to find the value of the third to last character in teh string: -
Dim sChar as String
-
sChar = Mid(UnknownMonetaryValue, Len(UnknownMonetaryValue)-2, 1)
-
If sChar = "," Then
-
'Value is German
-
ElseIf sChar = "." Then
-
'Value is UD
-
Else
-
'Value is Unknown
-
End If
-
zmbd 5,501
Expert Mod 4TB
The problem is that OP didn't specify if the numer was currency, fixed point, or some other floating point; thus, left-2 may not work for the text input.
-z
zmbd 5,501
Expert Mod 4TB
A partial thought....
Logically, the position of the division mark between the integer and factional parts of the number will follow the grouping mark ( for English: commas before periods etc...)
Using English notation we need to handle the following:
[a] # - any integer less than one-thousand
[b] #,000 - any integer greater than 999
[c] #.0# - any numerical less than one-thousand
[d] #,000.0# - any numerical greater than 999
Using the instr function
[a] determine if there are any comma/periods and if not then treat as any standard integer
[b] This is tricky if the # is not equal to zero. If the first number is zero, and there are no other numbers between the leading zero and the dividing mark, then we can treat the number as a fractional and convert as needed. However if the first number is non-zero then we may have a situation as in [c]
[c] same as [b]
[d] if the comma is before the period then we have an English style etc.
Cases [b] and [c] have me stumped for the moment
-z
diannosd,
Do you have a sample of the text files you will be importing? This would assist the entire forum in coming up with some ideas for importing....
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Piet |
last post by:
I am developping a small Python/wxPython app to track expenses. A
bunch of information is collected in a wxGUI consisting of text
controls, comboboxes and a small grid. Some values are not put in...
|
by: Roger |
last post by:
I am currently working on an application that is being
used mostly in the US but recently has been purchased by
several users outside the US, mostly in South America. The
application uses an...
|
by: Larry Rekow |
last post by:
Have been using macros to do automatic importing of flat text files
lately....wish I knew about his capability before :)
question: is there any way to specify one of the fields I import as
being...
|
by: Trev |
last post by:
Is there a way to create/manipulate import specs in VBA?
|
by: Tony Williams |
last post by:
I have a 4 Page report , the second page is in two column format. At the
moment I have to print page 2 as a separate report and then collate it with
the other pages. Is there any way to print a...
|
by: Vig |
last post by:
Is scanf or any other function capable of reading numbers in the format
1.2345d-13 where 'd' serves the same role as 'e' usually does in scientific
notation? This operation is iterated through...
|
by: Sam |
last post by:
Hi,
How can I check if a string corresponds to a valid number format ?
e.g :
1,000.00
1.000,00
1 000,00
1'000.00
How to check the above are valid number formats ?
|
by: Sam |
last post by:
Hi,
I've already asked a kind of question like that, but it's a bit
different this time. How can I get all of the supported numeric formats
by Windows? I want to fill a combobox with those...
|
by: =?Utf-8?B?UHVjY2E=?= |
last post by:
I Have the following in my own library project and I call it from my C#
application twice each time to get Unix group account information and user
account information. But it would return...
|
by: A_H |
last post by:
Hi, I'm using PyExcelerator, and it's great, but I can't figure out a
few things:
(1) I set the cell style to '0.00%' but the style does not work.
(2) I want to place a border around the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |