#1   Report Post  
Patrick
 
Posts: n/a
Default import csv file

How can I get excel to import a csv file with entries like "000111" and
format it as text



My database creates a csv file to load into excel

It contains a name and a id field
eg
"joe bloggs","jb01"

this loads into excel no problem both columns formatted as text as needed

unfortunately some of the ids begin with zero and only contain numbers
when loaded into excel they are convereted to numbers and leading zeros
chopped off. I need them to stay as text

I can make excel display leading zeros - but its still a formatted as a
number - no good!
Using a ' on the front loads in correctly but as soon as the ' is removed it
reverts to a number

Thanks

--
Patrick
  #2   Report Post  
Nick Hodge
 
Posts: n/a
Default

Patrick

Excel sees a file with an extension of *.csv as native and so opens it
without any user intervention. This can be both an advantage and a
disadvantage. You have the latter.

To overcome, rename your file with no extension and in Excel go to
Open...'Files of type' 'All and navigate to your file. This will invoke
the text import wizard. Use delimited and then comma and in the third step,
highlight the columns you want as text and mark them as so. Excel will
import these as so with leading zeroes, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Patrick" wrote in message
...
How can I get excel to import a csv file with entries like "000111" and
format it as text



My database creates a csv file to load into excel

It contains a name and a id field
eg
"joe bloggs","jb01"

this loads into excel no problem both columns formatted as text as needed

unfortunately some of the ids begin with zero and only contain numbers
when loaded into excel they are convereted to numbers and leading zeros
chopped off. I need them to stay as text

I can make excel display leading zeros - but its still a formatted as a
number - no good!
Using a ' on the front loads in correctly but as soon as the ' is removed
it
reverts to a number

Thanks

--
Patrick



  #3   Report Post  
Patrick
 
Posts: n/a
Default

Thank you

Of course it works perfectly

Been tryiong to do it for ages

Thanks

Patrick

"Nick Hodge" wrote:

Patrick

Excel sees a file with an extension of *.csv as native and so opens it
without any user intervention. This can be both an advantage and a
disadvantage. You have the latter.

To overcome, rename your file with no extension and in Excel go to
Open...'Files of type' 'All and navigate to your file. This will invoke
the text import wizard. Use delimited and then comma and in the third step,
highlight the columns you want as text and mark them as so. Excel will
import these as so with leading zeroes, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Patrick" wrote in message
...
How can I get excel to import a csv file with entries like "000111" and
format it as text



My database creates a csv file to load into excel

It contains a name and a id field
eg
"joe bloggs","jb01"

this loads into excel no problem both columns formatted as text as needed

unfortunately some of the ids begin with zero and only contain numbers
when loaded into excel they are convereted to numbers and leading zeros
chopped off. I need them to stay as text

I can make excel display leading zeros - but its still a formatted as a
number - no good!
Using a ' on the front loads in correctly but as soon as the ' is removed
it
reverts to a number

Thanks

--
Patrick




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing MM:SS From Tab Delimited File and Charting Bryan Charts and Charting in Excel 4 July 28th 06 04:14 PM
Problem with xlusrgal.xls file Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 06:54 PM
File asks to update when no links were created Dave Links and Linking in Excel 1 December 16th 04 09:10 PM
Getting a file and Saving a File carl0s66 Excel Discussion (Misc queries) 2 November 29th 04 10:59 PM
Open a file in excel from a link in eplorer Dave Peterson Excel Discussion (Misc queries) 0 November 26th 04 02:25 AM


All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"