Text vs. Number

[John Persico]

Why is is that if I have a tab-deliminated file or csv file that I open in
excel the following happens:

If the field has twelve digits (a UPC code) -- say 068888701648 -- that when
it gets into the Excel spreadsheet is drops the front zero and calls it a
number.

I don't want that to happen.

Also when I export to a csv file I want the field to be a text field --
"068888701648" -- as opposed to a number.

This is the reason. What I've been doing up to this point is creating a
custom format -- 000000000000 -- twelve digits. Most UPC codes have a zero
at the beginning. So this forced my field to put create a 12 digit number
whenever Excel only wanted to make it 11 digits, because of the zero up
front.

And, when I write a CSV file out, sure enough the field shows up properly
when I load it into a text editor -- ...,068888701648,...

But, now I've discovered that my shopping software ALSO takes off the front
zero when I load my products via CSV. So, the tech people have told me to
create a text field instead of a number field.

How can I make all this happen?

 

[Gord Dibben]

Change the csv extension to txt.

Open the txt file in Excel and the Text Import Wizard pops ups up.

Parse and designate the column data format.


Gord Dibben MS Excel MVP

 

[John Persico]

Thanks. I actually figured this out after I wrote the message.

I know this might sound obvious, but when I bring up folders, they don't
show the file extension. So, when I try to rename a file, I'm only allowed
to change the name before the extension. How can I change how I view the
files in the folder so that I can actually change the extension?

 

[John Persico]

I figured out how to change the extension.

--

Thanks. I actually figured this out after I wrote the message.

I know this might sound obvious, but when I bring up folders, they don't
show the file extension. So, when I try to rename a file, I'm only
allowed to change the name before the extension. How can I change how I
view the files in the folder so that I can actually change the extension?

 

[John Persico]

When I change the extension to .txt and use the import tool, delimited,
comma, for some reason the file doesn't open properly (the rows are not
setup properly).

But, if I let Excel do it, clicking on the .csv file, the file opens fine
(but I have the UPC problem).

Any idea why the Excel import tool is not working like I thought it would?

 

[Dave Peterson]

My first thought is that you did something wrong when you imported the .txt file
(after the rename).

I'd suggest that you try it once more to see if you can get it to work.

If that fails, then I think you're going to have to explain in greater detail
what's going wrong.