String Values being turned into nothing or date-VBForums
Results 1 to 11 of 11

Thread: String Values being turned into nothing or date

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    180

    String Values being turned into nothing or date

    Hello:

    I have some code that reads in a csv through ado.net, and writes out a new csv.

    The input looks like this:

    Name:  Screen Shot 02-07-18 at 08.42 AM.jpg
Views: 79
Size:  22.8 KB

    My concern is only for Column A at this point.


    I read the value like this:
    Code:
                Dim Level As String = ""
                If IsDBNull(rs.Fields(0).Value) = False Then Level = (rs.Fields(0).Value)

    I write out the value like this:

    Code:
        Private Sub CreateCSVFile(ByVal _strNewCSVPath As String, ByVal _Level As String, ByVal _PN As String, ByVal _Desc As String, ByVal _Qty As Decimal, ByVal _Rev As String, ByVal _RawMatl As String, ByVal _MatlQty As Decimal, ByVal _MatlUnits As String)
            Try
                Dim objWriter As IO.StreamWriter = IO.File.AppendText(_strNewCSVPath)
                If IO.File.Exists(_strNewCSVPath) Then
                    objWriter.Write(_Level & ",")
    As you can see below, the A column is NOT the same string value. Something is being converted. I have not seen this in all csv files, but ran across it on one... In other words, my initial testing was flawless.

    Name:  Screen Shot 02-07-18 at 08.47 AM.jpg
Views: 82
Size:  27.4 KB

    Much thanks!

  2. #2
    Fanatic Member
    Join Date
    Aug 2004
    Location
    Essex, UK
    Posts
    543

    Re: String Values being turned into nothing or date

    Are you sure you do not have the column formatted as a date? If not, perhaps you should enclose those characters in quotes so they are handled as strings and not numbers e.g. "1.2.1" etc.

  3. #3
    Fanatic Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    979

    Re: String Values being turned into nothing or date

    If you have the column formatted as General, Excel will helpfully try to analyze the data and if it sees something that could be a Date, presto, it changes it to a Date. If the data is Text data, but is actually numbers, you need to format the column as Text to avoid this behavior.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    180

    Re: String Values being turned into nothing or date

    Then problem is these are csv files, no formatting that I know of. Can I do some kind of conversion in code?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    180

    Re: String Values being turned into nothing or date

    I tried this:

    Code:
    If IsDBNull(rs.Fields(0).Value) = False Then Level = "= '" & (rs.Fields(0).Value) & "'"
    Which produces this:
    Name:  Screen Shot 02-07-18 at 10.19 AM.jpg
Views: 33
Size:  21.9 KB

    The strange thing is, why am I getting blanks, because there is data on all the columns for
    Code:
    (rs.Fields(0).Value)

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,460

    Re: String Values being turned into nothing or date

    Step 1 - STOP opening the file in Excel. I can't count the number of times I have to tell my clients that.
    Step 2 - Open the file in a TEXT editor, make sure the col is still what it should be...

    As Noted, Excel will do funny things with data. Since it's a CSV, there's limited amount you can do with it... encasing it in quotes may help, I don't know. But stop opening it in Excel will help too.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,460

    Re: String Values being turned into nothing or date

    Quote Originally Posted by ssabc View Post
    I tried this:

    Code:
    If IsDBNull(rs.Fields(0).Value) = False Then Level = "= '" & (rs.Fields(0).Value) & "'"
    Which produces this:
    Name:  Screen Shot 02-07-18 at 10.19 AM.jpg
Views: 33
Size:  21.9 KB

    The strange thing is, why am I getting blanks, because there is data on all the columns for
    Code:
    (rs.Fields(0).Value)
    Oi... by putting the = on the front, you told Excel to treat it as a formula... so that's what it's doing... it looks at 1.2.1 and says, what I can I do wwith this? Hey, I think I can convert this to a date! And so it does... but 1.1 doesn't convert to a date, so it doesn't do anything with it, so... "blank" cells.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    180

    Re: String Values being turned into nothing or date

    The question is, why is it converting it to a date?

    Most files have run great!
    Normal file before:
    Name:  Screen Shot 02-07-18 at 10.37 AM.jpg
Views: 32
Size:  29.1 KB

    Normal file after:
    Name:  Screen Shot 02-07-18 at 10.37 AM 001.jpg
Views: 31
Size:  30.0 KB


    Problem file before:
    Name:  Screen Shot 02-07-18 at 10.38 AM.PNG
Views: 32
Size:  18.3 KB

    Problem file after:
    Name:  Screen Shot 02-07-18 at 10.38 AM 001.PNG
