Results 1 to 26 of 26

Thread: [RESOLVED] Type Conversion Issue

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Resolved [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?
    My usual boring signature: Nothing

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Type Conversion Issue

    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)

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: Type Conversion Issue

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Type Conversion Issue

    Silly but...try formatting the CSV column to text.
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    @ 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.
    My usual boring signature: Nothing

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Type Conversion Issue

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

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: Type Conversion Issue

    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“
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  9. #9
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Type Conversion Issue

    Quote Originally Posted by Shaggy Hiker View Post
    @ 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.
    Please remember next time...elections matter!

  10. #10

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: Type Conversion Issue

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Type Conversion 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.
    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.

  13. #13

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  14. #14
    Fanatic Member
    Join Date
    Jun 2019
    Posts
    557

    Re: Type Conversion Issue

    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.

  15. #15

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Type Conversion Issue

    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.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: Type Conversion Issue

    Just found something: https://docs.microsoft.com/en-us/dot...Text_Encoding_
    Args 5 and 7 look interesting
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  19. #19
    Fanatic Member
    Join Date
    Jun 2019
    Posts
    557

    Re: Type Conversion Issue

    /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 ?

  20. #20

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Type Conversion Issue

    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.
    My usual boring signature: Nothing

  21. #21
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

    Re: [RESOLVED] Type Conversion Issue

    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
    Name:  ShaggyCsv.jpg
Views: 344
Size:  29.7 KB
    Last edited by ChrisE; Jun 16th, 2022 at 12:35 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.

  22. #22

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] Type Conversion Issue

    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.
    My usual boring signature: Nothing

  23. #23
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

    Re: [RESOLVED] Type Conversion Issue

    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
    Name:  Shaggy2.jpg
Views: 254
Size:  34.7 KB
    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.

  24. #24
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: [RESOLVED] Type Conversion Issue

    Quote Originally Posted by ChrisE View Post
    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
    Name:  ShaggyCsv.jpg
Views: 344
Size:  29.7 KB
    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.
    Last edited by wes4dbt; Jun 16th, 2022 at 12:58 PM.

  25. #25
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

    Re: [RESOLVED] Type Conversion Issue

    Quote Originally Posted by wes4dbt View Post
    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....
    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!
    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'
    Last edited by ChrisE; Jun 16th, 2022 at 01:41 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.

  26. #26

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] Type Conversion Issue

    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.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width