Exporting query to csv - Number stored with additional zeros

jaryszek

Registered User.
Local time
Today, 13:05
Joined
Aug 25, 2016
Messages
756
Hi,

i am exporting table to csv with US settings:

attachment.php


but problem what i have when i opening this in Excel is:

attachment.php


Here i should have "64" not "64.00".

Did you have maybe similar issue?
Please help,
Jacek
 

Attachments

  • Screenshot_7.jpg
    Screenshot_7.jpg
    65.3 KB · Views: 189
  • Screenshot_8.png
    Screenshot_8.png
    11.1 KB · Views: 163
Hi. Just a thought but try exporting your data using a query where you convert your numbers into text.
 
hi theDBguy,

thanks.

i am clicking second mouse button in query and choosing export.

attachment.php


I have double ield format in my source table.

The issue is disappearing when i am using Decimal Simbol as "," (polish settings). This would be ok but i have to provide this file to Americans...

Best,
Jacek
 

Attachments

  • Screenshot_9.jpg
    Screenshot_9.jpg
    42.4 KB · Views: 163
Ok i found that when i am changing regional settings No. of digits after decimal to 0 i am getting "14." instead of "14.00" number...

attachment.php


It is possible to eliminate also "." from double exported from Access?

Jacek
 

Attachments

  • iXVFe.png
    iXVFe.png
    30.4 KB · Views: 154
Hi. Can you show us the SQL statement of your query? Thanks.
 
Hi,

Code:
SELECT DISTINCT tblServers.Server, tblvMaxDataDiskCount.MaxDataDiskCount, tblvMemorySize.MemorySize AS MemoryinGB, tblvCores.Cores AS NumberOfCores, tblDiskSizes.OSDiskSize AS OSDiskSizeInGB, tblDiskSizes.ResourceDiskSize AS ResourceDiskSizeInGB, tblTopologies.TopologyName AS Function, tblMachineInfo.MachineName AS Machine, tblWorkload.WorkloadName, tblvScale.Scale, tblCustomers.CustomerName AS Customer
FROM tblvMemorySize RIGHT JOIN (tblvMaxDataDiskCount RIGHT JOIN (tblvCores RIGHT JOIN (tblTopologies RIGHT JOIN (tblServers INNER JOIN ((tblMachineInfo RIGHT JOIN (tblDiskSizes RIGHT JOIN ((tblCustomers RIGHT JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) INNER JOIN (((tblCustomerTopologyServer INNER JOIN tblCustTopoServerDiskSizes ON tblCustomerTopologyServer.CustomerTopologyServerID = tblCustTopoServerDiskSizes.CustomerTopologyServerIDFK) INNER JOIN tblCustTopoServerMaxDataDisk ON tblCustomerTopologyServer.CustomerTopologyServerID = tblCustTopoServerMaxDataDisk.CustomerTopologyServerIDFK) INNER JOIN tblServerSpecs ON tblCustomerTopologyServer.CustomerTopologyServerID = tblServerSpecs.CustomerTopologyServerIDFK) ON tblCustomerTopology.CustomerTopologyID = tblCustomerTopologyServer.CustomerTopologyIDFK) ON tblDiskSizes.DiskSizeID = tblCustTopoServerDiskSizes.DiskSizeIDFK) ON tblMachineInfo.MachineInfoID = tblServerSpecs.MachineInfoIDFK) INNER JOIN (tblServerDetailedInfo LEFT JOIN ((tblWorkload RIGHT JOIN tblWorkloadScaleDetails ON tblWorkload.Workload_ID = tblWorkloadScaleDetails.WorkloadID) LEFT JOIN tblvScale ON tblWorkloadScaleDetails.ScaleID = tblvScale.ScaleID) ON tblServerDetailedInfo.ServerDetailedInfoID = tblWorkloadScaleDetails.ServerDetailedInfoIDFK) ON tblServerSpecs.ServerSpecID = tblServerDetailedInfo.ServerSpecIDFK) ON tblServers.ServerID = tblCustomerTopologyServer.ServerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK) ON tblvCores.CoreID = tblServerDetailedInfo.CoreIDFK) ON tblvMaxDataDiskCount.MaxDataDiskCountID = tblCustTopoServerMaxDataDisk.MaxDataDiskCountID) ON tblvMemorySize.MemorySizeID = tblServerDetailedInfo.MemorySizeIDFK
WHERE (((tblCustomers.CustomerName)='Microsoft'))
ORDER BY tblTopologies.TopologyName;

thank you.

I think that in Access it is not possible...

https://stackoverflow.com/questions/21938911/csv-export-of-an-access-query-limits-float-single-double-values-to-2-decimal-p

Best,
Jacek
 
why is the trailing zero an issue? I can't imagine it would ever be a problem.
 
Hi gemma-the-husky.

Because my customer doesn't want to have zeros in the end.

They want to see 14 GB not 14.00 GB. And question is how later access will import it?

Best,
Jacek
 
But a customer won't look at a csv (or shouldn't care)