Views: 32
Size:  20.2 KB

    As I mentioned, until this file, 100% of the results have been what is desired. There is in fact data in every row of column 1 in the before scenario.

    Thanks again!

  9. #9
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,455

    Re: String Values being turned into nothing or date

    It looks to me like your original code makes some changes. For example, line 2 changes from "1.1" to 1.1.15 in the first two screenshots. There are a lot of other differences between the two lines.

    You didn't really show us all of your code. My understanding is you have a process like this:

    1. You start with a CSV file I'll call "original".
    2. Your program opens "original", processes it, and produces a new CSV file "new".
    3. You open "new" in Excel, and it looks different from what you expect.

    First, you need to verify that (2) is producing the output you expect. If that's not true, then you can't really blame Excel for displaying an incorrect file incorrectly.

    We're going to be fumbling around like blind people until you take a step back, explain the entire problem, and show your code to produce "new" from "original" along with examples of what you expect.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  10. #10
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    957

    Re: String Values being turned into nothing or date

    Hi ssabc,

    load the CSV to a new Access Table

    Code:
     Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
            Dim sSql As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")
            sSql = "Select * Into CSVImport From [Adressen.csv] IN 'c:\' 'Text;'"
            con.Open()
            ExecuteSQL(con, sSql)
            con.Close()
            con = Nothing
        End Sub
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    let's see then what the Import look's like

    EDIT:
    I have some code that reads in a csv through ado.net, and writes out a new csv.
    can you show us the Code, it might help




    regards
    Chris
    Last edited by ChrisE; Feb 7th, 2018 at 12:15 PM.
    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.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    180

    Re: String Values being turned into nothing or date

    My apology for any code confusion. As I said, it worked flawlessly on 100% of the files until it his this one.

    The code reads column B until it finds the proceeding column name with a .1, .2, etc., after the filename. Also, if there is no value in Column F it is ignored.

    Name:  Screen Shot 02-07-18 at 11.33 AM.jpg
Views: 23
Size:  18.5 KB

    The full result is the new csv file below. Columns A thru E come from the line without the .1, .2, .3 etc. in the filename. Columns F thru H come from the .1, .2, .3 lines respectively.

    Name:  Screen Shot 02-07-18 at 11.34 AM.jpg
Views: 23
Size:  43.9 KB

    The code is below:
    Code:
        Private Sub GetADOfromCSV()
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
    
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2" & ".csv", s)
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "SELECT * FROM [" & CSVFILENAME & "]" ' ORDER BY 1" ' Cannot add this because of 1.1.10 issue
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=Delimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
    
            rs.MoveFirst()
            Debug.WriteLine("New File to Create: " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2" & ".csv")
    
            Do Until rs.EOF
                For i As Integer = 0 To rs.Fields.Count - 1
                    Debug.WriteLine(rs.Fields(i).Name & " = " & rs.Fields(i).Value)
    
                Next
    
                Dim Level As String = ""
                Dim PN As String = ""
                Dim Desc As String = ""
                Dim Qty As Integer = -1
                Dim Rev As String = ""
                Dim RawMatl As String = ""
                Dim MatlQty As Decimal = 0.00
                Dim MatlUnits As String = ""
    
                If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                If IsDBNull(rs.Fields(5).Value) = False Then RawMatl = rs.Fields(5).Value
                If IsDBNull(rs.Fields(6).Value) = False Then MatlQty = rs.Fields(6).Value
                If IsDBNull(rs.Fields(7).Value) = False Then MatlUnits = rs.Fields(7).Value
    
                Dim rsb As Object = rs.Bookmark
    
                Do Until rs.EOF
                    rs.MoveNext()
                    Dim EvalPN As String = ""
                    If rs.EOF = False Then
                        If IsDBNull(rs.Fields(1).Value) = False Then EvalPN = rs.Fields(1).Value
                        Debug.WriteLine("EvalPN: " & EvalPN & ", " & "PN: " & PN)
    
                    End If
    
                    If EvalPN.Contains(PN) Then
                        If IsDBNull(rs.Fields(5).Value) = False Then RawMatl = rs.Fields(5).Value
                        If IsDBNull(rs.Fields(6).Value) = False Then MatlQty = rs.Fields(6).Value
                        If IsDBNull(rs.Fields(7).Value) = False Then MatlUnits = rs.Fields(7).Value
    
                        If Len(RawMatl) > 0 And RawMatl IsNot Nothing Then CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv", Level, PN, Desc, Qty, Rev, RawMatl, MatlQty, MatlUnits)
    
                    Else
                        Exit Do
    
                    End If
    
                Loop
    
                rs.Bookmark = rsb
                rs.MoveNext()
    
            Loop
    
            rs.Close()
            rs = Nothing
    
            MessageBox.Show("Updated: " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
    
        End Sub

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width