[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Prevent reformatting of data in CSV import in Excel

How to force excel to treat columns as text automatically. I have a .csv file as below

acp,acno,ivs,ivt
"PI",500000,"01","SD"

The third column always has the leading 0 stripped. I know I can change it to a .txt file name and use the import wizard dialogue to manually treat the column as text. But I really prefer it to be automatic and not force the user to constantly manually set column types. Is there a way to force the column to automatically be text? I thought I had read something about apostrophe at the start of the column but that doesn't seem to work, the apostrophe stays in the column in Excel.
0
gringlobal
Asked:
gringlobal
  • 2
  • 2
1 Solution
 
mark_harris231Commented:
If you have control on the export format of the CSV, use the equal sign in front of the values you don't want intepreted:

e.g., ="PI",500000,="01",="SD"
0
 
pony10usCommented:
Do you have the ability to control the content of the .csv file?  If so, can you try making the third column a formula?

i.e.:   ="001"  The = needs to outside the quotes.
0
 
pony10usCommented:
oops - I started my reply and got interrupted.  Mark gave the proper response.   :)
0
 
gringlobalAuthor Commented:
I have control of the format and the = seems to work. You see ="01" when you select the cell but it looks OK in the cell and filter seems to work. Thanks.
0
 
mark_harris231Commented:
Glad to be of help...
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now