They will import it into excel or a database, and it will be correct in there.

If you are want to have total control, then instead of

docmd.transferdata "queryname" to produce the csv


you will need to do this all manually - and then you can get whatever presentation you want when you write the row.

Code:
create a text file
open query recordset

write header row
for each row in query
  write data row to text file
next row

close recordset
close textfile
 
Hi,

thank you.

I think that in Access it is not possible...

https://stackoverflow.com/questions...its-float-single-double-values-to-2-decimal-p

Best,
Jacek
Hi Jacek. Okay, see if this makes any difference...
Code:
SELECT DISTINCT tblServers.Server,
   tblvMaxDataDiskCount.MaxDataDiskCount, 
   tblvMemorySize.MemorySize AS  MemoryinGB, 
   tblvCores.Cores AS NumberOfCores, 
   [b]Format(tblDiskSizes.OSDiskSize,"@") AS  OSDiskSizeInGB, 
   Format(tblDiskSizes.ResourceDiskSize,"@") AS ResourceDiskSizeInGB,[/b] 
   tblTopologies.TopologyName AS Function, 
   tblMachineInfo.MachineName AS  Machine, 
   tblWorkload.WorkloadName, 
   tblvScale.Scale, 
   tblCustomers.CustomerName AS Customer
FROM tblvMemorySize 
RIGHT JOIN (tblvMaxDataDiskCount 
RIGHT JOIN  (tblvCores 
RIGHT JOIN (tblTopologies 
RIGHT JOIN (tblServers 
INNER JOIN  ((tblMachineInfo 
RIGHT JOIN (tblDiskSizes 
RIGHT JOIN ((tblCustomers  
RIGHT JOIN tblCustomerTopology 
  ON tblCustomers.CustomerID =  tblCustomerTopology.CustomerIDFK) 
  INNER JOIN  (((tblCustomerTopologyServer 
  INNER JOIN tblCustTopoServerDiskSizes 
    ON  tblCustomerTopologyServer.CustomerTopologyServerID =  tblCustTopoServerDiskSizes.CustomerTopologyServerIDFK) 
  INNER JOIN  tblCustTopoServerMaxDataDisk 
    ON  tblCustomerTopologyServer.CustomerTopologyServerID =  tblCustTopoServerMaxDataDisk.CustomerTopologyServerIDFK) 
  INNER JOIN  tblServerSpecs 
    ON tblCustomerTopologyServer.CustomerTopologyServerID =  tblServerSpecs.CustomerTopologyServerIDFK) 
    ON  tblCustomerTopology.CustomerTopologyID =  tblCustomerTopologyServer.CustomerTopologyIDFK) 
    ON  tblDiskSizes.DiskSizeID = tblCustTopoServerDiskSizes.DiskSizeIDFK) 
    ON  tblMachineInfo.MachineInfoID = tblServerSpecs.MachineInfoIDFK) 
  INNER  JOIN (tblServerDetailedInfo 
  LEFT JOIN ((tblWorkload 
  RIGHT JOIN  tblWorkloadScaleDetails 
    ON tblWorkload.Workload_ID =  tblWorkloadScaleDetails.WorkloadID) 
  LEFT JOIN tblvScale 
    ON  tblWorkloadScaleDetails.ScaleID = tblvScale.ScaleID) 
    ON  tblServerDetailedInfo.ServerDetailedInfoID =  tblWorkloadScaleDetails.ServerDetailedInfoIDFK) 
    ON  tblServerSpecs.ServerSpecID = tblServerDetailedInfo.ServerSpecIDFK) 
    ON  tblServers.ServerID = tblCustomerTopologyServer.ServerIDFK) 
    ON  tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK) 
    ON  tblvCores.CoreID = tblServerDetailedInfo.CoreIDFK) 
    ON  tblvMaxDataDiskCount.MaxDataDiskCountID =  tblCustTopoServerMaxDataDisk.MaxDataDiskCountID) 
    ON  tblvMemorySize.MemorySizeID = tblServerDetailedInfo.MemorySizeIDFK
WHERE (((tblCustomers.CustomerName)='Microsoft'))
ORDER BY tblTopologies.TopologyName;
Hope it helps...
 
Hi!

theDBguy - brilliant! Working like a charm !

Only thing what i have to remember is importing data to change to double format.

Thank you!
Jacek
 
Hi!

theDBguy - brilliant! Working like a charm !

Only thing what i have to remember is importing data to change to double format.

Thank you!
Jacek
Hi Jacek. You're welcome. Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom