?
Solved

Keep Leading Zeroes In CSV File When Importing To VB.NET

Posted on 2007-07-25
6
Medium Priority
?
2,720 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:redmission
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 19569265
Are you showing/managing that information as string? If you use the integer it cuts the leading zeroes.
0
 
LVL 2

Accepted Solution

by:
drekow earned 1000 total points
ID: 19569731
If you are managing using a string you can use padleft to put the leading zero(s) in place.

strSomeName.Padleft(Number of spaces,Char to pad)
strSomeName.Padleft(3,"0")   This will insert leading zero(s) if the number in the string is less than 3 characters long   12 would be 012 etc....

Dale
0
 

Author Comment

by:redmission
ID: 19569773
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Expert Comment

by:drekow
ID: 19570241
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
0
 

Author Comment

by:redmission
ID: 19573919
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.
0
 
LVL 2

Expert Comment

by:drekow
ID: 19574871
Glad I could be of help. Sorry about bailing yesterday. My work is like having a child.

Dale
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question