Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Export of a CSV file loses second decimal place...

Export of a CSV file loses second decimal place...

Scheduled Pinned Locked Moved Database
questiontutorial
11 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • N Offline
    N Offline
    new_phoenix 0
    wrote on last edited by
    #1

    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:

    R 1 Reply Last reply
    0
    • N new_phoenix 0

      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:

      R Offline
      R Offline
      riced
      wrote on last edited by
      #2

      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.

      N 2 Replies Last reply
      0
      • R riced

        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.

        N Offline
        N Offline
        new_phoenix 0
        wrote on last edited by
        #3

        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

        A 1 Reply Last reply
        0
        • N new_phoenix 0

          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

          A Offline
          A Offline
          Ashfield
          wrote on last edited by
          #4

          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

          N 1 Reply Last reply
          0
          • A Ashfield

            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

            N Offline
            N Offline
            new_phoenix 0
            wrote on last edited by
            #5

            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?

            A 1 Reply Last reply
            0
            • R riced

              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.

              N Offline
              N Offline
              new_phoenix 0
              wrote on last edited by
              #6

              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?

              A R 2 Replies Last reply
              0
              • N new_phoenix 0

                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?

                A Offline
                A Offline
                Ashfield
                wrote on last edited by
                #7

                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

                N 1 Reply Last reply
                0
                • N new_phoenix 0

                  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?

                  A Offline
                  A Offline
                  Ashfield
                  wrote on last edited by
                  #8

                  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

                  1 Reply Last reply
                  0
                  • A Ashfield

                    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

                    N Offline
                    N Offline
                    new_phoenix 0
                    wrote on last edited by
                    #9

                    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

                    A 1 Reply Last reply
                    0
                    • N new_phoenix 0

                      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?

                      R Offline
                      R Offline
                      riced
                      wrote on last edited by
                      #10

                      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.

                      1 Reply Last reply
                      0
                      • N new_phoenix 0

                        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

                        A Offline
                        A Offline
                        Ashfield
                        wrote on last edited by
                        #11

                        OK, 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

                        1 Reply Last reply
                        0
                        Reply
                        • Reply as topic
                        Log in to reply
                        • Oldest to Newest
                        • Newest to Oldest
                        • Most Votes


                        • Login

                        • Don't have an account? Register

                        • Login or register to search.
                        • First post
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • World
                        • Users
                        • Groups