Results 1 to 16 of 16

Thread: Large number in CSV file

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    41

    Large number in CSV file

    I try to export my data as a .csv file with the following code
    Code:
    Open StrPath & "\ CardNoRpt.csv" For Output As #FileNum
    Write #FileNum, "SlNo", "Name", "Address", "BoxNo", "CardNo"
    
    NumberOfRows = vRsRepo.RecordCount
    vRsRepo.MoveFirst
    For vRowNo = 1 To NumberOfRows
        Write #FileNum, CInt(vRowNo), vRsRepo.Fields("Name"), vRsRepo.Fields("Address"), vRsRepo.Fields("BoxNo"), vRsRepo.Fields("CardNo")
        vRsRepo.MoveNext
    Next
    vRsRepo.Close
    Close #FileNum
    In the above, the BoxNo column is very long containing about 20 numeric characters.
    While exporting what happens is that the string 40065812120403355 sits as 4.00658E+16.
    And if I double click the cell it shows 40065812120403300. i.e the field got truncated.

    How to export this as-it-is ? It should not appear like 4.00658E+16.
    I can't even prefix with "'" (i.e.single quote)
    Please help me fix this problem.
    Last edited by ragavendran; Feb 8th, 2018 at 07:17 PM.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Large number in CSV file

    Will a Format$() work?

    Maybe something like: Format$(vRsRepo.Fields("BoxNo"), "#0")

    EDIT1: Also, that's a VERY large number. What's the "type" in the database? To get numbers that large, and if they're integers, you'll probably need to use a Decimal type.

    EDIT2: The largest Decimal type is 79228162514264337593543950335.

    EDIT3: Also, and I'm not sure why (or where), but I'm guessing it's being converted to an IEEE Double somewhere along the way, and a Double is going to run out of precision at about 15 digits, and you're right at the limit of that.

    EDIT4: Also, you say "cell". It sounds like you're opening it with Excel. Have you checked the output file in Notepad to see what's in there? I'm not sure but Excel may just be reading it as a Double even though there's full precision in your output file.

    EDIT5: If EDIT4 is correct, to fix it, put a single quote (') before the number and then Excel will read it as a string/text. You could still use the Format$() function, something like this: Format$(vRsRepo.Fields("BoxNo"), "'#0")

    Notice the (') in the format string.
    Last edited by Elroy; Feb 8th, 2018 at 07:47 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Large number in CSV file

    Are you actually performing any calculations using that number? It looks more like it should be a Text field.

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    41

    Re: Large number in CSV file

    BoxNo is a Text column in my Access DB.
    It's nothing but a product number printed on the box.
    I do not make any calculation with it.
    I just need to export my data to .CSV file in excel.

    I already mentioned in my Post #1 that I can't prefix it with " ' " i.e. single quote.

    Two issues.
    1. The text value got truncated
    2. It appears as a scientific notion
    Last edited by ragavendran; Feb 8th, 2018 at 07:57 PM.

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Large number in CSV file

    If it is Text, then you should handle it as text in your program, and in the .csv file, write it out as text surrounded by double quotes (") or Chr$(34) characters:

    Code:
    "40065812120403355"
    "40065812120403300"

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Large number in CSV file

    ragavendran,

    It's appearing as scientific notation in Excel! Did you look in your CSV file with Notepad? If it's a Text field in the database, I'll bet dollars-to-donuts it's just fine in the CSV file. Excel is just "seeing" it as a number. Therefore, it's going to typecast it into an IEEE double when it reads it.

    So, here's a question for you. How important is it to read this CSV file with Excel? If that's not important, then you're all set. If that IS important, then you need to find a way to be okay with putting a single-quote-mark before it.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Large number in CSV file

    @jdc2000: Surrounding with double-quotes still doesn't help him with Excel. The only way I know to get there is to preface it with a single-quote (').

    EDIT1: In fact, it's been decades since I've used the Write statement, but I believe it'll already put the double-quote-marks around it. He's just still going to need that single-quote mark (even if, after that, it's surrounded with double-quotes).
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    41

    Re: Large number in CSV file

    Quote Originally Posted by Elroy View Post
    @jdc2000: Surrounding with double-quotes still doesn't help him with Excel. The only way I know to get there is to preface it with a single-quote (').

    EDIT1: In fact, it's been decades since I've used the Write statement, but I believe it'll already put the double-quote-marks around it. He's just still going to need that single-quote mark (even if, after that, it's surrounded with double-quotes).
    oh my goodness ! I just wanted to know if it's possible without the quote prefix.

    What's the other way you mentioned about in your earlier reply ? i.e. " if not opening with Excel is an issue "

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Large number in CSV file

    Hi,

    try it like this..

    Artikel is a Table in the Database
    Code:
    Private Sub Command28_Click()
    Dim strSQL_CSV As String
    Dim strSQL_Excel As String
    
     
      strSQL_CSV = "Select *  Into [data1.csv] In 'c:\' 'Text;' From Artikel"
      
      strSQL_Excel = "SELECT * INTO [TabImport] In  'C:\data81.xls' 'EXCEL 8.0;' FROM Artikel"
     
    adoConnection.Execute strSQL_CSV
    adoConnection.Execute strSQL_Excel
    
    adoConnection.Close
    End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Large number in CSV file

    or directly in excel vba with the 'copyfromrecordset' method
    Code:
    Sub doit()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = OpenDatabase("E:\northwind.mdb")
        Set rs = db.OpenRecordset("Clients")
        Sheet1.Range("A1").CopyFromRecordset rs
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End Sub
    do not put off till tomorrow what you can put off forever

  11. #11

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    41

    Re: Large number in CSV file

    Quote Originally Posted by ChrisE View Post
    Hi,

    try it like this..

    Artikel is a Table in the Database
    Code:
    Private Sub Command28_Click()
    Dim strSQL_CSV As String
    Dim strSQL_Excel As String
    
     
      strSQL_CSV = "Select *  Into [data1.csv] In 'c:\' 'Text;' From Artikel"
      
      strSQL_Excel = "SELECT * INTO [TabImport] In  'C:\data81.xls' 'EXCEL 8.0;' FROM Artikel"
     
    adoConnection.Execute strSQL_CSV
    adoConnection.Execute strSQL_Excel
    
    adoConnection.Close
    End Sub
    regards
    Chris
    Actually I do so many things after retrieving from the Access Table, put them in a Recordset and then I transfer the Recordset to CSV.

    For e.g

    Code:
    vSql = "Select * from myTable"
    set vRs = new recordset
    vrs.open vsql,,,,,,,,,
    
    vrs.movefirst
    do while not vrs.eof
      ***some calculations** here
        If vUnpaidStatus = False Then
                vRow = vRow + 1
                vRsRepo.AddNew
                vRsRepo("SlNo") = vRow
                vRsRepo("CusNo") = vRs("CusNo")
                vRsRepo("Name") = vRs("Name")
                vRsRepo("Address") = vRs("Address")
                vRsRepo("BoxNo") = CommaConvert(vRs("BoxNo"))
                vRsRepo("CardNo") = CommaConvert(vRs("CardNo"))
                
                vRsRepo.Update
               
            End If
    
    vrs.movenext
    loop
    So, now from vRsRepo I need to export as a CSV excel file......

    Now can you help me to know how can I implement this line :::: "Select * Into [data1.csv] In 'c:\' 'Text;' From Artikel"

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Large number in CSV file

    Hi,

    create a query with all the Calculations, Insert that query to a New Table with Select Into...
    now you can pass the New Table like I've shown you above

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    41

    Re: Large number in CSV file

    Quote Originally Posted by ChrisE View Post
    Hi,

    create a query with all the Calculations, Insert that query to a New Table with Select Into...
    now you can pass the New Table like I've shown you above

    regards
    Chris
    Sorry I do not get you

  14. #14
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Large number in CSV file

    Quote Originally Posted by ragavendran View Post
    Actually I do so many things after retrieving from the Access Table, put them in a Recordset and then I transfer the Recordset to CSV.

    For e.g

    Code:
    vSql = "Select * from myTable"
    set vRs = new recordset
    vrs.open vsql,,,,,,,,,
    
    vrs.movefirst
    do while not vrs.eof
      ***some calculations** here
        If vUnpaidStatus = False Then
                vRow = vRow + 1
                vRsRepo.AddNew
                vRsRepo("SlNo") = vRow
                vRsRepo("CusNo") = vRs("CusNo")
                vRsRepo("Name") = vRs("Name")
                vRsRepo("Address") = vRs("Address")
                vRsRepo("BoxNo") = CommaConvert(vRs("BoxNo"))
                vRsRepo("CardNo") = CommaConvert(vRs("CardNo"))
                
                vRsRepo.Update
               
            End If
    
    vrs.movenext
    loop
    So, now from vRsRepo I need to export as a CSV excel file......

    Now can you help me to know how can I implement this line :::: "Select * Into [data1.csv] In 'c:\' 'Text;' From Artikel"
    what is vRsRepo ?
    what do you calculate

    EDIT: added Function(s)
    Code:
    'try one of these Functions
    vrs.movefirst
    do while not vrs.eof
      ***some calculations** here
        If vUnpaidStatus = False Then
                vRow = vRow + 1
                vRsRepo.AddNew
                vRsRepo("SlNo") = vRow
                vRsRepo("CusNo") = vRs("CusNo")
                vRsRepo("Name") = vRs("Name")
                vRsRepo("Address") = vRs("Address")
                vRsRepo("BoxNo") =  GetNum(vRs("BoxNo"))
    'or:
    ' vRsRepo("BoxNo") =  GetNumStr(vRs("BoxNo"))
                vRsRepo("CardNo") = CommaConvert(vRs("CardNo"))
                
                vRsRepo.Update
               
            End If
    
    vrs.movenext
    
    
    'liefert einen Numerischen Wert als Variant
    Public Function GetNum(Rs As ADODB.Recordset, _
                           FieldName As String, _
                           Optional DefaultIfIsNull As Variant = 0) _
                           As Variant
          If IsNull(Rs.Fields(FieldName).Value) Then
             GetNum = DefaultIfIsNull
          Else
             GetNum = Rs.Fields(FieldName).Value
          End If
    End Function
    
    'liefert einen numerischen Wert als String
    Public Function GetNumStr(Rs As ADODB.Recordset, _
                              FieldName As String, _
                              Optional DefaultIfIsNull As _
                              String = vbNullString, _
                              Optional sFormat As String = _
                              vbNullString) As String
       Dim s As String
          If IsNull(Rs.Fields(FieldName).Value) Then
             s = DefaultIfIsNull
          Else
             s = Rs.Fields(FieldName).Value
          End If
          
          If Len(sFormat) > 0 Then
             s = Format(s, sFormat)
          End If
          GetNumStr = s
    End Function
    is this shown in a Listview or MsFlexgrid ?

    regards
    Chris
    Last edited by ChrisE; Feb 9th, 2018 at 07:41 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  15. #15
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Large number in CSV file

    Good morning ragavendran,

    I didn't thoroughly read all the over-night posts (just scanned them), but it seems you need to identify precisely where the problem is. You can open your CSV file by right-clicking it and selecting "Open with..." and then selecting Notepad (rather than Excel). That way, you can directly examine the CSV file to see what's in it.

    I'm betting that it'll have the correct (non-rounded) information in it. Your problem is entirely just the way Excel reads these files, and not what VB6 is actually doing.

    Or, as others have suggested, you could have VB6 assist you with re-reading these files.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  16. #16
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Large number in CSV file

    Another option would be to use an Excel macro to import the file. Then you can control how the data is put into an Excel sheet, including setting the cell or column as Text.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width