-
Feb 8th, 2018, 07:14 PM
#1
Thread Starter
Member
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.
-
Feb 8th, 2018, 07:31 PM
#2
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.
-
Feb 8th, 2018, 07:38 PM
#3
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.
-
Feb 8th, 2018, 07:53 PM
#4
Thread Starter
Member
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.
-
Feb 8th, 2018, 08:25 PM
#5
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"
-
Feb 8th, 2018, 08:25 PM
#6
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.
-
Feb 8th, 2018, 08:27 PM
#7
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.
-
Feb 9th, 2018, 12:47 AM
#8
Thread Starter
Member
Re: Large number in CSV file
Originally Posted by Elroy
@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 "
-
Feb 9th, 2018, 03:50 AM
#9
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.
-
Feb 9th, 2018, 04:14 AM
#10
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
-
Feb 9th, 2018, 04:32 AM
#11
Thread Starter
Member
Re: Large number in CSV file
Originally Posted by ChrisE
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"
-
Feb 9th, 2018, 04:40 AM
#12
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.
-
Feb 9th, 2018, 05:18 AM
#13
Thread Starter
Member
Re: Large number in CSV file
Originally Posted by ChrisE
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
-
Feb 9th, 2018, 06:18 AM
#14
Re: Large number in CSV file
Originally Posted by ragavendran
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.
-
Feb 9th, 2018, 08:43 AM
#15
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.
-
Feb 9th, 2018, 06:43 PM
#16
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|