java - 复制到 * CSV 文件时数据不匹配

Question

运行 sql 查询以将内容导出到 CSV 文件我注意到某些列没有正确显示。在我的转换日期时间戳中没有正确显示在单元格中

代码如下:

  COPY  (select hostname as "Host Name",devicetype as "Device Type",platform as "Model",Ipaddress as "IP Address",swversion as "Software Version",configuredTime as "Configured",activeTime as "Active",cluster as "Clusters",location as "Location",macaddress as "Mac Address",devicepool as "Device Pool" from (select getmodelinfo.ipaddress,max(getmodelinfo.hostname) as
            hostname, max(getmodelinfo.macaddress) as macaddress,
            max(getmodelinfo.devicetype) as devicetype,
            max(getmodelinfo.platform) as platform,
            max(getmodelinfo.swversion) as swversion, min(getmodelinfo.day_end_date) as configuredtime,
            max(getmodelinfo.active_end_date) as activetime,
            max(getmodelinfo.ucmclustername)
            as cluster, max(getmodelinfo.ucmlocation) as location,
            max(getmodelinfo.ucmdevicepool) as devicepool from (select
            pcwh_inv.uniquedeviceid,pcwh_inv.ipaddress,
            pcwh_inv.endpointmodel, pcwh_inv.hostname, pcwh_inv.devicetype, pcwh_inv.platform,
            pcwh_inv.macaddress, pcwh_inv.version as
            swversion,pcwh_inv.deployed_day_end_date as day_end_date,
            pcwh_inv.lastupdated_day_end_date as active_end_date,
            pcwh_inv.ucmclustername,pcwh_inv.ucmlocation,pcwh_inv.ucmdevicepool
            from pcwh_inventory_20160410 pcwh_inv,
            (select pcwh_inv.uniquedeviceid, max(pcwh_inv.lastupdated_day_end_date) as
            lasttime from pcwh_inventory_20160410 pcwh_inv where pcwh_inv.ipaddress
            notnull group by
            pcwh_inv.uniquedeviceid)gettime where pcwh_inv.endpointmodel = 'SX' and
            pcwh_inv.uniquedeviceid = gettime.uniquedeviceid and
            pcwh_inv.lastupdated_day_end_date = gettime.lasttime and  pcwh_inv.mgmtstatus not in ('Deleted')
                )getmodelinfo group by ipaddress) M) TO '/opt/emms/emsam/export/raj2.csv' DELIMITER ',' CSV HEADER 

请在下面找到我在不导出到 csv 的情况下运行查询得到的结果截图，在导出到 csv 时(下图)，当我点击特定行内容时单元格内容的图像它正确显示(最底部的图像)

在我看来，格式化 csv 文件存在问题，您能告诉我如何从查询级别执行此操作以显示 db 列中的确切内容吗？

Answer 1

我相信您的代码没有任何问题，但 Excel 将该列视为日期以外的其他内容，这种情况经常发生。可以通过选择所有受影响的单元格、右键单击并通过后续菜单将格式更改为文本来解决此问题。

如果此 CSV 将被另一个应用程序进一步操作，我认为您不会有问题，因为数据看起来很好。

如果出于某种原因您需要它在 Excel 中动态正确显示，您可能需要查看类似的内容.. Java - Excel Cell Type