Export of a CSV file loses second decimal place...
-
When exporting a data file from MS Access into a CSV file, the dollar amount loses the second decimal place. When clicking the mouse on the cell with the data, it shows .72 in the top address bar, but the cell itself only shows .7. What is the reason, and how do I correct it so that it shows the correct amount. I suspect that there may need to be some formatting before it is exported, but I do not know how to format it correctly. I believe it should be formatted as "Format Cells" and "Number" with "2" decimal places, but how do I do this programmatically? :confused::confused::confused:
-
When exporting a data file from MS Access into a CSV file, the dollar amount loses the second decimal place. When clicking the mouse on the cell with the data, it shows .72 in the top address bar, but the cell itself only shows .7. What is the reason, and how do I correct it so that it shows the correct amount. I suspect that there may need to be some formatting before it is exported, but I do not know how to format it correctly. I believe it should be formatted as "Format Cells" and "Number" with "2" decimal places, but how do I do this programmatically? :confused::confused::confused:
The second decimal place is not being lost. You can check this by opening the CSV file in Notepad or some other text editor). I assume you are opening the CSV in Excel so the fact that Excel displays it in the 'address bar' shows that. You need to set the cell formatting in Excel to display 2 decimal places.
-
The second decimal place is not being lost. You can check this by opening the CSV file in Notepad or some other text editor). I assume you are opening the CSV in Excel so the fact that Excel displays it in the 'address bar' shows that. You need to set the cell formatting in Excel to display 2 decimal places.
Correct. When I put the cursor in the cell, it shows the value as being stored in the cell as .72 but it only displays .7. However, when I manually format it using "Number" with "2" decimal places, it shows the full value. If I open the CSV file and use the AutoSum feature, the aggregate total does not match the original data file. From this effect, I can conclude that it is not really being used in the calculation properly. To that end, I need to programmatically save the value as if I were manually sitting there in the middle of the code and applying the "Format > Cells" and "Number" with "2" decimal places. How can this be done? Here is some code.
strAggregateGroupFileName1 = "qryAggregationMatching" strAggregateGroupSQLString1 = "SELECT tblGroupGLCodeMapping.GL\_CODE, " & \_ "tblGroupGLCodeMapping.GL\_CATEGORY, tblInputGroupFile.GL\_AMOUNT " & \_ "FROM tblGroupGLCodeMapping INNER JOIN tblInputGroupFile " & \_ "ON tblGroupGLCodeMapping.GL\_CODE = tblInputGroupFile.GL\_CODE" Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strAggregateGroupFileName1, \_ strAggregateGroupSQLString1)
The code should probably go around: tblInputGroupFile.GL_AMOUNT
-
Correct. When I put the cursor in the cell, it shows the value as being stored in the cell as .72 but it only displays .7. However, when I manually format it using "Number" with "2" decimal places, it shows the full value. If I open the CSV file and use the AutoSum feature, the aggregate total does not match the original data file. From this effect, I can conclude that it is not really being used in the calculation properly. To that end, I need to programmatically save the value as if I were manually sitting there in the middle of the code and applying the "Format > Cells" and "Number" with "2" decimal places. How can this be done? Here is some code.
strAggregateGroupFileName1 = "qryAggregationMatching" strAggregateGroupSQLString1 = "SELECT tblGroupGLCodeMapping.GL\_CODE, " & \_ "tblGroupGLCodeMapping.GL\_CATEGORY, tblInputGroupFile.GL\_AMOUNT " & \_ "FROM tblGroupGLCodeMapping INNER JOIN tblInputGroupFile " & \_ "ON tblGroupGLCodeMapping.GL\_CODE = tblInputGroupFile.GL\_CODE" Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strAggregateGroupFileName1, \_ strAggregateGroupSQLString1)
The code should probably go around: tblInputGroupFile.GL_AMOUNT
It can't. You are exporting data to a flat file, if you open your csv file in notepad you will see the data is actually correct. It is Excel that is applying the formating, and as you are not exporting to Excel you have no control over it.
Bob Ashfield Consultants Ltd
-
It can't. You are exporting data to a flat file, if you open your csv file in notepad you will see the data is actually correct. It is Excel that is applying the formating, and as you are not exporting to Excel you have no control over it.
Bob Ashfield Consultants Ltd
Actually, in the data can be formatted in the query but it would need to be done explicitly with code like CovertDecimal(AMOUNT, 2). Is there code like this?
-
The second decimal place is not being lost. You can check this by opening the CSV file in Notepad or some other text editor). I assume you are opening the CSV in Excel so the fact that Excel displays it in the 'address bar' shows that. You need to set the cell formatting in Excel to display 2 decimal places.
The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:
Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)
I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?
-
Actually, in the data can be formatted in the query but it would need to be done explicitly with code like CovertDecimal(AMOUNT, 2). Is there code like this?
In actual fact you are not taking any notice of what people are telling you. THE DATA IN THE FILE WILL BE CORRECT IF YOU TAKE THE TROUBLE TO LOOK. The problem is the default format for the data in EXCEL. Yes, you can format the data to decimal, look in help for how to do it, but it will make no difference.
Bob Ashfield Consultants Ltd
-
The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:
Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)
I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?
Please see the answers you have been given. YOU ARE NOT SETTING ANYTHING IN EXCEL, YOU ARE WRITING A FLAT FILE. For the last time, the problem is the default format for Excel is not 2 decimal places, and there is NO way you can set it from a csv file as it is NOT Excel
Bob Ashfield Consultants Ltd
-
In actual fact you are not taking any notice of what people are telling you. THE DATA IN THE FILE WILL BE CORRECT IF YOU TAKE THE TROUBLE TO LOOK. The problem is the default format for the data in EXCEL. Yes, you can format the data to decimal, look in help for how to do it, but it will make no difference.
Bob Ashfield Consultants Ltd
Here is the issue, the data file is not yet in CSV file format, so while the data is in VBA format in MS Access, it can be modified to a decimal format. Here is the code.
'Still in VBA in MS Access
strFileName = "qryAggregationGroupEntityMatching"
strSQLString = "SELECT tblBSMappingGroup.COUNTRY, " & _
"tblBSMappingGroup.ENTITY, tblBSMappingGroup.ACCOUNT_LABEL, " & _
"tblBSMappingGroup.GL_CATEGORY, qryAggregationRollingTotals.ACTUAL " & _
"FROM qryAggregationRollingTotals INNER JOIN tblBSMappingGroup ON " & _
"qryAggregationRollingTotals.GL_CATEGORY=tblBSMappingGroup.GL_CATEGORY"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strFileName , _
strSQLString)'Exports to CSV
strOutputCSV1Path = strOutputFilePath + " - Grouping File.xls"
strMakeTableUpdateQuery = "SELECT qryAggregationGroupEntityMatching.ENTITY, " & _
"qryAggregationGroupEntityMatching.ACCOUNT_LABEL, qryAggregationGroupEntityMatching.ACTUAL " & _
"INTO tblExportCSV1 FROM qryAggregationGroupEntityMatching"
DoCmd.SetWarnings False
DoCmd.RunSQL (strMakeTableUpdateQuery)
strReplacementOutputString = "csv"
strNewOutputString = Replace(strOutputCSV1Path, _
strRemovedOutputString, strReplacementOutputString)
DoCmd.TransferText acExportDelim, "Standard Output", _
"tblExportCSV1", strNewOutputString, False -
The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:
Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)
I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?
The Selection.NumberFormat function will do it in Excel. E.g. Range("A1:A5").Select Selection.NumberFormat = "0.00" will format cells A1 to A5. To format all of column A use Columns("A:A").Select instead of Range. This has to be a macro or VBA subroutine in Excel. If you use a subroutine you could trigger it with the the WorkBook_Open event.
-
Here is the issue, the data file is not yet in CSV file format, so while the data is in VBA format in MS Access, it can be modified to a decimal format. Here is the code.
'Still in VBA in MS Access
strFileName = "qryAggregationGroupEntityMatching"
strSQLString = "SELECT tblBSMappingGroup.COUNTRY, " & _
"tblBSMappingGroup.ENTITY, tblBSMappingGroup.ACCOUNT_LABEL, " & _
"tblBSMappingGroup.GL_CATEGORY, qryAggregationRollingTotals.ACTUAL " & _
"FROM qryAggregationRollingTotals INNER JOIN tblBSMappingGroup ON " & _
"qryAggregationRollingTotals.GL_CATEGORY=tblBSMappingGroup.GL_CATEGORY"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strFileName , _
strSQLString)'Exports to CSV
strOutputCSV1Path = strOutputFilePath + " - Grouping File.xls"
strMakeTableUpdateQuery = "SELECT qryAggregationGroupEntityMatching.ENTITY, " & _
"qryAggregationGroupEntityMatching.ACCOUNT_LABEL, qryAggregationGroupEntityMatching.ACTUAL " & _
"INTO tblExportCSV1 FROM qryAggregationGroupEntityMatching"
DoCmd.SetWarnings False
DoCmd.RunSQL (strMakeTableUpdateQuery)
strReplacementOutputString = "csv"
strNewOutputString = Replace(strOutputCSV1Path, _
strRemovedOutputString, strReplacementOutputString)
DoCmd.TransferText acExportDelim, "Standard Output", _
"tblExportCSV1", strNewOutputString, FalseOK, one last time. A CSV file is NOT an Excel file, it does NOT contain formatting information and there is no way under the sun you are gong to make Excel put the fields in any format except its default one. The ONLY reason a csv file open in Excel is because of the file associations on your computer. If you want, you could make Word the default association for a csv file - would you expect it to input and format the text then? It does not matter what you do with decimal places in the csv file, Excel will format the column with its default settings.
Bob Ashfield Consultants Ltd