Link to home
Start Free TrialLog in
Avatar of redmission
redmissionFlag for United States of America

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.
Avatar of Jorge Paulino
Jorge Paulino
Flag of Portugal image

Are you showing/managing that information as string? If you use the integer it cuts the leading zeroes.
ASKER CERTIFIED SOLUTION
Avatar of drekow
drekow

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of redmission

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.OLEDB.4.0;Data 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.DataSource = dsMail.Tables("Mailshop").DefaultView


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.
Avatar of drekow
drekow

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