[RESOLVED] Type Conversion Issue

I have a CSV that I am reading into a datatable using ADO.NET. One of the columns has a problem. The value in the column happens to be 10-05-20. That looks like a date, but it is not. It's just a string.

ADO interpreted the value as a date, so when I imported the data with SELECT *, it turned that column into a date field, which broke the program that was not expecting a date.

The first thing I tried was explicitly adding the column to the datatable with a datatype of String. That was the only column I was adding, so I wasn't sure it would work, but it did...sort of. ADO.NET correctly added all the other columns, and put the data into the column in question, but stuck it in there as a date...that had been turned into a string. That broke the program, as well.

So, what I'm looking for is a way to select everything from a CSV where one field may look like a date, but is actually not at all a date, and if it is converted into a date, then bad things happen.

I tried SELECT CAST(<my field here> AS varchar)

and SELECT CONVERT (varchar, <my field here>)

The first resulted in an error I didn't understand (IErrorInfo.GetDescription failed with E_FAIL(0x80004005).), while the second said that CONVERT was not a recognized function.

How can I stop ADO from seeing that text string as a date?

I tested this and the datatable column remains a String type.

Code:

---

        Dim folder = "c:\ajunk2019\"
        Dim CnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";"
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn("Column1", GetType(String)))
        dt.Columns.Add(New DataColumn("Column2", GetType(String)))
        Using Adp As New OleDbDataAdapter("select * from [test2.csv]", CnStr)
            Adp.Fill(dt)
        End Using

        Me.DataGridView1.DataSource = dt

        MessageBox.Show(dt.Columns(1).DataType.ToString)

---

According to some pages, your Error indicates the usage of a reserved keyword in your SQL-Statement, the premium suspect being a Column- or Table-Name.
Have you tried writing out the SELECT-Statement using [] to escape Keywords?

As a second approach: Have you tried CONCAT-ing your problematic field with Quotes?
SELECT '\'' || YourField || '\'' As YourFieldAsString From CSV-Table
No idea how you escape quotes in T-SQL

Silly but...try formatting the CSV column to text.

@ Tyson: How does one format a text file in any fashion? While this is a file containing Comma Separated Values (CSV), it is still just a simple text file. I believe that any formatting is done by ADO.

@Wes: I tried that, as well. The column does remain string, but what happens is that ADO takes the field, turns it into a date, then turns the date into a string and puts it in the column. In other words, it does the conversion to a date before it gets to the datatable, so what ends up in the datatable is wrong.

@Zvoni: The field name already has to be in square brackets, even before key words are considered. It annoys the heck out of me, but the folks who supply this data made virtually EVERY column name (field name) a multi word string separated by spaces. That just makes that stuff really awkward, and there's no good reason for it. Still, I have to live with it.

I have brought in the column directly, and that works, so when I get an error with CAST, then all that is different is the CAST. For example, I tried "SELECT [CWT Value]" and that worked, but "SELECT CAST([CWT VALUE] AS varchar)" gives that error, so I'm guessing that CAST itself is the issue.

I have not tried concating with quotes, and that's worth a try.

If you enclose 10-5-20 in quotes, "10-5-20", it solves the problem. That's probably what Ty was talking about.

Quote:

---

Originally Posted by Shaggy Hiker;[URL="tel:5570939"

5570939[/URL]]@ Tyson: How does one format a text file in any fashion? While this is a file containing Comma Separated Values (CSV), it is still just a simple text file. I believe that any formatting is done by ADO.

@Wes: I tried that, as well. The column does remain string, but what happens is that ADO takes the field, turns it into a date, then turns the date into a string and puts it in the column. In other words, it does the conversion to a date before it gets to the datatable, so what ends up in the datatable is wrong.

@Zvoni: The field name already has to be in square brackets, even before key words are considered. It annoys the heck out of me, but the folks who supply this data made virtually EVERY column name (field name) a multi word string separated by spaces. That just makes that stuff really awkward, and there's no good reason for it. Still, I have to live with it.

I have brought in the column directly, and that works, so when I get an error with CAST, then all that is different is the CAST. For example, I tried "SELECT [CWT Value]" and that worked, but "SELECT CAST([CWT VALUE] AS varchar)" gives that error, so I'm guessing that CAST itself is the issue.

