CSVs - enforcing preceeding zeroes

  • Thread starter Thread starter amb
  • Start date Start date
A

amb

I have a programmatic problem, where CSVs are being produced automatically
by some Transact SQL processes.

The CSVs are then used by a number of users, some who are not very
technically literate

The CSVs can look a bit like this
"0401","Data","Data","000234092"

But when the CSV is opened in excel the preceeding zeroes vanish. Im trying
to force it to open with the zeroes already there. Otherwise I am
continually have to show users through how to use the 'Format Cells' options
to get the numeric IDs to display correctly.

Is there any way I can do this.

I have tried preceeding the data with a single quote eg "'0401" but Excel
converted it into the display

(Incidentally CSVs are used because not all users are using Excel and for
the minority that arent, they can live with the 401's)

Amb
 
Well - I have found that putting =Concatenate("0401") works.

The CSVs look like this now:
=Concatenate("0401"),"String","value",=Concatenate("0401")
=Concatenate("0401"),"String","value",=Concatenate("0401")
=Concatenate("0401"),"String","value",=Concatenate("0401")'

Which isnt very tidy, but works. Unfortunately, I have a feeling it will
stop the values from being loadable directly into Access, but it is the more
advanced users that do this (I hope)

Other ideas still appreciated? There must be a smoother way

Amb
 
amb said:
I have a programmatic problem, where CSVs are being produced automatically
by some Transact SQL processes.

The CSVs are then used by a number of users, some who are not very
technically literate

The CSVs can look a bit like this
"0401","Data","Data","000234092"

But when the CSV is opened in excel the preceeding zeroes vanish. Im trying
to force it to open with the zeroes already there. Otherwise I am
continually have to show users through how to use the 'Format Cells' options
to get the numeric IDs to display correctly.
Click to expand...

Could you change the template of the excel document so that all the cells
are pre-formatted the way you want?
 
Well - given that im providing a CSV that they could use on any system, no.

I think im going to write the files in XML instead. Its about time I learnt
about XML.
 
Back
Top