CSV Files (1)

Topic

New Reply

Does anyone know how to add leading zeros to a CSV file?

Reply | Quote

Replies

Load the file into Excel (if it is not too large).
Use the text to colums feature to break the data into separate columns. Ensure each columns data type is set to text.
Then you can manipulate the colum you wat to add leading zero’s to. I suggest that you use a fourmula in another spreadsheet to do this eg =”00″ & A1 to add 2 leading zero’s, and then seelct the whole column and paste backinto the orginal using Paste Special and the values option.

When all looks OK, save the fiel as CSV and you are finished.

Reply | Quote

I have an entire column which has three digit numbers such as 100,108,109 etc. all of which need to have leading zeroes. I tried the formula you mentioned; however, I believe I maybe entering it in wrong. Any suggestions would help

Reply | Quote

Are the entries currently three digits, and you need to have a leading zero to bring them up to four digits?

Reply | Quote

Yes, the entires are three digits and need to be four digits
Example: 100 needs to be 0100

Reply | Quote

If the column is the first one in the CSV file, then the solution is a minor variation. Instead of replacing CR/LF/” combinations with CR/LF/=”, the replace changes to CR/LF/=”0.

If it is not the first column, then the solution may be simpler in a formula within Excel.

For example, if the entry is in column B, and the first entry is in B2:

