Solved

loosing leading zero in excel when opening a csv

Posted on 2007-03-28
16
1,706 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

713 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