I need some help with a spreadsheet project. I am not a frequent or skilled user of Excel 2013 and I am having a few problems in which there is likely an easy fix by someone more knowledgeable and experienced than me.

This is an example of one row of comma separated value data that I want to import into Excel 2013:

37950,004,08/21/2014 20:49:22,778052149 ,00310906021959200001

Column 1: ZIP Code (5-digit only)
Column 2: Date & Time (hh:mm:ss)
Column 3: ZIP+4+2 Code (11-digits; note last two digits are usually blank spaces in downloaded data)
Column 4: Tracking Number (20 digits)

How do I importing csv data to achieve the following:



Columns 1 should be ZIP Code (5-digit) formatted
Column 2 should be date formatted
Column 3 should be ZIP+4 (9-digit) formatted (ignore 2-digit blank spaces)
Column 4 should be numeric formatted data
Maintain all preceding zeros

I am currently getting:
(1) Dropped preceding zeros in all columns which I want to maintain
(2) Seconds in date/time dropped (but okay)
(3) Column 4 is main problem...imported data does not automatically format as numeric values and instead displays "3.10906E+17" (example)
(4) Column 4 certain values are zeros (not the value) For example, "200001" in example record at top of this message appears as "200000" and "200010" also appears as "200000"

How do I maintain preceding zeros?
What's happening to my last column values on importing?

Any help appreciated.

Keith

Instead of simply opening the file, use the Data>Text from File method to import the data. For column 4, specify 'Text' during the import. For columns 1-3, you should be able to get the formats you require by applying custom cell formats after importing the data.

For column 1, use: 00000
For column 2, use: DD/MM/YYYY HH:mm:ss or MM/DD/YYYY HH:mm:ss
For column 3, use: 00000-0000, though I do know what you mean by 'ignore 2-digit blank spaces'

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul:

Your suggested method for importing data does seem to be a little easier than what I was previously doing...opening my downloaded csv data file into a spreadsheet and then copying it over to where I desire the data in my main spreadsheet.

When I used your method, I formatted the columns to restore the dropping of preceding zeros so that annoying problem was easily resolved.

However, I am still getting a rather odd import data error. Here are two examples of numeric data from column four (20 digits): "00310906021959200001" & "00310906021959200016". Note the only difference is the last two digits in the string. After importing (by my earlier method or yours) from the csv text file, I always get "00310906021959200000" (last two digits are getting changed) for EVERY ROW. What is happening? When I format the column to text I get "3.10906E+17". What is going on during the import of the fourth column data? What are values getting changed and why can I not format the data as text? I am attaching a small sample csv data file if you or anyone else wants to play with it.

Many thanks!

Keith

Attached Files

Sample Data File.txt (1.1 KB, 8 views)

That suggests you didn't select 'Text' as the import format for column 4.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul:

I discovered what I was doing wrong...not selecting ALL columns as text (I was apparently only selecting the default first column as text). When I import now I get exact copy of data from text file and even the leading zeros are retained.

Thanks again.

Keith

I imported csv data, a date, as text. After importing in my spreadsheet, I tried to format the column to a particular date format but it does not seem to work. Any workaround to this?

During data importing, I suspect there cannot be different formats among the columns and is it all one way. I haven't tried but perhaps one could probably import the date separately from the other column data? The later option seems like lots of work to simply obtain a desired date format. Any suggestions welcomed.

I don't think this needs to be a separate thread as it's part of the same issue I posted.

Thanks,
Keith

As I have already said, it's the 4th column you need to specify the 'Text' format for. When importing, different columns can have different formats. You should not be importing the others as text.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]