Hi, I am running a graph to export data to an excel file, few columns have zipcodes and when i open it in excel, the leading zero’s get dropped. I searched this forum and saw a link which gave me a solution to use the below, but it doesn’t work and i get a warning
ltrim_blanks(to_char(table.columnname,‘09999’))
Conversion warning <Warning: Expression <table.column> of type will be converted to type .> for expression <ltrim_blanks( to_char( table.column, ‘09999’))
Now to make matters worse or complicated, I have another column which does also have leading zeros but not fixed as a zipcode in length. It can be 004, 0045, 010008. How can I in this instance, not drop the leading zeros.
I don’t think this is an issue in DS. Excel is doing so called smart features where it hides those ZERO’s. I always say, if you will have to publish data to someone using Excel, then Data Services is not the tool. It is not a front-end friendly tool! You will have to use BO Reporting or for that matter whatever reporting tool.
You will have to use LPAD(COLUMN,5,‘0’) for getting always a 5 Character in a column. To trim the leading '0’s you will have to use LTRIM(COLUMN,‘0’)
Excel thinks your columns are numeric, so it drops the leading zeroes. Force it to be a string. You could try adding a single quote mark like this: '80123. That tells Excel that the value is a string, not a numeric. I’m not sure how that will translate from DS to the .csv file though.
The datatype for the field is set to varchar in DS. I did try the lpad(column, 5, ‘0’) but still the same result.
Unfortunately, i cannot add a quote in the beginning as this final file is picked up via ftp to load data into another application which is expecting jus numeric values.
Your target application consuming in a Excel File? Can’t you send in CSV?
Send in the Excel File with Column Header, then Excel will assume all the columns as VARCHAR.
Wait a minute, in DS you cannot have Excel Target. I believe it is just the case when you open and see? Does your target system folks ask for a Excel File or a CSV file?
My apologies for calling it excel. End result is in a csv file. But the application treats it as an excel file as it can accept only excel files so, i had to use a csv in the flat file but once the application picks up the file, the leading zeros gets dropped.
Can you sketch our your entire solution and avoid words like “the application” because I’m slightly confused as to what data goes where and through which software.
We’ve been dealing with this one for years. The way to get around it in Excel is to first open Excel and browse to the file. Then it allows you to select the format for individual columns.
You could also use VBScript and the Excel.Application to open the file, format the column and save it. Caveat: If you have unicode in your data, Excel may corrupt it on saving.
You can also change the file format from .csv to an actual .xls file. Preserving the leading zeros. O.o
(BOB member since 2008-07-17)
(BOB member since 2007-01-10)
(BOB member since 2008-07-17)