Solved

loosing leading zero in excel when opening a csv

Posted on 2007-03-28
16
1,707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

739 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