Scientific #'s in Datagrid 2

[RonRepp]

Hi all:

I have a csv file that I'm populating a dataset and then a datagrid. One field has very long strings (CC NUM), and it is displaying it in scientific #'s, like Excel.

I've set the datatype to system.string...

You'll probably get more use out of the code than my yammering.

Sub OpenDXP()
        Dim i As Integer
        Dim s As String
        Dim conCSV As ADODB.Connection
        Dim rsCSV As ADODB.Recordset
        Dim SQL As String
        conCSV = New ADODB.Connection
        rsCSV = New ADODB.Recordset
        conCSV.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtDir.Text & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""

        conCSV.Open(conCSV.ConnectionString)

        SQL = "SELECT  * FROM " & RD + strDate + ".csv"

        rsCSV.Open(SQL, conCSV, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, ADODB.CommandTypeEnum.adCmdText)

        DS = New DataSet("DS1")
        TBL = New DataTable("TBL1")
        For i = 0 To rsCSV.Fields.Count - 1
            DC = New DataColumn(rsCSV.Fields(i).Name)
            DC.DataType = System.Type.GetType("System.String")

            TBL.Columns.Add(DC)

        Next

        Do While Not rsCSV.EOF
            DR = TBL.NewRow
            For i = 0 To rsCSV.Fields.Count - 1
                DR(rsCSV.Fields(i).Name) = rsCSV(i).Value

            Next

            rsCSV.MoveNext()
            TBL.Rows.Add(DR)
        Loop
        DS.Tables.Add(TBL)
        DG2.CaptionText = RD + strDate + ".csv"
        DG2.SetDataBinding(DS, "TBL1")
    End Sub

Everything else loads fine.

Any ideas?

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Sorwen]

I don't know about data grids, but just as a thought to give you incase. Excel usually does that because the number is longer than the field. Is you field big enough to fit the full number?

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

Oh I forgot to mention the side thought (I some times get to a conclusion and forget to mention some of the steps I took to get there). Like excel it could be that some times no matter what you tell it sometimes excel will still convert a string to a number (when doing various imports of data). I don't know if a data grid does it, but in excel you can force it by putting a single quote (') before the number.

-I hate Microsoft!
-Forever and always forward.

 

[Qik3Coder]

if it really will not let you use an int or an INT64, then try converting it to a char before you bring it in:

Select cast(cc_num as varchar(16)), * from myTable

-The answer to your problem may not be the answer to your question.

 

[RonRepp]

Thanks for the responses.

About 2 a.m. (didn't figure any of you were still looking to help idiots like me) I discovered that it's coming from the recordset that way.

I put a message box in, eliminated the DG, and the msgbox came up with the same scientific annotation.

Now I'm lost, because I've used the same connection string hundreds of times for CSV and never had this problem. That's why I ASSUMED it was the datagrid.

Should I try maybe going to the text drivers, or, perhaps, another Connection String?

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Qik3Coder]

RonRepp, you may want to be careful with that logic, about it coming from the CSV that way.
Did you open the CSV, and see the scientific notation?

If not then the message box is behaving in an identical fashion to the datagrid.

You may want to try my suggestion and see if it helps.

-The answer to your problem may not be the answer to your question.

 

[RonRepp]

Q3:

Okay, I'll try it and get back to you. Are you saying that you want my sql string to say: "Select cast(cc_num as varchar(16)), * from " & RD + strDate + ".csv"?


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

Q3:

Nope, no luck. The funny thing is that for American Express Cards it works fine. I also use virtually identical coding in another program and it works.

This one has me scratching my head.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Qik3Coder]

So the SQL didn't work, which means it is trying to process it as an int before it gets to the SQL.

Sidenote- American Express cards are 16 or 12 digits?

Can you make a similar function but instead of loading the values into the datagrid, just pull back the credit card column and try putting it into an INT64 and try messageboxing those values. (to see what they look like)

Try with a cast as float, in your SQL

Select cast(col1 as float) from ...

place the value into an INT64



-The answer to your problem may not be the answer to your question.

 

[Sorwen]

Again just general information and questions.

"Provider=Microsoft.Jet.OLEDB.4.0;"

This is the same Provider used by Excel/Access. It can decide to do strange things when pulling in data. I can make the suggestion to use another Provider, but it is the only one I've figured out how to use so I couldn't suggest which other(s) or how to use them. Not much help, but I wanted to explain what might be happening. If it is the provider nothing you do in code is going to convert it correctly.

For example a number like 123456789012 will pull in as something like 1.23457E+11, but will convert as 123457000000. So you are not going to get the full number as you would need it. This may not be happening, but from what has been said sounds likely.

What creates the CSV file? Depending what is creating it might give some other ideas as to what changes that could be made. Looking just at the raw CSV file does it wrap the credit card number in quotes like "123456789012" or just 123456789012? If no quotes wrapping the number in quotes may do the trick.

Does the data save to a database for what you are getting the info from and you export it as a CSV file? If that is the case could you pull directly from that database instead? If there are some security issues involved then it is possible to setup for readonly access for most databases or through the connect itself.

Though it might be a little more annoying you might think about just reading it as a simple text file rather than through an adodb connection. That way it would be read and could be converted however you wanted.

-I hate Microsoft!
-Forever and always forward.

 

[RonRepp]

Q3 & Sorwen:

Sorry for the delay. The project got pushed back and I had not had time to get back to you.

Q3: I agree with what you're saying. I've done a few of these, but this is the first one that I just tried to pull using a * wildcard. Every other one I listed out the fields and used the actual field values, so you may be right...probably are. I'll have to go ahead and list them all out. I've been avoiding this because there are 125 field names that I have to parse.

Sorwen: No quotes. I checked the other files and they had no quotes, either. I did, however, add quotes to a section of the table that was brought in, but I received the same results.

Well, back to it.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Qik3Coder]

keep the select *, if you need it.

what i was getting at was just select the 1 column to use as testing.


-The answer to your problem may not be the answer to your question.