Leading Zeroes 1

[randy700]

Excel 2010, opening a .csv file.
The file is created in a COBOL program and received thru email.

My incoming file looks like this:

"Company","Department","Customer","Sales","Salesman"
"Walmart","Shoes","0123456",0001.23,","Jones"

First line is column headings.
My problem: I cannot find a way to retain the leading zero in the customer field (0123456).
Unless I add an apostrophe to indicate it's a text field.

Changing customer numbers to exclude leading zeroes is not an option.
When received in email as a .csv file, the apostrophe is visible in the spreadsheet, unlike the display when manually typed.

Does anyone know how I can format the incoming file to retain the leading zeroes without showing the apostrophe?



Randy

 

[vgulielmus]

How do you open the csv?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[randy700]

Found a solution.​

"Walmart","Shoes",[b][red]=[/red][/b]"0123456",0001.23,","Jones"

This includes the equal sign and double quotes in the value, but not in the display.​

Randy

 

[SkipVought]

hi,

My problem: I cannot find a way to retain the leading zero in the customer field (0123456).

faq68-6659


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

BOTTOM LINE:

I'd recommend NEVER opening a .csv or other text file directly via Excel, as Excel will very helpfully CHANGE CERTAIN DATA based in some built-in assumptions.

Rather IMPORT your data into an Excel workbook, via Data > Get External Data > From Text...

This will make it possible to define specific columns as TEXT or DATE or GENERAL.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[randy700]

Skip,

Thanks for your reply, but it certainly was of no help to me.

I was looking for a method to build the input file in a way to display the data as needed.
That method is available (see my earlier post) and it is not in the FAQ you referenced.



Randy

 

[SkipVought]

Huh?

If your .csv input file is simply...
[pre]
Company,Department,Customer,Sales,Salesman
Walmart,Shoes,0123456,0001.23,,Jones
[/pre]
...then IMPORTING this file and selecting the appropriate column formats will result in the Customer ID with leading zeros!

Once you have specified this in the IMPORT Wizard, then all you need to is REFRESH the QueryTable each time you have new data in your .csv.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[randy700]

The individuals who need this file are too far up the food chain for me to force them to learn anything other than CLICK.
I need a method to create the csv file that will allow them to open the new file in an excel format and retain all leading zeroes in TEXT fields. These fields were created in COBOL, years ago, before Excel even existed. Changing thousands of customer numbers, vendor numbers, etc is simply not an option. The solution I posted works to display the leading zeroes as needed.

Importing is not an option.
Pre-formatting is not an option.

Perhaps I should have posted this question in a different forum.

Randy

 

[SkipVought]

We are talking past each other man!

I need a method to create the csv file that will allow them to open the new file in an excel format and retain all leading zeroes in TEXT fields.

It is NOT a function of the text file. You already have your leading zeros in your text file! There is nothing else that you can do with your text file that will effect the result you want in Excel.

Importing is not an option.

This is an assertion, not a requirement! Why is this not an option? YOU, not anyone else, ONE TIME, set up a workbook to IMPORT the text file and in so doing, assure that the Customer ID with leading zeros will not be CHANGED by Excel to a NUMBER, by specifying, ONE TIME, the format of that column as TEXT!

YOU, randy700 (Programmer), can set up this dashboard workbook to automatically refresh the QueryTable each time it is opened by the user, no user intervention required!

The option that is NOT available for ANYONE is to open the text file directly and expect the data to appear as required! What follows is the default result. You must IMPORT and SPECIFY in order to avert this behavior!

[pre]
[highlight #FCAF3E]Examples how Excel "helps" you out:[/highlight]
Data Entry Excel Changes to
000123 123
123E2 12300
1/23 01/23/2014 actual value [highlight #FCE94F]41641[/highlight]: month 1, day 23 of the current year
1: 01:00 actual value [highlight #FCE94F]0.0416666666666667[/highlight]
[/pre]



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}