raz05
asked on
Import Negative Number into access
We are trying to import records from a csv file into Access. One of the fields is a long integer field and the csv file is a negative field and has a trailing - sign. When we import the file into the database we receive a type conversion error. If I create an import spec and import the data into a long field it works fine. An import spec is not practical in our case - is there a way to make the regular import process work?
Alternatively, create a table in access with the fields you require, and set the datatype of the problem field to text, then import into that table.
-0tacon-
-0tacon-
ASKER
The file is too large to open and change the format. We are able to change the field to text and then modify to be a number but were hoping for a cleaner process as it is our clients who will be doing the imports.
Import the file (name import) but keep the field with the trailing negative as text.(fldtxt)
Add a field of type integer (fldint) and do an update query
update import set fldint = cint(fldtxt);
the nice thing about cint() is that it recognizes trailing signs.
Add a field of type integer (fldint) and do an update query
update import set fldint = cint(fldtxt);
the nice thing about cint() is that it recognizes trailing signs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
routinet: I could not find a way to get the import spec to handle trainling signs. I kept getting an importerror table
Sorry, that should have read:
routinet: I could not find a way to get the import spec to handle trailing signs. I kept getting an import error table.
routinet: I could not find a way to get the import spec to handle trailing signs. I kept getting an import error table.
I dunno...I haven't tried working with import specs since ACC97. Truth be told, I would have first tried to change the export format, and failing that I would have used your solution. Most likely, I wouldn't have tried too long on changing the export format either. :)
Raz05 said he tried using a spec and got it to work just fine, but did not want to use it because his end users may not know how to work with them. Perhaps he will be so kind as to tell us what he did....? In the meantime, I have a few minutes after my cigarette. I'll see if I can't play with it some.
Raz05 said he tried using a spec and got it to work just fine, but did not want to use it because his end users may not know how to work with them. Perhaps he will be so kind as to tell us what he did....? In the meantime, I have a few minutes after my cigarette. I'll see if I can't play with it some.
ASKER
I did use an import spec and was able to import the field as a long int. I have found in the past that I was able to import dates this way which were not formatted in a way normally accepted (can't remember for sure but think it was yyyy-mm-dd).
We are going to modify the Oracle export program to change the way the data is formated on the csv file.
We are going to modify the Oracle export program to change the way the data is formated on the csv file.
I know you can do that for dates, but I've never used it for different number formats. Looks like I got some experimenting to do. :)
-0tacon-