How do I open CSV using Excel without deleting leading zeros?

Hello,

Whenever I open up a CSV file or a Text file, Excel treats string like 05710 as a number and turn it into 5710. How do I open up a CSV file and still keep the leading zeros?

Thank you very much.

[220 byte] By [magicalclick] at [2007-12-27]

«« SQLWB.EXE max cpu
»» LineItem.DisplayName

# 1

In the CSV file place a ' (single quote) in front of the number such as

123,'0456,

OmegaMan at 2007-9-4 >

top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...

# 2

Thanks. But the thing is I don't want to change the CSV file before I open it. I have a loop that populate each cell one by one from the CSV file. But it is not effecient.

magicalclick at 2007-9-4 >

# 3

If you are populating it into Excel, specify that the cell should be Text format. See my post here Scientific Notation where I specify how it is done.

OmegaMan at 2007-9-4 >

# 4

Thank you for the help.

I did that on my current appraoch. I set all cells to Text and populate the cell one by one. So, it is pretty slow compare to Excel default file loader. Excel open up a CSV file in one second. Mine takes a long time depends on number of cells I am populating. I think 10K cells takes about 5 seconds, pretty slow. I am going to twick it a bit to load 5 times faster. But I think my way is no where near as fast as the Excel CSV loader though. Oh well, I guess I just have to live with that.

magicalclick at 2007-9-4 >

# 5

You may want to post another thread with what you have to do, and see if there is a way/method to help the process speed up. Before I discovered, through these forums, the methodology of placing all data in an array and loading it all at once, my data load was slow too. Good luck.

OmegaMan at 2007-9-4 >

# 6

Thank you very much. That sounds like a good idea because it skips the screen refresh slowness. Maybe I can create a Range object and then swap that with Cells. That will be so fast.

magicalclick at 2007-9-4 >

# 7

Hi,

but if I put the single quote then the cell content become '0456 instead of 0456 that is what I need.

Is there a way to tell Excel to interpret a function? e.g. =text(0456,"0000")

Thank you,

Cristina.

Cris831 at 2007-9-4 >

# 8

One way to divine the inner workings of any office document and how to manage it via the interops is to record a macro of the process needed. Once done examine the vba code, it will show settings changes and other items of interest that can lead the way through the tribal knowledge of the interops.

If no one has an answer...try posting it in the Discussions in Office Development or for a better responses or Discussions in Automation forum for interop questions.

OmegaMan at 2007-9-4 >

Microsoft ISV Community Center Forums

Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

it's Archive about 'How do I open CSV using Excel without deleting leading zeros?...'

How do I open CSV using Excel without deleting leading zeros?

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Site Classified