I have not tried concating with quotes, and that's worth a try.

---

errrr……. does the CSV-Driver (Ole-provider?) understand CAST?
Remember REPLACE with Jet-Databases (Access): Replace works in Access itself, but not from „outside“

Probably it does not.

I went looking at type inferrence. By default, the first 8 rows are used. This is problematic in many cases, but kind of a disaster in this case, since the format is unpredictable, usually empty, and it can look like a date.

I'm not sure that ADO can be used for importing this file because of that limitation. I tried IMEX=1, but that gave me some different error about something not being available. I don't have the option of installing something on the computer this will run on, so anything that isn't installed is going to stay uninstalled.

I then tried the MS text...uhh, I forget what it was I tried, and I'm out getting my car worked on, at the moment. Anyways, it didn't work for some reason I didn't have time to look into. It didn't seem promising anyways, because it's still using the OleDB provider, and that probably means the same type inference issue.

@Wes: I believe that wrapping the thing in quotes, whether single or double, would work, but it's also out of the question. The file is coming from a service. I get what I get, and what I get isn't good...in SO many ways, but the other issues aren't worth talking about. One of the things about this field is that I have no idea whether the format that I am seeing is being enforced by anything, or not. My 10-05-07 could be 100507 next time. It had better not be 1057, ever, because that would indicate that the people gathering the data have no idea what they are doing...but they don't, so it could happen.

What could definitely happen would be something like 108989. The 10 will remain the same (for me), but the other four digits can be nearly anything. The way type inference works is that the first 8 rows are used to figure out a type. If the provider figures out a type of Date, as it did in this case, then it gets 108989, which isn't a date, it will import Null for that, which is almost worse.

I may just have to use a different mechanism for working with this file and drop the ADO route.

Quote:

---

Originally Posted by Shaggy Hiker

@ Tyson: How does one format a text file in any fashion? While this is a file containing Comma Separated Values (CSV), it is still just a simple text file. I believe that any formatting is done by ADO.

@Wes: I tried that, as well. The column does remain string, but what happens is that ADO takes the field, turns it into a date, then turns the date into a string and puts it in the column. In other words, it does the conversion to a date before it gets to the datatable, so what ends up in the datatable is wrong.

@Zvoni: The field name already has to be in square brackets, even before key words are considered. It annoys the heck out of me, but the folks who supply this data made virtually EVERY column name (field name) a multi word string separated by spaces. That just makes that stuff really awkward, and there's no good reason for it. Still, I have to live with it.

I have brought in the column directly, and that works, so when I get an error with CAST, then all that is different is the CAST. For example, I tried "SELECT [CWT Value]" and that worked, but "SELECT CAST([CWT VALUE] AS varchar)" gives that error, so I'm guessing that CAST itself is the issue.

I have not tried concating with quotes, and that's worth a try.

---

Sorry...when I see CSV I think Excel.

Yeah, now that Excel opens CSV so seamlessly, it makes sense. Excel and LibreOffice Calc both open this file without issue and don't do the type conversion on the field. The only way I have seen to try to get the ADO data provider to NOT do type inference is by setting IMEX=1 in the connection string, but, as I noted, that gave me an error about something not being installed, nor can it be installed.

Well, there is the option of opening the file sequentially.
skip the first line,
read everything else line by line, split each line by delimiter (comma?).
you‘d have full control of the datatypes

Quote:

---

@Wes: I believe that wrapping the thing in quotes, whether single or double, would work, but it's also out of the question. The file is coming from a service. I get what I get, and what I get isn't good...in SO many ways, but the other issues aren't worth talking about. One of the things about this field is that I have no idea whether the format that I am seeing is being enforced by anything, or not. My 10-05-07 could be 100507 next time. It had better not be 1057, ever, because that would indicate that the people gathering the data have no idea what they are doing...but they don't, so it could happen.

---

Yeah, if you don't have control of what you receive then your stuck. If the field format can change then reading the file and doing data validation before adding it to the datatable seems unavoidable. But I guess that depends on what you do with it after you import the data. Good luck.

