Solved

loosing leading zero in excel when opening a csv

Posted on 2007-03-28
16
1,700 Views
Last Modified: 2012-05-05
Hello all,

I have an issue where the leading zero in the first couple of fields of a single line csv file, just disappears when opened in excel. I have tried formatting changes but nothing brings it back.
Any ideas?
0
Comment
Question by:Aidy_B
  • 6
  • 3
  • 3
  • +4
16 Comments
 
LVL 13

Expert Comment

by:WJReid
ID: 18806542
Hi,

Are the numbers formatted as text when you open the Excel file, i.e. are they left aligned?

Bill
0
 
LVL 12

Expert Comment

by:StuFox100
ID: 18806567
The easiest way to get these in is:
1. Change the file name from CSV to TXT
2. Open Excel
3. Open the TXT file
4. Work through import screens until format screen
5. select all common and make them text.
There you go it is in excel.
Cheers
Stu
0
 
LVL 11

Expert Comment

by:Louis01
ID: 18806595
If the number has quotes around it, stripping the quotes first should work. (Maybe commas and spaces should be stripped as well)
0
 
LVL 8

Expert Comment

by:nedfine
ID: 18806601
hi
just specify all columns as text while importing the CSV.
 In the step 3 of the text import wizard you can specify the column format -default is general. change it to text for all columns.
even if you specify it as text you can use it for calculations.
thank you
0
 
LVL 8

Expert Comment

by:nedfine
ID: 18806612
in step 3 of the text import wizard u can see a "data preview" of all the columns . use shift and click on the last column to select all columns then change the column data format to text and click finish.
0
 

Author Comment

by:Aidy_B
ID: 18807168
ok, bit more info:
the file is opened using excel as opposed to imported. this is part of a macro that rips through an entire folder of csv files using a wildcard loop instead of filenames so without major re-work on the macro, using the import process is not an option.

When opened, excel recognises the different fields without any issues, but the leading zeros on any of them have already gone by the time its opened it.

I am looking for a way to tell excel to treat the fields as text, but whilst still using csv format.

The option of renaming the files to txt is the fall back plan, but due to the macro operations of removing fields and exporting part data out of other fields, and sticking it in other places, the txt option is being left as a last resort, again due to the changes that would be required in the macro operation.

Thank you all for your input so far and i am most grateful for any continued involvement of any of you.

Regards
Aidy.
0
 
LVL 8

Expert Comment

by:nedfine
ID: 18807267
if you dont want to use it for calculation then you can use '00123' in CSV (if u can edit CSV)
0
 

Author Comment

by:Aidy_B
ID: 18808740
unfortunately, the csv's are generated by an external system and collected by ftp prior to being processed by the macro, then fed into our plc setup. the editing of the csv's would need to be automated and 100% at that. this whole setup is ran with no human intervention at all, 24hrs/day.

This does make it rather more awkward to put other steps into the sequence, rather than just making excel see the zero. Any suggestion on achieving this will be most appreciated.

Regards
Aidy.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:xanius
ID: 18809393
Aidy,

It is very hard to fool Excel at this point as excel thinks it knows best :-(

A possible workaround:
If you happen to know how many digits the numbers have you can change the numbers back to text with the format function: i.e. with five digits:

Columns("A:A"):numberformat = "@"

then loop thrugh all cells in the column:
[A2].value = Format([A2].value,"00000")

If wanted I could return with a complete solution.

Cheers
Xanius

0
 

Author Comment

by:Aidy_B
ID: 18812344
the theory had crossed my mind but had steered away in the hope of a really simple solution. However that is starting to look unlikley, i am finding your proposal temting.

The full code used in the macro is shown below:

Private Sub Workbook_Open()
Application.Visible = True
Dim FileName As String
           Const Folder = "C:\process"
               FileName = Dir(Folder & "\*.txt")
            Do While FileName <> ""
                Application.Workbooks.Open Folder & "\" & FileName
                ActiveWorkbook.Sheets(1).Range("C:AH").Delete
                    Range("D1").Select
                    ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,50)"
                    Range("D1").Select
                    Selection.Copy
                    Range("C1").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Range("D1").Select
                    Application.CutCopyMode = False
                    Selection.ClearContents
                ActiveWorkbook.Close True
                FileName = Dir
            Loop
Application.Quit
End Sub


the first 2 columns are the guilty ones (a & b), and both consist of a 10 digit number.

As i have said before, any help is much appreciated.

Regards
Aidy.
0
 
LVL 10

Accepted Solution

by:
xanius earned 500 total points
ID: 18814521
Hi Aidy,

Try the following macro. I've taken your macro and compacted it a bit - it seems like you used the macro recorder to cut of the "C1" field after 50 characters. When hand-coding the macro this can be reduced to a single statement!.

The solution to your problem ist the line

        Range("A:B").NumberFormat = "0000000000"

When you know the width of the field, it can be as easy as that!


Private Sub Workbook_Open()
    Application.Visible = True
    Dim FileName As String
    Const Folder = "C:\process"
    FileName = Dir(Folder & "\*.txt")
    Do While FileName <> ""
        Application.Workbooks.Open Folder & "\" & FileName
        Range("C:AH").Delete
        Range("C1").Value = Left(Range("C1").Value, 50)
        Range("A:B").NumberFormat = "0000000000"
        ActiveWorkbook.Close True
        FileName = Dir
    Loop
    Application.Quit
End Sub

Cheers
Xanius
0
 

Author Comment

by:Aidy_B
ID: 18835331
hi. thanks for that. im going to go and try it now, so watch this space......
0
 

Author Comment

by:Aidy_B
ID: 18835943
spot on. couldnt ask for more if i wanted to. well not for this problem anyway...
Thanks alot, well earned points.
Thank you to everyone else for their sugestions earlier on.

Aidy
0
 
LVL 10

Expert Comment

by:xanius
ID: 18838976
Thanks for the points and the grade!

Xanius
0
 

Author Comment

by:Aidy_B
ID: 18839027
no probs. Can u check ur mail as i sent you one.
Aidy
0
 

Expert Comment

by:cliffzim
ID: 21470902
Here's a solution that worked for me that appeared in http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm.

Putting quotes around numeric fields in your csv file will not preserve leading zeroes because the default formatting is "General".  To suppress the formatting, precede the quoted field with "=", eg ...,="0123",....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now