Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2014, 01:48 AM
ekeithjohnson ekeithjohnson is offline Importing & Formatting CSV Data Problems Windows 7 32bit Importing & Formatting CSV Data Problems Office 2013
Novice
Importing & Formatting CSV Data Problems
 
Join Date: Aug 2014
Posts: 12
ekeithjohnson is on a distinguished road
Question Importing & Formatting CSV Data Problems

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
Reply With Quote
  #2  
Old 08-23-2014, 11:41 PM
macropod's Avatar
macropod macropod is offline Importing & Formatting CSV Data Problems Windows 7 64bit Importing & Formatting CSV Data Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 08-24-2014, 02:02 AM
ekeithjohnson ekeithjohnson is offline Importing & Formatting CSV Data Problems Windows 7 32bit Importing & Formatting CSV Data Problems Office 2013
Novice
Importing & Formatting CSV Data Problems
 
Join Date: Aug 2014
Posts: 12
ekeithjohnson is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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'
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
File Type: txt Sample Data File.txt (1.1 KB, 6 views)
Reply With Quote
  #4  
Old 08-24-2014, 02:21 AM
macropod's Avatar
macropod macropod is offline Importing & Formatting CSV Data Problems Windows 7 64bit Importing & Formatting CSV Data Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That suggests you didn't select 'Text' as the import format for column 4.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-24-2014, 03:49 AM
ekeithjohnson ekeithjohnson is offline Importing & Formatting CSV Data Problems Windows 7 32bit Importing & Formatting CSV Data Problems Office 2013
Novice
Importing & Formatting CSV Data Problems
 
Join Date: Aug 2014
Posts: 12
ekeithjohnson is on a distinguished road
Default Importing CSV Numeric Data as Text

Quote:
Originally Posted by macropod View Post
That suggests you didn't select 'Text' as the import format for column 4.
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
Reply With Quote
  #6  
Old 08-24-2014, 04:14 AM
ekeithjohnson ekeithjohnson is offline Importing & Formatting CSV Data Problems Windows 7 32bit Importing & Formatting CSV Data Problems Office 2013
Novice
Importing & Formatting CSV Data Problems
 
Join Date: Aug 2014
Posts: 12
ekeithjohnson is on a distinguished road
Exclamation Formatting Imported Text

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
Reply With Quote
  #7  
Old 08-24-2014, 08:32 PM
macropod's Avatar
macropod macropod is offline Importing & Formatting CSV Data Problems Windows 7 64bit Importing & Formatting CSV Data Problems Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save conditional formatting when importing gbaker Outlook 0 04-12-2013 07:42 AM
Importing & Formatting CSV Data Problems Importing Styles causing odd formatting problems. Red Pill Word 3 06-12-2012 06:19 AM
Importing & Formatting CSV Data Problems Problems with importing text and formatting lists mawallace Word VBA 1 06-11-2012 04:35 AM
Importing data in PDF into Powerpoint HowardC PowerPoint 2 03-14-2012 11:21 AM
Importing & Formatting CSV Data Problems Problems Importing Pictures Maurices5000 Word 2 02-03-2011 07:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft