How can we help you today?
Leading Zeros
12 CommentsSorted by Oldest First
You are most likely losing them because you are opening up the CSV with excel and then saving it again. a CSV (comma separated values) file doesn't retain any formatting information so any fields that look like numbers will be interpreted as numbers when loaded into Excel. That means that Excel will remove the leading zeroes if it thinks it is a number.
If you can export directly to an excel document instead of a CSV that is best because RE will format the column with the ID as "text" specifically which will leave the zeroes. Then you can "save as" a CSV file for the import and the zeroes will be there.
If you are starting with a CSV file then you need to open Excel separately with a new blank worksheet. Go into the Data tab and select "From Text" in the "Get External Data" section. Basically you are going to import the CSV file into Excel. When you click "from text" it will open up a wizard that will ask if the file is delimited (it is) and then what it is delimited by (a comma) then it will let you choose what data type each column is. You should be able to leave most of them as the default but select any columns that might have leading zeroes (like IDs, zip codes, CC last 4, etc.) and choose the "text" type for those columns. Then hit finish and you will have your leading zeroes!
Sounds long but actually it's pretty simple once you have done it once. As I said though, it's generally a good idea to export to an excel file first and then save as a CSV from Excel once you have made any edits that you want.
-Wayne
P.S. This is a web page that explain the process a lot more clearly! http://www.upenn.edu/computing/da/b...Zeros.html
If you can export directly to an excel document instead of a CSV that is best because RE will format the column with the ID as "text" specifically which will leave the zeroes. Then you can "save as" a CSV file for the import and the zeroes will be there.
If you are starting with a CSV file then you need to open Excel separately with a new blank worksheet. Go into the Data tab and select "From Text" in the "Get External Data" section. Basically you are going to import the CSV file into Excel. When you click "from text" it will open up a wizard that will ask if the file is delimited (it is) and then what it is delimited by (a comma) then it will let you choose what data type each column is. You should be able to leave most of them as the default but select any columns that might have leading zeroes (like IDs, zip codes, CC last 4, etc.) and choose the "text" type for those columns. Then hit finish and you will have your leading zeroes!
Sounds long but actually it's pretty simple once you have done it once. As I said though, it's generally a good idea to export to an excel file first and then save as a CSV from Excel once you have made any edits that you want.
-Wayne
P.S. This is a web page that explain the process a lot more clearly! http://www.upenn.edu/computing/da/b...Zeros.html
Issues like this are typically why I tell people not to use .csv files, and instead use .txt files. The format of the file itself is identical, but Windows treats them differently, and as such it's much harder to break a .txt file by accident.
Thank you for your suggestions. I like the Dictionary solution, as this will continue to happen in the future & others might not remember to adjust the CSV file. I'm getting stuck on the iterations...any help would be appreciated!
The first one I entered works; I changed ^\d{4}(-\d{4})?$ to ^\d{3}(-\d{3})?$ because that’s the number of digits without the leading zeros (total of 8 digits). But when I apply the same principles to the 2nd one, it goes haywire? I only need one zero added to 7 existing digits (for a total of 8 digits). What am I doing wrong? Please see attached screenshot.
Tracie, I might be under-thinking it a bit but wouldn't this work?
Replacement Value Value to Match On
0$& ^\d{7}$
That would add one leading zero to a string of 7 digits, making an 8 digit result with a leading zero (1234567 becomes 01234567). Hope that helps, sometimes a simpler regex is the answer when manipulating those syntax expressions.
Best of luck in all you do, Allison B
Replacement Value Value to Match On
0$& ^\d{7}$
That would add one leading zero to a string of 7 digits, making an 8 digit result with a leading zero (1234567 becomes 01234567). Hope that helps, sometimes a simpler regex is the answer when manipulating those syntax expressions.
Best of luck in all you do, Allison B
Thanks for your reply, Allison! Unfortunately, it didn't work...see attached for a screenshot of what I got.
I think it's the -BLANK- in there that is messing it up. Try to delete that entry on the right.
I'm not able to delete it...?
Tracie,
Highlight the -BLANK- value under REplacement values and hit your delete key. That should remove the whole entry from the dictionary.
-Allison B
Highlight the -BLANK- value under REplacement values and hit your delete key. That should remove the whole entry from the dictionary.
-Allison B
Here is my favorite way to handle the iterations...just copy and paste from excel!
| 0$& | ^\d{7}$ | TRUE |
| 00$& | ^\d{6}$ | TRUE |
| 000$& | ^\d{5}$ | TRUE |
| 0000$& | ^\d{4}$ | TRUE |
| 00000$& | ^\d{3}$ | TRUE |
| 000000$& | ^\d{2}$ | TRUE |
| 0000000$& | ^\d{1}$ | TRUE |
Success!! I started over using your excel doc.
Much thanks!
Much thanks!
Hi all - sorry this is an old thread, but has anyone had the issue of using this type of dictionary on an unknown/variable length ID? We have a database that has changed const. ID formatting several times, resulting in variable lengths, with some leading zeroes all over the place. How would you go about resolving this?
Ashley, what do you want to do exactly?
Do you have variable length IDs and want to front-pad them with 0's to all be the same length? Or are you trying to remove the leading zeroes?
Do you have variable length IDs and want to front-pad them with 0's to all be the same length? Or are you trying to remove the leading zeroes?
Help Desk Software
by Freshdesk Support Desk
Tracie Cassidy
I have a few old RE records that have leading zeros in the donor ID. They are throwing excpetions when I try to import. Is there a way for me to retain the leading zeros on my data sheet so they will import in IOM?