redmission
asked on
Keep Leading Zeroes In CSV File When Importing To VB.NET
I'm importing a CSV file into VB.NET 2005. I'm storing the CSV file in a dataset, and I want to manipulate that...NOT the original CSV file. This dataset is then being displayed in a DataGridView or a Messagebox.
The CSV file contains a 3-character field that holds numbers with leading zeros. Example: 027, 001, 033. These leading zeros ARE showing when I view the CSV file in Notepad.
The problem is that when I display these values in a Messagebox or DataGridView, it appears that the leading zeros have been dropped. How can I keep these leading zeros when I import the CSV to a dataset for further manipulation?
PS: I AM NOT using an Excel spreadsheet, and I am not manipulating the CSV file directly...just the dataset that stores info. from the CSV. The field in question is called "loc" and it should be 3 characters long, with leading zeros. Example: 027, 033, 001 They are stored correctly in the actual CSV file.
The CSV file contains a 3-character field that holds numbers with leading zeros. Example: 027, 001, 033. These leading zeros ARE showing when I view the CSV file in Notepad.
The problem is that when I display these values in a Messagebox or DataGridView, it appears that the leading zeros have been dropped. How can I keep these leading zeros when I import the CSV to a dataset for further manipulation?
PS: I AM NOT using an Excel spreadsheet, and I am not manipulating the CSV file directly...just the dataset that stores info. from the CSV. The field in question is called "loc" and it should be 3 characters long, with leading zeros. Example: 027, 033, 001 They are stored correctly in the actual CSV file.
Are you showing/managing that information as string? If you use the integer it cuts the leading zeroes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought it would all be text since it's a CSV file. Here's a sample of a record from the CSV:
id,loc,fullname,address
101,027,Frances Gavin,3 Fencourt Rd
101,027,Geo Seabrook,7 Fencourt Rd
The "loc" field is the one that won't display with the leading zero. I don't care about the first field (the "id" field) not having leading zeroes, it's the "loc" field that I want with the zeroes. Here's my VB...This pulls all the CSV info and puts it in a datagridview...but won't show leading zeroes:
Dim strConn As String = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" & _
strAddressesFilePath & ";Extended Properties=Text;"
Dim objConn As New OleDbConnection(strConn)
objConn.Open()
Dim cmd As New OleDbCommand("SELECT * FROM [" & strAddressesFile & "]", objConn)
Dim objAdapter As New OleDbDataAdapter()
objAdapter.SelectCommand = cmd
dsMail = New DataSet
objAdapter.Fill(dsMail, "Mailshop")
Me.DataGridView1.DataSourc e = dsMail.Tables("Mailshop"). DefaultVie w
Is there a way to manipulat the dataset for that column so that it formats it correctly, or is there something wrong in the CSV file? Help...this is driving me insane!!! :) Thanks so much.
id,loc,fullname,address
101,027,Frances Gavin,3 Fencourt Rd
101,027,Geo Seabrook,7 Fencourt Rd
The "loc" field is the one that won't display with the leading zero. I don't care about the first field (the "id" field) not having leading zeroes, it's the "loc" field that I want with the zeroes. Here's my VB...This pulls all the CSV info and puts it in a datagridview...but won't show leading zeroes:
Dim strConn As String = "Provider=Microsoft.Jet.OL
strAddressesFilePath & ";Extended Properties=Text;"
Dim objConn As New OleDbConnection(strConn)
objConn.Open()
Dim cmd As New OleDbCommand("SELECT * FROM [" & strAddressesFile & "]", objConn)
Dim objAdapter As New OleDbDataAdapter()
objAdapter.SelectCommand = cmd
dsMail = New DataSet
objAdapter.Fill(dsMail, "Mailshop")
Me.DataGridView1.DataSourc
Is there a way to manipulat the dataset for that column so that it formats it correctly, or is there something wrong in the CSV file? Help...this is driving me insane!!! :) Thanks so much.
Take a look at this. Work is calling me right now so I will be out of pocket for a bit.
http://www.exforsys.com/tutorials/vb.net-2005/editing-data-with-ado-.net.html
Dale
http://www.exforsys.com/tutorials/vb.net-2005/editing-data-with-ado-.net.html
Dale
ASKER
drekow: Previously, I had tried strTheString = Format(strTheString, "000"), and that didn't work! But your solution of strTheString.PadLeft(3, "000") worked PERFECTLY! Thanks so much! Now I can continue with my work.
Glad I could be of help. Sorry about bailing yesterday. My work is like having a child.
Dale
Dale