=text(B2,”000#”)

Hope this helps.

Reply | Quote

All the entries are in column G. The formula you gave me worked however it changes the entire column to have the same data. I have multiple entries such as 100, 108, 109

Reply | Quote

Instead of using B2 is there a way to reference all cells within column G? For instance something like =text(*.*,”000#”)

Reply | Quote

If the first entry is in G2, then the formula is =text(G2,”000#”).

Copy that formula down, and the cell references will automatically change to reflect the appropriate row. For example, the second entry formula will be =text(G3,”000#”), and the third entry will be =text(G3,”000#”)

Regards.

Reply | Quote

The CSV will not save formulas

Reply | Quote

A CSV is only supposed to save the values.

FWIW, I used HansV’s suggestion and exported an Excel file with a column of employee counts (all less than 1,000) within a table set as Format, Custom, 00000, to CSV. The CSV saves the data with the leading zeros, which can be seen by opening the file with Notepad. However, on reimporting the CSV into Excel, the leading zeros are lost and the column has to be reformatted.

[Edited] Sorry if that is unclear. I understand that you are trying to get an existing CSV to have the leading zeros. Open it in Excel then format the applicable columns, then re-export it, the CSV will have the leading zeros.

Reply | Quote

Have your tried opening the CSV file in XL and formating the column with cell formats where the category is “custom” and the type “0000”? You should see all numbers displayed as four digits such as 100 being 0100.

You can save the CSV file with the new format. One thing though if you try to open the CSV file in XL once again it will lose the format you saved it under.

Reply | Quote

I would like to thank everyone who responded to this posting, my apologies that I have not responded sooner as I have been on vacation. As it turned out the CSV file that was extracted from Oracle DID have the leading zeros; however, when we opened the CSV file in XL it lost the format we saved under.

Reply | Quote

If you want to save numbers with a fixed number of digits (e.g. 1 becomes 0001, 23 becomes 0023, 456 becomes 0456 and 9876 becomes 9876), you can simply format them in Excel as “0000”. Exporting to CSV will preserve this format.

Or do you want to add leading zeroes to an existing CSV file?

Reply | Quote

I need to add leading zeroes to an entire column in an existing CSV file. Any help would be greatly appreciated.

Reply | Quote

The normal routine for a CSV is, of course, something similar to this:

“Smith”,”John”,”000123456″

which when loaded into Excel gives you

Smith John 123456

However, try this:

“Smith”,”John”,=”000123456″

I think this is what you are after.

Reply | Quote

I have an entire column in which all cells need to have leading zeroes. For Instance 100 needs to be 0100

Reply | Quote

If the column with the need for the leading zero is the first column, then open up the CSV file with a good text editor, and replace the CR/LF/” entries with a CR/LF/=” entry. (Of course, you would have to change the first line manually.) If it is not, then you may want to replace all ,” with ,=”.

This second choice will play havoc with any other numeric values in the file. But they can be addressed via value() formulas afterwards.

Hope that helps.

Reply | Quote

I have done this in the past as follows:

– Import the csv file into excel.
– Insert a column next to the field you need to add leading zero’s too.
– Create a nested if then statement that states
— If length of field needing leading zeros is 4, then ‘0, 3 then ’00, 2 then ‘000 1 then ‘0000, else ‘00000
—– this creates the adding zero’s needed. The ‘ is required to make this work.
– Copy and the Paste the column on itself using the Paste Special, values to get rid of the if then statements.
– Insert another column and then concatenate the leading zero’s with the original column to give you ‘0100.
– Copy the concatenated data and paste special on the original field with 100.
– Delete extra columns
-Re-save the data as a .csv.

You will not lose the leading zeros doing it this way and the leading zero’s will still be there if you re-import the data into excel.

Hopefully, this will give you some other ideas.
HTH – even if it is a bit manual….

Reply | Quote

Gary,

What would the nested if then statement look like?

Reply | Quote

The nested if then would look something like this.

=IF(LEN(C3)=1,”‘0000″,IF(LEN(C3)=2,”‘000″,IF(LEN(C3)=3,”00″,IF(LEN(C3)=4,”‘0″,””))))

You can then concatenate this column with the original data to get the leading zero’s.

Reply | Quote

Gary,

I think this can be simplified. The leading zeroes and the concatenation can be combined into one formula:

="'"&REPT("0",4-LEN(C3))&C3

if C3 is the cell referred to.

Reply | Quote

… so thats how you combine the two formula’s. I was never able to figure it out.

Reply | Quote

I think you will find that =text([cellref],”0000#”) will accomplish the same task, and with a lot less brackets.

Reply | Quote

I’ve been doing something similar to GARYPSWANSON but without using the if formula

I have done this in the past as follows:

– Import the csv file into excel.
– Insert a column next to the field you need to add
leading zero’s too.
– change the 101,102,103 to numbers and do an sum example +G1+1000000
– Copy and the Paste the column on itself using the
Paste Special, values to get rid of the sum
– Do a copy paste special and select fixed width and
decide how many digits it should be
– on the next screen select text
– Copy data and paste on the original field with 100.
– Delete extra columns
-Re-save the data as a .csv.

Reply | Quote

I think it’s time to do a little thinking. What do you want to do achieve with your CSV file?

You have been given several suggestions that will save numbers formatted with leading zeroes to a CSV file. The easiest one, I think, was my suggestion to open the CSV file, format a column with the custom format of 0000, and then save it. The saved file will contain the formatted numbers, as you can see when you open it in NotePad (JohnBF also remarked this). However, the formatting will be lost when you import the CSV file into Excel or Access.

There is probably a way of saving the values with an apostrophe ‘ in front of them. That way, they will be interpreted as text in Excel. But I think they will be useless for use in other applications. The purpose of CSV is to enable data interchange between different applications.

I don’t want to sound harsh, but I think it probably comes down to this:

If you want to have a file with formatting, save it as an Excel worksheet. Many programs can read Excel worksheets.
If you really need a CSV file, don’t bother with formatting the data. Do the formatting in the application in which you use it.

Reply | Quote

Hans,

I have to agree and disagree with your statements. From doing data conversions using .csv files as the required format to be submitted, if the apostrophe is not inserted as you state, the format is lost during the import of the data. This wreaks havoc on the input programs and causes the data to fall into incorrect categories and be misread. (I am speaking from doing volumes of conversion data from Oracle, to Excel / CSV and back into SAP.) — of course I may be looking to deep into this.

Other applications will ignore the apostrophe on import and treat the data as text as you indicate. Trying to format the data after import is also useless as the data is out of format and alignment. Once the data is imported into the system, the format can be re-established.

Reply | Quote

Gary,

I bow to your greater experience. I don’t use Oracle and SAP (OK, I use SAP as an end user – grumblingly).

I work mostly with Access. Access doesn’t ignore an apostrophe in front of a number. That was the cause of my remark.

Regards,
Hans

Reply | Quote

… Hans,

If the conversion people had used access from the start, then we would never have encountered this problem. Actually, we never did figure out why they didn’t just transfer data from one Oracle database to another??? They ultimately went from each accounting system into Excel to save as a .csv and then imported the csv data into access for testing and then took the correct csv file into Oracle. (Kind of a long and odd way to do this)

We had to help many people to deal with the issue of losing leading zero’s on fixed width columns of data for the conversion process.

As always, thanks for your input and suggestions, … and I return the bow to your knowledge of access and other programs (which far exceeds my knowledge).

Reply | Quote