Results 1 to 18 of 18

Thread: Importing CSV Strings as numbers

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Importing CSV Strings as numbers

    Hello, I'm having trouble reading in certain CSV's. When I have an alphanumeric field, It's been read in as a number. Anyone know why and how to fix?

    Code:
                DialogName = IO.Path.GetFileNameWithoutExtension(DialogFile)
                Dim DialogFileName As String = IO.Path.GetFileName(DialogFile)
                Dim Page As TabPage = New TabPage() With {.Text = DialogName, .Name = $"{DialogName}"}
                Dim dgv As New DataGridView
                Dim txtData As DataTable = New DataTable()
                'Dim txtCon As OleDb.OleDbConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DialogDirect & "';Extended Properties='Text;HDR=Yes;FMT=Delimited(,)'")
                Dim txtCon As OleDb.OleDbConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DialogDirect & "';Extended Properties='Text;HDR=Yes;FMT=Delimited'")
                Dim txtCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [" & DialogFileName & "]", txtCon)
                Dim txtAdapt As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(txtCmd)
                txtAdapt.Fill(txtData)
                txtCon.Close()
                With dgv
                    .Name = $"dgv{DialogName}"
                    .Parent = Page
                    .Dock = DockStyle.Fill
                    .DataSource = txtData
                    .AllowUserToAddRows = False
                    .AllowUserToOrderColumns = False
                End With
                txtData.Dispose()
                Page.Controls.Add(dgv)
                BulkTabControl.TabPages.Add(Page)
                AddHandler dgv.MouseUp, AddressOf BulkGrid_MouseUp
                AddHandler dgv.UserDeletingRow, AddressOf BulkGrid_UserDeletingRow
    The above code is used to read in the this sample csv
    Name:  csv.PNG
Views: 497
Size:  4.5 KB

    However the result I get in the DGV is as follows;
    Name:  dgv result.jpg
Views: 472
Size:  7.3 KB

    Thank You

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Importing CSV Strings as numbers

    Look at the CSV directly... what's in it? Don't guess what's in it... know what's in it.

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

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by techgnome View Post
    Look at the CSV directly... what's in it? Don't guess what's in it... know what's in it.

    -tg
    Thanks, not sure if you're trolling or being helpful. How do I look at the CSV directly? Is there another method for getting CSV to DGV?

  4. #4
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by neodotnet3 View Post
    How do I look at the CSV directly? Is there another method for getting CSV to DGV?
    use the notepad to open the CSV and see how it is structured.

    With a streamreader, with TextFieldParser..

    in your CSV file do you have headers ? if so, read the first line (the headers), separate the headers and create the columns in the datatable (or directly in the DGV) with these headers. Then read every line, separate data and put the data in a new row in each columns in the datatable or the DGV.

    I joint a test program on reading CSV to DGV I have done for a project of mine
    test_ressource_csv classe.zip
    Last edited by Delaney; Sep 22nd, 2020 at 03:52 AM.
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by neodotnet3 View Post
    Thanks, not sure if you're trolling or being helpful. How do I look at the CSV directly? Is there another method for getting CSV to DGV?
    He's suggesting that you open the CSV file directly, but I think he missed that one of your screenshots is the file open in Excel. I'd still recommend opening it in Notepad or the like though. Excel does some weird things sometimes.

    Jet can do some weird things at times too, trying to make a guess at your intentions. Maybe the F is being interpreted forcing the values to type float. What is the actual type of those values? If it's Single or Double rather than Integer then I'd say that's definitely what's happening. If you want to stick with ADO.NET then you may need to use a schema.ini file.

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Re: Importing CSV Strings as numbers

    Got it, thanks.

    I did initially check what the structure looked like in notepad. I have also forced double quotes around the F numbers which produced the same result although practically speaking this would not be possible in production.


    Name:  test notepad.png
