CODEWITHSUNDEEP
oracle-databasems-accessdecimal
user441521
user441521

Reputation: 6998

Access 2007 export table to csv decimal

I have some tables in Access that I'm trying to export to csv so that I can import to Oracle. I don't use the export via ODBC because I have 70K - 500K records in some of these tables and that feature takes way to long as I have about 25 tables to do so I want to export to csv (which is much faster) then load via sqlldr.

Some numeric columns can go out to 16 decimal places and I need them all. However when I export they only go out 2. I've done some googling around this. Regional settings only allows 9 decimals out (Win XP), formatting the column via a query will change it to text which I don't want when I import to Oracle (maybe I can use to_number() in the control file?).

Why is this so difficult? Why can't Access just export numeric columns as they are?

Upvotes: 3

Views: 1813

Answers (2)

HansUp
HansUp

Reputation: 97131

In my Access 2007 test case, I'm not seeing quite the same result you described. When I export to CSV, I get all the decimal places.

Here is my sample table with decimal_field as decimal(18, 16).

id some_text decimal_field
-- --------- ------------------
 1 a         1.0123456789012345
 2 b                          2

Unfortunately, those exported decimal_field values are quoted in the CSV:

"id","some_text","decimal_field"
1,"a","1.0123456789012345"
2,"b","2"

The only way I could find to remove the quotes surrounding the decimal_field values also removed the quotes surrounding genuine text values.

If quoted numeric values are unworkable, perhaps you could create a VBA custom CSV export procedure, where you write your values to each file line formatted as you wish.

Regarding "Why is this so difficult?", I suspect decimal data type as the culprit. I don't recall encountering this type of problem with other numeric data types. Unfortunately, that's only my speculation and won't help even if it's correct.

Upvotes: 1

SeanC
SeanC

Reputation: 15923

  1. Create a query selecting all the records from your table. Format the troublesome column by using the format function:
    Select Format(Fieldname,"0000.00000") AS FormattedField
    Save this query and export the query instead of the table.

    One disadvantage of this approach is that your numeric field is then treated as text, so you then get quotes around the exported numbers, and if you use the option not to enclose text in quotes, then any actual text fields you export in the same query lose their quotes too

  2. The other (quicker, dirtier, bodge job) method is to export first into Excel and from there to text. This leaves decimal places intact, but obviously it's not very elegant.

Upvotes: 0

Related Questions