SQL experts? Adding Leading Zeros on some Barcode Items

Jun 07, 2007 3 Replies
R

I've imported about 4,000 items into RMS but first edited the .csv in excel, then exported to CSV to do the import. I just realized that the leading zero on some barcodes (item numbers) were taken off in excel & didn't make it to the final csv file...



Any SQL experts have a suggestion? Need to do do something like this (that works)...



UPDATE ITEM_BARCODE SET ITEM_BARCODE= 0+ITEM_BARCODE Where ITEM_BARCODE size =< 10



Barcode size needs to be 11 long (we are not sending the last 'check digit' from the UPC.


Vote
Twitter Facebook LinkedIn
J

The solution in Excel to stop the leading zero from falling off is the format the column as a TEXT value type before you enter the data. I'd make the fix in Excel in another column rather than making the fix in RMS. Let's say your ILCs are in column A. In another column, use a formula like this:

=if(len(A2) I've imported about 4,000 items into RMS but first edited the .csv in

Vote
Twitter Facebook LinkedIn
B

=CONCATENATE(0,A1) also works in excel to add a zero in front of a number and since it is a text function it will keep the leading zero

Barry

Vote
Twitter Facebook LinkedIn
J

Very true - plus you don't have to format the column as text if you paste special / values because you are pasting text, not a number.

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required