-
Feb 7th, 2018, 09:48 AM
#1
Thread Starter
Fanatic Member
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:
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.
Much thanks!
-
Feb 7th, 2018, 10:16 AM
#2
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.
-
Feb 7th, 2018, 10:48 AM
#3
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.
-
Feb 7th, 2018, 11:07 AM
#4
Thread Starter
Fanatic Member
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?
-
Feb 7th, 2018, 11:20 AM
#5
Thread Starter
Fanatic Member
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:
The strange thing is, why am I getting blanks, because there is data on all the columns for
Code:
(rs.Fields(0).Value)
-
Feb 7th, 2018, 11:23 AM
#6
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
-
Feb 7th, 2018, 11:25 AM
#7
Re: String Values being turned into nothing or date
Originally Posted by ssabc
I tried this:
Code:
If IsDBNull(rs.Fields(0).Value) = False Then Level = "= '" & (rs.Fields(0).Value) & "'"
Which produces this:
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
-
Feb 7th, 2018, 11:43 AM
#8
Thread Starter
Fanatic Member
-
Feb 7th, 2018, 12:04 PM
#9
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:
- You start with a CSV file I'll call "original".
- Your program opens "original", processes it, and produces a new CSV file "new".
- 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.
-
Feb 7th, 2018, 12:08 PM
#10
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.
-
Feb 7th, 2018, 12:38 PM
#11
Thread Starter
Fanatic Member
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.
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|