loosing leading zero in excel when opening a csv

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?
Aidy_BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WJReidCommented:
Hi,

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

Bill
0
StuFox100Commented:
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
Louis01Commented:
If the number has quotes around it, stripping the quotes first should work. (Maybe commas and spaces should be stripped as well)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nedfineCommented:
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
nedfineCommented:
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
Aidy_BAuthor Commented:
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
nedfineCommented:
if you dont want to use it for calculation then you can use '00123' in CSV (if u can edit CSV)
0
Aidy_BAuthor Commented:
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
xaniusCommented:
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
Aidy_BAuthor Commented:
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
xaniusCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aidy_BAuthor Commented:
hi. thanks for that. im going to go and try it now, so watch this space......
0
Aidy_BAuthor Commented:
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
xaniusCommented:
Thanks for the points and the grade!

Xanius
0
Aidy_BAuthor Commented:
no probs. Can u check ur mail as i sent you one.
Aidy
0
cliffzimCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.