It's looking like I'll have to use TextFieldParser for this because of the quirks of ADO and CSV files (or at least the OleDB data provider) and the quirks of this field. I'll leave this open for a time, to see whether anybody has any other suggestions.

I am for what Zvoni suggested: write own parser and just split the string. Then handle each field as you want, not what some library will return to you.

I'd say that I have to. I'm kind of surprised at this. You can find lots of examples of people using ADO.NET to read in CSV files, but that type inference can be quite nasty. One of the nastier aspects of it is that, if it decides that records are type X, then anything that can't be coerced into an X is set to Null.

I'm using the ADO.NET approach on two other tables. Now I'm wondering whether or not they were really doing the right thing, or if I ended up with a bunch of inaccurate Nulls because the type inference was wrong. It sure seems like it should be possible to turn off type inference, it just doesn't seem to be readily possible.

Quote:

---

I'm using the ADO.NET approach on two other tables. Now I'm wondering whether or not they were really doing the right thing, or if I ended up with a bunch of inaccurate Nulls because the type inference was wrong. It sure seems like it should be possible to turn off type inference, it just doesn't seem to be readily possible.

---

Even if you could, since you don't have control of what is sent you, wouldn't it be safer to do data validation on the import anyway. If not, who knows what your importing.

Just found something: https://docs.microsoft.com/en-us/dot...Text_Encoding_
Args 5 and 7 look interesting

What IS that? A preview for the Machine Learning API? I sure don't think I want to touch that.

Another option, if I were able to do this in .NET 6, would be to load the CSV into a JSON object. I encountered a simple means to do that, probably in the Text.JSON namespace, but since I can't go to .NET 6, that's also out of the question.

/off: VS 2019 supports .NET 6 from today.

If you have to import from CSV, why to convert to another format like JSON instead of simply importing everything as you want? I've never used "library" for something that is few lines of code and I can manage in my code all strange cases instead of relying to some library.

Some pseudo-code:

Code:

---

public class ImportedRecordFromCSV
  public property id as long
  public property name as string
  public property datecreated as datetime
  public property blah as string
  public property undocumentedfield as string
  public property somestrangefield as string
end class

....
dim linenum as long = 0
for each line in File.ReadLines(filename)
  linenum++    ' remember? this is pseudo code :)

  if linenum = 1 then  continue for  ' ignore first line (headers)
 
  dim fields = line.Split(",")  ' set to whatever split char you need
  if fields.length <> some number then
    log.error($"Line {linenum} err: num fields wrong {fields.length}")
    continue
  endif
  dim record = new ImportedRecordFromCSV() {
    .id = ParseLongField(field(0)),
    .name = field(1),
    .datecreated = ParseDateTimeField(field(2)),
    .blah = field(3),
    .undocumentedfield = field(4),
    .somestrangefield = ParseSomeStrangeField(field(5)),
  }
next

---

