convert ###,###,##0 to INT

Hi,

I've imported an Excel file into a work table, via an Access Project. One of my fields is an integer, represented in Excel with thousands separator e.g. 3,137,458

The above now sits in a varchar column, and I need to convert these values to an INT. Strangely, is numeric() returns One, but then convert( int, ...) does not like the commas.

To add insult to injury, my MSDE does not seem to allow me to CREATE FUNCTION. It protests even if I do Grant Create Function to Login, while running as 'sa'. Side question: is this a known limitation of MSDE ?

Is there an efficient way to convert such strings to Int ?

I note that the commas may actually be missing, since their presence depends on the "Digit Grouping" value in the Regional Settings of Control Panel.

In the past, I was using Sybase, and I had to use set-based queries, running against a few work fields in my table. The first query would use charindex() to find the position of the first comma, if any. The second query would pick up the portion of the string up to the comma, then another query chasing the next comma, etc. Rather painful.Hmm...maybe you could try playing around with the replace command to filter out the commas.

I tested this 1 line code in QA and it works fine.

select cast(replace('3,137,458',',','') as int).|||oops, temporary blindness ... apologies ... please ignore this question

convert( int, REPLACE( column_name, ',', '' ) )|||thanks, mate, I've just found it at the same time. Works like a charm.
Me self-learner too...

Controlling the freeze pane location in Reporting Services

So I have a report that has a header, and in the body there is a
table. The table has a heading row. When I export this report to
excel, the freeze pane is put under the header.
Is there a way to specify the location of the freeze pane when the
report is exported to excel? I would like to have the freeze pane
directly below the column header row.
One possible solution I thought of was to put the column header row
into the actual header, but I can't do that as right above the column
header row is a summary of the data in the table. This summary
references fields from my datasets. And anything that references a
field cannot be put in the header row.
Thanks in advance.On Apr 28, 8:41 am, Jesse...@.gmail.com wrote:
> So I have a report that has a header, and in the body there is a
> table. The table has a heading row. When I export this report to
> excel, the freeze pane is put under the header.
> Is there a way to specify the location of the freeze pane when the
> report is exported to excel? I would like to have the freeze pane
> directly below the column header row.
> One possible solution I thought of was to put the column header row
> into the actual header, but I can't do that as right above the column
> header row is a summary of the data in the table. This summary
> references fields from my datasets. And anything that references a
> field cannot be put in the header row.
> Thanks in advance.
As far as I know, there is not really anyway to control this. I'm
actually surprised that you have managed to get the Excel export to
maintain the freeze panes at all, as I have not seen it work
automatically after export. Sorry that I could not be of further
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Control the WorkSheet Names when export to Excel

When I exported the report to Excel File with Multiple Worksheets, can I name
the worksheets with the values of the field I group on?
--
Thanks,
Albert ChowUnfortunately, that's not supported yet.
--
Adrian M.
MCP
"Albert" <Albert@.discussions.microsoft.com> wrote in message
news:DDD282FE-DEB3-41F6-8E1D-AEED8AB4B17C@.microsoft.com...
> When I exported the report to Excel File with Multiple Worksheets, can I
> name
> the worksheets with the values of the field I group on?
> --
> Thanks,
> Albert Chow

Continuation of emailing an excel file

Hi Rafael,

I need to create a new excel file daily and then need to mail it.

The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.

Any idea why this is happening?So iam unable to schedule as a job...

Thanks,

Vani.

This is more of an integration services forum question than data access forum question. Moving this thread to SQL IS forums.|||

You might try setting the DelayValidation property true on the data flow task and the connection manager. You might take a look at this post for more information on dynamically creating Excel files in SSIS.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html