Views: 313
Size:  6.9 KB

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Re: Importing CSV Strings as numbers

    Also the F numbers need to be strings. The structure of the CSV will change with different number of columns and datatypes.

    I could try reading line by line, to see if that fixes things.

  8. #8
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Importing CSV Strings as numbers

    In you datatable, when your create the column, you precise what type of data you want so you can force it to be a string

    Code:
    with table.columns
    .add("header",gettype(string))
    end with
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Re: Importing CSV Strings as numbers

    I'm thinking, create a temp datatable to initially fill with the CSV data, then cycle through the headers (as they are not known in advance) and build the structure of my final datatable to then fill once again.

    Code:
                Dim txtTmp As DataTable = New DataTable()
                txtAdapt.Fill(txtTmp)
                For Each column In txtTmp.Columns
                    txtData.Columns.Add(column.ToString, GetType(String))
                Next
                txtAdapt.Fill(txtData)
    However this doesn't actually work, I'm getting the same result as before.

    Also this only happens with numbers that are prefixed with "F". Other alphanumeric combinations do not produce this error. Do F### numbers mean anything? Something I can tell .net to ignore.

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by neodotnet3 View Post
    Do F### numbers mean anything? Something I can tell .net to ignore.
    Cough, cough.
    Quote Originally Posted by jmcilhinney View Post
    Maybe the F is being interpreted forcing the values to type float. What is the actual type of those values? If it's Single or Double rather than Integer then I'd say that's definitely what's happening. If you want to stick with ADO.NET then you may need to use a schema.ini file.

  11. #11
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Importing CSV Strings as numbers

    what is that???
    Code:
    For Each column In txtTmp.Columns
    txtData.Columns.Add(column.ToString, GetType(String))
    Next
    the synthax is :
    Code:
    .Add (columnName As String, type As Type)
    so as column name, you take the column and convert it to string whatever it means?
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    12

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by jmcilhinney View Post
    Cough, cough.
    I know you said that, but not sure what to do with it . Is there not an option to just read the data as is? What is the alternative to ADO.NET?

    Also when I add decimals to the test file these are shown in the DGV. If the first value it comes across is an F number all other Alphanumeric values are removed.

  13. #13
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by neodotnet3 View Post
    I know you said that, but not sure what to do with it . Is there not an option to just read the data as is? What is the alternative to ADO.NET?.
    I gave you a example file in post #4, you just have to adapt it (as I have already my headers in my file)
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Importing CSV Strings as numbers

    What I find interesting is that I never knew that ADO would interpret a string in any way, and even JMC seems to be just speculating. It's not the first time that I have encountered MS doing something surprising. For example, Excel will open text files, but for decades it wouldn't open a text file where the first two characters were "ID", and the resulting error message was pretty cryptic. To find that ADO is doing a bit of interpretation is kind of interesting. I would guess that it has to do with interpreting text.
    My usual boring signature: Nothing

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by jmcilhinney View Post
    He's suggesting that you open the CSV file directly, but I think he missed that one of your screenshots is the file open in Excel. I'd still recommend opening it in Notepad or the like though. Excel does some weird things sometimes.
    I didn't miss it, I just also know Excel does weird things... which is why I wondered what the raw CSV looked like...

    Quote Originally Posted by neodotnet3 View Post
    Got it, thanks.

    I did initially check what the structure looked like in notepad. I have also forced double quotes around the F numbers which produced the same result although practically speaking this would not be possible in production.

    Name:  test notepad.png
Views: 313
Size:  6.9 KB
    And it looks like this... OK great... Just for fun... And I know that you may not be able to do this in production, but, just for fun, try adding a SINGLE QUOTE in front of the values:
    Code:
    'F123456
    Sounds wonky, I know, but try it... see if that changes any thing.

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

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

    Re: Importing CSV Strings as numbers

    hi,

    in this String you have 'DialogDirect'
    Code:
    Dim txtCon As OleDb.OleDbConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DialogDirect & "';Extended Properties='Text;HDR=Yes;FMT=Delimited'")
    where does that come from?
    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.

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Importing CSV Strings as numbers

    Quote Originally Posted by neodotnet3 View Post
    I know that you may not be able to do this in production, but, just for fun, try adding a SINGLE QUOTE in front of the values:
    Code:
    'F123456
    Sounds wonky, I know, but try it... see if that changes any thing.
    I can't remember exactly why - it may have been for phone numbers - but I tried that once and it didn't work. Adding a single quote within Excel does force data to be interpreted as text in XLS files (not sure about XLSX) but a literal single-quote in a text file is treated like any other character, so you end up with text starting with a single-quote.
    Last edited by jmcilhinney; Sep 22nd, 2020 at 07:53 PM. Reason: Added missing "didn't".

  18. #18
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Importing CSV Strings as numbers

    Yeah,I had issues a few years back is Student IDs where ' solved the issue... at this point, I'm inclined to think about dumping ADO and using TextFieldParser at this point... I've had greater success reading in data using TFP than ADO from CSVs
    But I've also needed to have the flexibility to get data from files of varying formats and TFP offored me the greatest flexibility with the least amount of annoyances.

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

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