Export > csv; Numbers rounded to 2dp (2002 SP3)

Topic

New Reply

This is a longstanding problem that I have mostly circumvented by avoiding using Access.

Whenever I export to .csv, numeric fields (single or double) are always rounded to 2dp – regardless of how I have formatted the fields & despite the export dialogue showing the numbers in their full precision. Eg. even where I have specified a field with 6dp, only 2dp is exported. I have spoken to other users who experience the same problem, but no one has been able to offer a work-around other than multiplying by say 1000, then dividing the exported values by the same number. Not so easy with csv files that are too large to fit into a spreadsheet. I have searched high & low, but found nothing in the export dialogue that allows control over the format of numbers exported.

Seems a dangerous limitation, especially as the same does not apply for Excel, which has a similar (& entirely usable) csv export facility. I work with csv files quite often (input to Fortran programs that cannot read .mdb). When I have to use Access (data availability), I often forget about the 2dp. A length of time issue + I frequently generate csv files ex Excel (+ no doubt, aging neurons).

Does anyone here know if it is possible to export from mdb to csv without numbers being rounded to 2dp?? Have I missed something fundamental, or is this a real limitation?

Thanks
David

(apologies if this has been asked a million times already – I did check back through the last 5 – 6 pages of posts, but without a search facility …)

Reply | Quote

Replies

We do have a search facility – click the Search button in the Lounge menu at the top of every Lounge page.

Reply | Quote

See ACC2000: Exporting to Text File Truncates to Two Decimals Places (valid for other versions of Access too).

Reply | Quote

Hans, many thanks.

A first look at the site was a turn off – “Requires basic macro, coding, and interoperability skills” !!! Never managed scripting (hair too thin & grey – will stick with Fortran, albeit f95 now). Did not even know where to start to add a “script” that included “Field: …”.

Later I played a bit with a query & realised that no scripting was required – just the Build tool to add in the Format spec & all worked a treat. Only one minor mod from the site example (“##0.0000” to “0.00###”). They would serve their customers better if they removed the ” .. macro, coding, …” line. Even better, if they would fix the problem.

In the interim, I discovered an alternative, very simple solution. After all these years!!! Just imported the .mdb into my GIS program (Manifold) & re-exported as csv. No truncation issues – not even format issues. A couple of minutes & it was done.

Very puzzling that the field format set via the field properties is not respected during export to csv, while the alternative above is. Seems a fundamental flaw. The mind boggles as to the thinking behind rounding everything to 2dp, and especially that the FLAW has not been fixed after all these years (sorry, this does NOT qualify as a feature). Bet this has cost some users big bucks – well beyond the not insignificant time I have lost over several years trying to circumvent the problem or find a solution.

Many thanks for the pointer
David

Reply | Quote

I guess I’m a bit mystified as to why simply clicking the Save Formated option box doesn’t meet your needs. The real issue behind all of this is that in general you are dealing with either single or double precision floating point (unless you specify the field as currency), so figuring out the number of significant digits gets to be a real pain (unless you want to use scientific notation). Am I missing a point here?

Reply | Quote

Save Formatted option box ?? Compare attached examples: 1. Save Formatted; 2. a csv file as needed.

Where is the mystery? They do not remotely look the same animal.

Quite apart from the layout, the former is 5x the size of the latter. Not nice when the latter can be several hundred MB.

Hard to see why a problem with the number of dp’s. If formatted as 5dp, then output 5dp. If the number of dp not specified, then output all available precision for each value; ie as per 2. attached (csv output from Manifold, with text qualifiers stripped). Excel seems to manage; Manifold manages.

David

Reply | Quote

To follow up on my earlier reply, the solution is in fact very simple, entirely within Access.

Its just that it does not seem to be well known – I have searched the Help in vain in the past for guidance on controlling the number of dp in exported data. I have also asked others who are much more experienced in using Access, equally in vain.

The solution is as outlined in the link that Hans pointed to. The details there initially looked daunting to me as a relative novice with Access, but as indicated in the attached, it is very straightforward. Just create a Query with the full Table in it (or whatever parts required) & use a formula to specify the number of dp. eg. in the example illustrated, field has been replaced by the formula (specifies between 2 & 5 dp, depending on the precision of the data). Pity the articles do not include examples as per the attached. Would be helpful to many inexperienced users.

The importance of the above is that the csv export respects the format set by the formula, whereas it does not respect formatting set via the field properties (which is tha hard part to understand).

David

Reply | Quote