-
Sep 21st, 2020, 10:14 AM
#1
Thread Starter
New Member
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
However the result I get in the DGV is as follows;
Thank You
-
Sep 21st, 2020, 11:34 AM
#2
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
-
Sep 22nd, 2020, 03:14 AM
#3
Thread Starter
New Member
Re: Importing CSV Strings as numbers
Originally Posted by techgnome
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?
-
Sep 22nd, 2020, 03:42 AM
#4
Re: Importing CSV Strings as numbers
Originally Posted by neodotnet3
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)
-
Sep 22nd, 2020, 03:46 AM
#5
Re: Importing CSV Strings as numbers
Originally Posted by neodotnet3
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.
-
Sep 22nd, 2020, 03:51 AM
#6
Thread Starter
New Member
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.
-
Sep 22nd, 2020, 03:54 AM
#7
Thread Starter
New Member
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.
-
Sep 22nd, 2020, 04:01 AM
#8
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)
-
Sep 22nd, 2020, 04:38 AM
#9
Thread Starter
New Member
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.
-
Sep 22nd, 2020, 04:41 AM
#10
Re: Importing CSV Strings as numbers
Originally Posted by neodotnet3
Do F### numbers mean anything? Something I can tell .net to ignore.
Cough, cough.
Originally Posted by jmcilhinney
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.
-
Sep 22nd, 2020, 04:50 AM
#11
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)
-
Sep 22nd, 2020, 04:51 AM
#12
Thread Starter
New Member
Re: Importing CSV Strings as numbers
Originally Posted by jmcilhinney
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.
-
Sep 22nd, 2020, 04:54 AM
#13
Re: Importing CSV Strings as numbers
Originally Posted by neodotnet3
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)
-
Sep 22nd, 2020, 06:26 AM
#14
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
-
Sep 22nd, 2020, 07:17 AM
#15
Re: Importing CSV Strings as numbers
Originally Posted by jmcilhinney
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...
Originally Posted by neodotnet3
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.
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:
Sounds wonky, I know, but try it... see if that changes any thing.
-tg
-
Sep 22nd, 2020, 08:38 AM
#16
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.
-
Sep 22nd, 2020, 08:51 AM
#17
Re: Importing CSV Strings as numbers
Originally Posted by neodotnet3
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:
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".
-
Sep 22nd, 2020, 12:17 PM
#18
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
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
|