Exporting query to csv - Number stored with additional zeros

[jaryszek]

Hi,

i am exporting table to csv with US settings:

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

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

Did you have maybe similar issue?
Please help,
Jacek

 

[theDBguy]

Hi. Just a thought but try exporting your data using a query where you convert your numbers into text.

 

[jaryszek]

hi theDBguy,

thanks.

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

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

 

[jaryszek]

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...

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

Jacek

 

[theDBguy]

Hi. Can you show us the SQL statement of your query? Thanks.

 

[jaryszek]

Hi,

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

 

[gemma-the-husky]

why is the trailing zero an issue? I can't imagine it would ever be a problem.

 

[jaryszek]

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

 

[gemma-the-husky]

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.

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

 

[theDBguy]

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...

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...

 

[jaryszek]

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

 

[theDBguy]

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.