Saving to db using Dapper (I've shown already somewhere examples with this library in other threads) is also pretty simple:

Code:

---

public sub Create(record as ImportedRecordFromCSV)
  using conn = GetDbConn()
    dim query =
"INSERT INTO ImportInfo
(id, name, datecreated, blah, undocumentedfield, somestrangefield)
VALUES (@id, @name, @datecreated, @blah, @undocumentedfield, @somestrangefield)"
    conn.Execute(query, record)
  end using
end sub

---

Above pseudocode will become a bit longer when implemented fully in working app. The only dependency I will use is Dapper library but it is in active support and works with .NET Framework and .NET Core/5/6+. The code will work not only on Windows but also on Linux if you ever want to move it to different server or "containerize".

So why you're (still) thinking of other ways with libraries instead of just try simple read line by line and split that CSV? Remember KISS :D ?

That isn't as simple as what I had working with ADO.NET. I did a SELECT * and used a dataadapter to fill a datatable. I then used the dataadapter to write to the DB in a transaction. It is a pretty tight, simple, solution...until I found out about the type inference, and the issue it caused with that one field. No library at all, unless you count the OleDB dataprovider, but if you counted that then you'd have to start counting all kinds of weird things.

Anyways, the TextFieldParser is simpler than what you showed, and it worked just fine. I still had to do a bit of conversion, since I was importing everything as a string, and I wanted an empty string to be Null for some fields and an empty string for other fields.

Along the way, I found that this data is weirder, and messier, than I had thought. For example, there is a length field. This is a numeric field, as lengths usually are. Lengths aren't always available, so Null makes sense, or 0 would be fine, but somebody had 'N/A' in the field, which means that the data input program must have allowed a string to be stuck into a field that has no business holding a string. That was a poor choice, though easy to deal with: Anything that wasn't a number became Null.

Still, the point that surprised me in all of this was not the type inference in the data provider. It was the very difficulty of turning it off. The type inference mechanism is based on the first 8 rows. It should be clear to anybody that enforcing a rule on an entire field based on the first eight entries into the field is inherently risky. It works great in most cases, but there are some cases where it will be wrong. The obvious solution would be some simple means to turn it off. What surprised me was that no such switch exists. IMEX=1 is the closest, but it only works in some cases.

did you try it with a Schema.ini ?

here a small sample, I do have to use a Txt since in Germany my Delimiter for .CSV is a semicolon

this what the Text file looks like

Code:

---

10-05-20, 00:00:00, -135.37, 3
11-05-20, 06:00:00, -147.09, 3
03-05-20, 12:00:00, -154.80, 4
05-05-20, 18:00:00, 175.46, 6
03-10-20, 00:00:00, -170.42, 5
02-01-20, 06:00:00, -178.72, 9
06-08-20, 12:00:00, -164.90, 6
06-01-20, 18:00:00, 169.76, 10

---

I use OLEDB, you do have to create a Foldername

Code:

---


Imports System.IO

Public Class Form3

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim dt As Data.DataTable = Nothing
        Dim FileName As String = "ShaggyTXT.txt"
        Dim sCon As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:\CSVTest\Shaggy;Extended Properties='Text;HDR=No;Schema=schema.ini;';"
        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()

        'write your Shema.ini
        Dim SchemaFile As String = "D:\CSVTest\Shaggy\Schema.ini"
        If File.Exists(SchemaFile) Then File.Delete(SchemaFile)

        Using SchemaStream As FileStream = New FileStream(SchemaFile, FileMode.CreateNew)
            Using writer As StreamWriter = New StreamWriter(SchemaStream)
                writer.AutoFlush = True
                writer.WriteLine("[" & System.IO.Path.GetFileName(FileName) & "]")
                writer.WriteLine("ColNameHeader=False")
                writer.WriteLine("Format=Delimited(,)")
                writer.WriteLine("DecimalSymbol=.")
                writer.WriteLine("CharacterSet=ANSI")
                writer.WriteLine("Col1=myString Text Width 12")
                writer.WriteLine("Col2=myString2 Text Width 10")
                writer.WriteLine("Col3=myDec Currency")
                writer.WriteLine("Col4=myInteger Integer")
            End Using
            SchemaStream.Close()
        End Using

        Dim sSql As String = "SELECT myString, myString2, myDec FROM " & FileName


        Dim Cmd As New OleDb.OleDbCommand(sSql, Cn)
        Dim Dr As OleDb.OleDbDataReader
        Dr = Cmd.ExecuteReader
        dt = New Data.DataTable
        dt.Load(Dr)
        Me.DataGridView1.DataSource = dt
    End Sub
End Class

---

and a Image
Attachment 185120

I don't have the option of altering the file I'm getting. It is what it is. There is clearly something different between the file you are creating and the file that I have to work with, because yours is imported with the strings being seen as strings. I would guess that there is some kind of encoding difference, or wrapping the fields in some fashion, but whatever the difference is, it doesn't really matter, because the file I'm getting doesn't have that, and I can't reasonably change that.

I'm not sure what you mean? you create a Schema.ini to deal with the Data you get
not to alter the File.

if I change the Schema.Ini to handel the first column as a Date

Code:

---

Using SchemaStream As FileStream = New FileStream(SchemaFile, FileMode.CreateNew)
            Using writer As StreamWriter = New StreamWriter(SchemaStream)
                writer.AutoFlush = True
                writer.WriteLine("[" & System.IO.Path.GetFileName(FileName) & "]")
                writer.WriteLine("ColNameHeader=False")
                writer.WriteLine("Format=Delimited(,)")
                writer.WriteLine("DecimalSymbol=.")
                writer.WriteLine("CharacterSet=ANSI")
                'writer.WriteLine("Col1=myString Text Width 12")
              writer.WriteLine("Col1=myString Date")
                writer.WriteLine("Col2=myString2 Text Width 10")
                writer.WriteLine("Col3=myDec Currency")
                writer.WriteLine("Col4=myInteger Integer")
            End Using
            SchemaStream.Close()
        End Using

---

I did change a value in the Textfile to 44-13-20 which isn't a date
the results will be valid Dates or NULL/Nothing
see Image
Attachment 185123

Quote:

---

Originally Posted by ChrisE

did you try it with a Schema.ini ?

here a small sample, I do have to use a Txt since in Germany my Delimiter for .CSV is a semicolon

this what the Text file looks like

Code:

---

10-05-20, 00:00:00, -135.37, 3
11-05-20, 06:00:00, -147.09, 3
03-05-20, 12:00:00, -154.80, 4
05-05-20, 18:00:00, 175.46, 6
03-10-20, 00:00:00, -170.42, 5
02-01-20, 06:00:00, -178.72, 9
06-08-20, 12:00:00, -164.90, 6
06-01-20, 18:00:00, 169.76, 10

---

I use OLEDB, you do have to create a Foldername

Code:

---


Imports System.IO

Public Class Form3

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim dt As Data.DataTable = Nothing
        Dim FileName As String = "ShaggyTXT.txt"
        Dim sCon As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:\CSVTest\Shaggy;Extended Properties='Text;HDR=No;Schema=schema.ini;';"
        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()

        'write your Shema.ini
        Dim SchemaFile As String = "D:\CSVTest\Shaggy\Schema.ini"
        If File.Exists(SchemaFile) Then File.Delete(SchemaFile)

        Using SchemaStream As FileStream = New FileStream(SchemaFile, FileMode.CreateNew)
            Using writer As StreamWriter = New StreamWriter(SchemaStream)
                writer.AutoFlush = True
                writer.WriteLine("[" & System.IO.Path.GetFileName(FileName) & "]")
                writer.WriteLine("ColNameHeader=False")
                writer.WriteLine("Format=Delimited(,)")
                writer.WriteLine("DecimalSymbol=.")
                writer.WriteLine("CharacterSet=ANSI")
                writer.WriteLine("Col1=myString Text Width 12")
                writer.WriteLine("Col2=myString2 Text Width 10")
                writer.WriteLine("Col3=myDec Currency")
                writer.WriteLine("Col4=myInteger Integer")
            End Using
            SchemaStream.Close()
        End Using

        Dim sSql As String = "SELECT myString, myString2, myDec FROM " & FileName


        Dim Cmd As New OleDb.OleDbCommand(sSql, Cn)
        Dim Dr As OleDb.OleDbDataReader
        Dr = Cmd.ExecuteReader
        dt = New Data.DataTable
        dt.Load(Dr)
        Me.DataGridView1.DataSource = dt
    End Sub
End Class

---

and a Image
Attachment 185120

---

This is very interesting. Never knew this was even possible. It does look like a good answer (if it works lol), if SH wants to use Oledb.

EDIT: I tested your code and it worked perfectly. Very cool solution.

Quote:

---

Originally Posted by wes4dbt

This is very interesting. Never knew this was even possible. It does look like a good answer (if it works lol), if SH wants to use Oledb.

EDIT: I tested your code and it worked perfectly. Very cool solution.

---

Thanks wes

I did take a look at my old notes on ADO with CSV because Shaggy wrote....

Quote:

---

ADO interpreted the value as a date, so when I imported the data with SELECT *, it turned that column into a date field, which broke the program that was not expecting a date.

---

the ADO Jet Driver does scan the first 25 rows and seems to decide this column.. is a Date! :eek:
I think it will be no diffrent with .NET

I would try putting Maxscanrows=0
into the connectionstring but that means the complete CSV will be checked not just the first 25 rows

Code:

---

;Extended Properties='text;MaxScanRows=0;IMEX=0'

---

Ah, I misunderstood what you were doing. That does look like a good solution. I'll make use of it for the other two files I have.