Hi,
I apologize if my message came across rude or if it was not as per
the standards of the group. I just wanted to emphasize particular text.
Having cleared that, here is what I have done. I have used Ltrim, Rtrim on
the SQL SERVER 2000 side in the insert & update statements. I had to
manually add so many LTRIM & RTRIM in the queries. Its working fine. Only
thing that bothers me is that I was not able to it in CF way. Thanks Teddy
for your input. I appreciate everyones feedback and time.
The dataset is like this. I have 84 columns in the text file seperated by
tabs and the line ends with CarriageReturn. The file could have thousands
of records. The process is like this. We upload the file to db server.
Validation is performed on the file by using the custom tag. If there are
errors then they are reported to the user. If not a stored proc is called
which bulk inserts the file contents into a holding table using a format(.
fmt) file. At midnight another DTS package is run which will move contents
from holding table to main tables. This is where I have added LTRIM, RTRIM
functions. I will try the CF way but right now its not very clear in my
mind how I will be doing it. Thats why I had to do it at database side.
Thanks,
Ajas Mohammed.
On 1/11/07, Teddy Payne <[EMAIL PROTECTED]> wrote:
>
> Ajas,
> First, capitalization inside your emails does not emphasize a point, but
> rather will turn most people away from answering your question.
>
> As for your code, you are reading a file that has a set delimeter. As
> you are probably aware, CF has several handy functions around lists. Lists
> are not data types, but strings manipulated in very much the same
> functionality that you are creating. I would validate the overall string
> that it satisfied the delimeter that I would be looking for and then after
> that, I could either just use lists if it is a small data set or I would
> convert the list to an array using ListToArray() for alrger data sets.
>
> At that point, you can use Trim() to remove white space or if you want
> more control you can REReplace() other characters that you do not want
> inside of your element.
>
> It seems to me that you are introducing complexity.
>
> Teddy
>
>
>
> On 1/10/07, Ajas Mohammed <[EMAIL PROTECTED] > wrote:
> >
> > I parse to check data validation and if any required fields are empty.
> > But as I mentioned earlier, the trim or replace or even the validation is
> > temporary since the actual file is stored on the server and the Trim/Replace
> > functions just works on the file by creating Array I think but doesnt
> > ACTUALLY SAVE/WRITE TRIMMED COL VALUES TO THE FILE. I am sorry if its
> > confusing but Please look at the code I had sent in 1st email for the Custom
> > Tag.
> >
> > Thanks. Let me know if its not clear.
> >
> >
> >
> > On 1/10/07, Teddy Payne <[EMAIL PROTECTED] > wrote:
> > >
> > > In your custom tag, are you parseing your results? If you are, you
> > > can use any number of ways to get rid of spaces. Trim() and REReplace()
are
> > > just a couple of ways.
> > >
> > > Teddy
> > >
> > >
> > > On 1/10/07, Ajas Mohammed <[EMAIL PROTECTED] > wrote:
> > > >
> > > > oh man... I was being so stupid... Heres what was happening.
> > > > The custom tag was parsing the file etc which is fine but the file
> > > > is uploaded to a server and then the custom tag does it stuff. So
> > > > technically the file with spaces is there on the server to be loaded
into
> > > > SQL SERVER.
> > > >
> > > > Here is the code :
> > > >
> > > > <!--- Upload file to shared folder. --->
> > > > <cffile
> > > > action="upload"
> > > > fileField="demographics_file"
> > > > destination="#application.demographics_folder#"
> > > > nameConflict="overwrite" />
> > > >
> > > > <!--- Save file name. --->
> > > > <cfset file_location = cffile.serverDirectory & "\" &
> > > > cffile.serverFile />
> > > >
> > > > <!--- Parse the file line by line. --->
> > > > <cf_demographicParser str_Filename="#file_location#">
> > > > So here the custom tag gets called and I check for errors in the
> > > > file.
> > > >
> > > > Now my question is, How can i make the changes made in custom tag
> > > > i.e. trim part saved in the file? maybe upload it again i.e.
> > > > overwrite old file??
> > > >
> > > > Is this doable?? Plz suggest...
> > > >
> > > > Thanks in advance...
> > > >
> > > > On 12/22/06, Ajas Mohammed <[EMAIL PROTECTED] > wrote:
> > > > >
> > > > > Thanks everyone.
> > > > >
> > > > > Douglas Knudsen --> Will replace take care of tabs also??? or
> > > > > shall i need to say chr(9) for example :
> > > > >
> > > > > str_Token = Replace(trim(mid(arguments.str_Content, int_CurrPos,
> > > > > int_NextPos - int_CurrPos)),' ','', 'All');
> > > > >
> > > > > replaced with this code
> > > > >
> > > > > <cfset str_Token = Replace(Replace(trim(mid(
> > > > > arguments.str_Content, int_CurrPos, int_NextPos -
> > > > > int_CurrPos)),' ','', 'All',Chr(9),'All');>
> > > > >
> > > > > Any suggestions?? should Chr(9) be in quotes???
> > > > >
> > > > > Ajas Mohammed.
> > > > >
> > > > >
> > > > > On 12/22/06, Teddy Payne <[EMAIL PROTECTED] > wrote:
> > > > > >
> > > > > > If the records already exists in a database, you will need to
> > > > > > run a stored procedure to scrub and clean your data. Since you are
running
> > > > > > SQL Server 2000, you can use table variables to perform this very
quickly.
> > > > > >
> > > > > > Example:
> > > > > >
> > > > > >
> > > > > > declare
> > > > > > @firstName varchar(50)
> > > > > > , @maxRows int
> > > > > > , @currentRow int
> > > > > >
> > > > > > select
> > > > > > @currentRow = 1
> > > > > >
> > > > > >
> > > > > > declare @dataSet table
> > > > > > (
> > > > > > rowID int identity (1, 1) primary key not null
> > > > > > , pkID int
> > > > > > , firstName varchar(50) not null
> > > > > > )
> > > > > >
> > > > > > insert into
> > > > > > @dataSet
> > > > > > select
> > > > > > firstName
> > > > > > from
> > > > > > tblFoo
> > > > > >
> > > > > > select
> > > > > > @maxRows = count(rowID)
> > > > > > from
> > > > > > @dataSet
> > > > > >
> > > > > > while @currentRow <= @maxRows
> > > > > > begin
> > > > > >
> > > > > > select
> > > > > > @firstName = firstName
> > > > > > , @pkID = pkID
> > > > > > from
> > > > > > @dataSet
> > > > > > where
> > > > > > rowID = @currentRow
> > > > > >
> > > > > > select @firstName = LTrim(RTrim(@firstName))
> > > > > >
> > > > > > update
> > > > > > tblFoo
> > > > > > set
> > > > > > firstName = @firstName
> > > > > > where
> > > > > > pkID = @pkID
> > > > > >
> > > > > > select @currentRow = @currentRow + 1
> > > > > >
> > > > > > end
> > > > > >
> > > > > > Kind of like that. You can scrub full tables at a time by
> > > > > > adding more columns to the table variable.
> > > > > > Teddy
> > > > > >
> > > > > >
> > > > > >
> > > > > > On 12/22/06, Douglas Knudsen <[EMAIL PROTECTED] >
> > > > > > wrote:
> > > > > > >
> > > > > > > not sure of the SLQ Server stuff, but in the CF code below
> > > > > > > you can utilise: Trim(Replace(myString,' ','')). Actually, looks
like the
> > > > > > > code is already using Trim(), just need to add the Replace() as
above to
> > > > > > > remove intermediate spaces.
> > > > > > >
> > > > > > > str_Token = Replace(trim(mid(arguments.str_Content,
> > > > > > > int_CurrPos, int_NextPos - int_CurrPos)),' ','', 'All');
> > > > > > >
> > > > > > >
> > > > > > > DK
> > > > > > >
> > > > > > > On 12/21/06, Ajas Mohammed <[EMAIL PROTECTED] > wrote:
> > > > > > > >
> > > > > > > > Hello everyone,
> > > > > > > > I need help on this one real quick. One of my colleauges
> > > > > > > > used this custom CF tag which parses a tab delimited text file
and stores in
> > > > > > > > array. How do I add functionality to this code so that it would
trim all the
> > > > > > > > spaces i.e either left or right or if value sent in txt
> > > > > > > > file has spaces?????
> > > > > > > >
> > > > > > > > If you understand the code , that will be great and if you
> > > > > > > > could pinpoint me to what needs to be done, then nothing like
it... Also any
> > > > > > > > explanation of this code will help a lot.
> > > > > > > >
> > > > > > > > I tried ltrim(rtrim(colvalue)) while inserting records but
> > > > > > > > it doesnt work in sql server 2000. Once records are inserted in
table, I am
> > > > > > > > not able to trim the records.. If you know any sql server 2000
fix for
> > > > > > > > removing extra spaces left or right or remove extra spaces for
values which
> > > > > > > > are sent with empty tabs or spaces???
> > > > > > > >
> > > > > > > > Thanks in advance..if u cant open attachment , here is the
> > > > > > > > code...
> > > > > > > >
> > > > > > > > <cfparam name="attributes.str_Filename" default="" />
> > > > > > > > <cfparam name="attributes.str_LineDelimiter"
> > > > > > > > default="#chr(13)##chr(10)#" />
> > > > > > > > <cfparam name="attributes.str_TokenDelimiter"
> > > > > > > > default="#chr(9)#">
> > > > > > > >
> > > > > > > > <cffunction name="TokenizeLine" returntype="array">
> > > > > > > > <cfargument name="str_Content" type="string"
> > > > > > > > required="true" />
> > > > > > > > <cfargument name="str_Delimiter" type="string"
> > > > > > > > required="true" />
> > > > > > > >
> > > > > > > > <cfset var ar_Tokens = arrayNew(1) />
> > > > > > > > <cfset var int_CurrPos = 1 />
> > > > > > > > <cfset var int_NextPos = 1 />
> > > > > > > > <cfset var str_Token = "" />
> > > > > > > >
> > > > > > > > <cfscript>
> > > > > > > > int_NextPos =
> > > > > > > > REFind("#arguments.str_Delimiter#|$",
> > > > > > > > arguments.str_Content, int_CurrPos);
> > > > > > > > while (int_NextPos gt 0)
> > > > > > > > {
> > > > > > > > str_Token = trim(mid(arguments.str_Content,
> > > > > > > > int_CurrPos, int_NextPos - int_CurrPos));
> > > > > > > > arrayAppend(ar_Tokens, str_Token);
> > > > > > > > int_CurrPos = int_NextPos + len(
> > > > > > > > arguments.str_Delimiter );
> > > > > > > > int_NextPos =
> > > > > > > > REFind("#arguments.str_Delimiter#|$",
> > > > > > > > arguments.str_Content, int_CurrPos);
> > > > > > > > }
> > > > > > > >
> > > > > > > > // If the line ends with a token, add
> > > > > > > > // an extra empty element to the array
> > > > > > > > if (len(arguments.str_Content) gt 0 and mid(
> > > > > > > > arguments.str_Content, len(arguments.str_Content), 1) is
> > > > > > > > arguments.str_Delimiter)
> > > > > > > > {
> > > > > > > > arrayAppend(ar_Tokens, "");
> > > > > > > > }
> > > > > > > > </cfscript>
> > > > > > > >
> > > > > > > > <cfreturn ar_Tokens />
> > > > > > > > </cffunction>
> > > > > > > >
> > > > > > > > <cfif ThisTag.ExecutionMode is "Start">
> > > > > > > > <!--- Read the file and inititialize position and
> > > > > > > > caller variables. --->
> > > > > > > > <cffile action="read" file="#attributes.str_Filename#"
> > > > > > > > variable="str_Content" />
> > > > > > > > <cfset int_CurrPos = 1 />
> > > > > > > > <cfset caller.TextParse.str_Line = "" />
> > > > > > > > <cfset caller.TextParse.ar_Tokens = arrayNew(1) />
> > > > > > > > </cfif>
> > > > > > > >
> > > > > > > > <!--- Find the next delimiter. The regular expression
> > > > > > > > "#attributes.str_LineDelimiter#|$"
> > > > > > > > finds the next line delimiter OR the end of the
> > > > > > > > string. --->
> > > > > > > > <cfset int_NextPos =
> > > > > > > > REFind("#attributes.str_LineDelimiter#|$", str_Content,
int_CurrPos) />
> > > > > > > >
> > > > > > > > <cfif int_NextPos gt 0>
> > > > > > > > <!--- Get and return the line and tokens. --->
> > > > > > > > <cfset str_Line = mid(str_Content, int_CurrPos,
> > > > > > > > int_NextPos - int_CurrPos) />
> > > > > > > > <cfset caller.TextParse.str_Line = str_Line />
> > > > > > > > <cfset caller.TextParse.ar_Tokens =
> > > > > > > > TokenizeLine(str_Line, attributes.str_TokenDelimiter) />
> > > > > > > >
> > > > > > > > <!--- Reset the current position. --->
> > > > > > > > <cfset int_CurrPos = int_NextPos + len(
> > > > > > > > attributes.str_LineDelimiter) />
> > > > > > > > </cfif>
> > > > > > > >
> > > > > > > > <cfif ThisTag.ExecutionMode is "End">
> > > > > > > > <!--- Keep looping as long as we have content to
> > > > > > > > process. --->
> > > > > > > > <cfif int_NextPos gt 0>
> > > > > > > > <cfexit method="Loop" />
> > > > > > > > </cfif>
> > > > > > > > </cfif>
> > > > > > > >
> > > > > > > >
> > > > > > > > -------------------------------------------------------------
> > > > > > > >
> > > > > > > > To unsubscribe from this list, manage your profile @
> > > > > > > >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> > > > > > > >
> > > > > > > > For more info, see http://www.acfug.org/mailinglists
> > > > > > > > Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> > > > > > > >
> > > > > > > > List hosted by FusionLink <http://www.fusionlink.com/>
> > > > > > > > -------------------------------------------------------------
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Douglas Knudsen
> > > > > > > http://www.cubicleman.com
> > > > > > > this is my signature, like it?
> > > > > > > -------------------------------------------------------------
> > > > > > >
> > > > > > > To unsubscribe from this list, manage your profile @
> > > > > > >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> > > > > > >
> > > > > > > For more info, see http://www.acfug.org/mailinglists
> > > > > > > Archive @
> > > > > > > http://www.mail-archive.com/discussion%40acfug.org/
> > > > > > > List hosted by FusionLink <http://www.fusionlink.com/>
> > > > > > > -------------------------------------------------------------
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > <cf_payne />
> > > > > > Adobe Certified ColdFusion MX 7 Developer
> > > > > > Atlanta CFUG (ACFUG): http://www.acfug.org
> > > > > > -------------------------------------------------------------
> > > > > > To unsubscribe from this list, manage your profile @
> > > > > >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> > > > > >
> > > > > > For more info, see http://www.acfug.org/mailinglists
> > > > > > Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> > > > > > List hosted by FusionLink <http://www.fusionlink.com/>
> > > > > > -------------------------------------------------------------
> > > > > >
> > > > >
> > > > >
> > > >
> > > > -------------------------------------------------------------
> > > > To unsubscribe from this list, manage your profile @
> > > >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> > > >
> > > > For more info, see http://www.acfug.org/mailinglists
> > > > Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> > > > List hosted by FusionLink <http://www.fusionlink.com/>
> > > > -------------------------------------------------------------
> > > >
> > >
> > >
> > >
> > > --
> > > <cf_payne />
> > > Adobe Certified ColdFusion MX 7 Developer
> > > Atlanta CFUG (ACFUG): http://www.acfug.org
> > > -------------------------------------------------------------
> > > To unsubscribe from this list, manage your profile @
> > >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> > >
> > > For more info, see http://www.acfug.org/mailinglists
> > > Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> > > List hosted by FusionLink <http://www.fusionlink.com/>
> > > -------------------------------------------------------------
> > >
> >
> >
> > -------------------------------------------------------------
> > To unsubscribe from this list, manage your profile @
> >
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
> >
> > For more info, see http://www.acfug.org/mailinglists
> > Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> > List hosted by FusionLink <http://www.fusionlink.com/>
> > -------------------------------------------------------------
> >
>
>
>
> --
> <cf_payne />
> Adobe Certified ColdFusion MX 7 Developer
> Atlanta CFUG (ACFUG): http://www.acfug.org
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
>
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com/>
> -------------------------------------------------------------
>
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com/>
-------------------------------------------------------------
