Is it possible to retain leading zeroes in a text field when saving an Excel csv file ?

Hi All,

I have an Excel 2010 worksheet which I am saving as a .csv file. One of the columns holds telephone numbers as text. However, when I save the file, Excel converts the column to numbers and removes all leading zeroes.

I can't save it as a .txt file as the .csv file is used as input to another software package (WiseSoft Bulk Active Directory Update tool). I've tried prefixing each telephone number with a single quote ' but it is ignored and leading zeroes are removed as before.

Are there any tricks I can apply to get around this problem.

Many thanks
Toco
TocogroupAsked:
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.

MAS (MVE)EE Solution Guide - Technical Dept HeadCommented:
upload file with at-least 2-3 entries in it
0
SteveCommented:
You can add a double apostrophe to the start of the number and convert the number to text and save it like that:

This may help:
="''" & Text(Number,"0000000")
0
TocogroupAuthor Commented:
Please find .csv file attached. The telephone numbers were saved as text.
ImportCSVfile.csv
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

drfrank319Commented:
This is a known limitation of .csv format. There are several workarounds:

Open a blank Excel spreadsheet, click on cell A1 and import the .csv as text from Data Tab > 'Get External Data' > 'From Text'. In the delimiting wizard, check 'comma' as delimiter and set Column Data Format to 'text'.

or

Make a copy of the .csv and change file extension to .txt. Double-click to open it with notepad/wordpad.

Hope this helps.
0
FaustulusCommented:
Toco,
I am using Excel 2010. After formatting the column as Text I don't get the leading zeros removed.
0
TocogroupAuthor Commented:
Hi All,

Thanks for all your suggestions.

Just to reiterate: I want to create a 3-columned worksheet as text, and convert it as a .csv file which can then be imported into an Active Directory update software tool. The tool won't accept the file as a .txt file...only as a .csv file.

However, the moment I save it as a csv file (it has to be .csv, not a .txt file) Excel converts the text items to numeric. I can't include formulas in the sheet as the software tool will only accept a csv file with values only. Single and double quotes aren't the answer either.

Changing the format to text is no good as Excel will convert the telephone numbers to numeric the moment it saves the sheet as a csv file.

The problem occurs when Excel saves the file, and not in formatting the telephone number as text.

I think I've been beaten on this problem.

Toco
0
TocogroupAuthor Commented:
Hi Faustalus,

Are you saving the worksheet as a .csv file ?
0
FaustulusCommented:
Hi Toco,
The problem occurs when Excel opens the cvs file.
1. Format the columns as Text
2. Save as CSV file
    Open the file in a text editor and you will see your zeros.
3. Open the CSV file and Excel converts the text to numbers.
4. Change the file extension to .txt
5. Now open the same file in Excel.
    You can specify the column format for each column.
    The default is General which convers to numbers.
    If you specify Text you get your zeros.

So, where is the problem? There shouldn't be one.
After saving your file as CSV the zeros are still in the file.
Does the tool you feed the CSV file to remove the zeros?
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
TocogroupAuthor Commented:
Hi Faustalus,

Thanks for the explanation. I thought it was the actual saving as a CSV file that removed the leading zeroes. I guess the tool must strip out leading zeroes then.

The tool will only accept a .csv file (the option is even called CSV Update). It doesn't give you the option of importing as a .txt file.

Toco
0
FaustulusCommented:
Toco,
What's the tool doing? It seems like it writes to an Excel workbook where the cells are formatted as 'General'. If it is impossible to adjust the tool to format cells at 'Text' it may be possible to treat the output workbook after the tool is done with it.
0
TocogroupAuthor Commented:
The tool is WiseSoft Bulk AD Updates.
It allows me to import bulk changes to the Windows Server 2008 Active Directory via a CSV file. It works fine but for this issue.
The program UI prompts for a CSV file (see attached screenshot)
Screenshot---CSV-Update-tool.JPG
0
FaustulusCommented:
I found the following solution at this link:
Data might be incorrectly converted to a number by the Microsoft.Jet provider, resulting in data loss.  e.g. The value 01234 is converted to 1234.  This can be prevented by using quotation marks in your CSV file to surround the value.  e.g. "01234" instead of 01234.  It's also possible to fix this problem using a schema.ini file.
Note: You might also experience a similar problem where a value gets converted to a date and the data appears in a different format in Active Directory.  e.g. 01/01/2010 would get converted to a date and appear as "01/01/2010 00:00:00".  This is part of the same issue and the workaround above will also apply.
They also promise to address the issue in their software's next version.
0
TocogroupAuthor Commented:
Yes, I found the same link. However, the double quotes are updated with the telephone number ! Not really what I wanted. I've sent them an email and hope they reply.

Thanks for your investigation. I'm going to apportion the points amongst those that give me a better insight into Excel and CSV files.

Thanks to you all.
0
FaustulusCommented:
I just came across this article and thought you might be interested.
0
TocogroupAuthor Commented:
Thanks for that very interesting and informative post. It explains the problem (and solution) very well.

Regards
Toco
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
Microsoft Excel

From novice to tech pro — start learning today.