Reading File As One String, Then Parsing - How To Do This?
May 7, 2007
Hi,
The suggestion to do this is buried deep in one of my posts, however I still do not have a clear idea of how to do this.
I have a flat file which has several "bad rows" in it. Because file error redirection is buggy, I need a manual approach to get rid of these incomplete rows in my data file.
Phil, you suggested I read the file as one long string, then parse out the bad rows (using a script?).... however I have no idea as to how to actually do this.
I was wondering if it's possible to clarify the steps involved in doing this, or perhaps point me to an example I can look at, as I cannot seem to get around this problem on my own.
Thanks much!!
View 24 Replies
ADVERTISEMENT
Jun 7, 2002
How to remove same repeated string in a column per row from a table? Looked at
replace, stuff string functions, but none take a column name as a parameter.
Help is appreciated.
Thanks,
View 1 Replies
View Related
Apr 12, 2008
I found an article about string parsing but its done using db2
http://www.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze1.html
can anybody translate to Transact SQL specifically the example of create function elemIdx i didnt understand how he used recursion may b cuz the language is odd to me i didnt get it
Thanks much
View 1 Replies
View Related
Apr 18, 2008
I have a string that is coming from a legacy system
###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757###
How can I convert above string to comma separted values
like one below so that it can be used for IN Clause for my SQL
'T1937','U1875','U1960','U3287','U5926','U6388','U4408','T1909','U2620','U5025','U6354','U7072','U7074','U6715','U6714','U4085','U6441','U7067','U7073','U2392','U6348','U7758','U6717','U7755','U7069','U7756','U6350','U6760','U7070','D0002','D0001','U6238','U6349','U6353','U6355','F0005','U7750','U6351','U7757'
Thanks in advance
View 17 Replies
View Related
Jul 11, 2006
I have a series of strings (field name FullName) in a table (FullNames) that look like this:
FullNames
--------------
macdonald, ronald
Doe, John
I need to extract the first and last names. Here's my code:
select substring (fullname, 1, patindex( '%,%', fullname)-1) + ' ' +
substring (fullname, patindex( '%,%', fullname)+1, len(fullname) ) from FullNames
I hate having to use patindex twice in the same SELECT. Is there any way around this?
TIA,
Barkingdog
View 8 Replies
View Related
Sep 16, 2007
<p>
Hi everybody, I was hoping to get some advice something I can't quite get my head around. I have a SQL db which contains a table with ratings using the AJAX rating control.
When someone rates an object, I need to select the current rating and then use those numbers to;
- calculate the new average
- add new score to total score
- increment number of votes by one.
I thought this can be best achieved using the SELECT statement and then parsing the SELECT string. (is the string comma separated?)
using each array, i'd need to convert this into integers and then do the calculation. and re-upload the data to the ratings table (using the UPDATE statement).
Is this the best way of proceeding? I have tried initially to write the code using three sql statements. But that would mean to many requests from the server, right?
Below is the conde I have writting already.int myrating;
myrating = Rating1.CurrentRating;string getscore = "SELECT " +
"RatingScore" +"FROM Rating " +
"WHERE ItemID= '" + _ItemID+ "'";string getcount = "SELECT " +
"RatingCount" +"FROM Rating " +
"WHERE ItemID = '" + _ItemID + "'";string getaverage = "SELECT " +
"RatingAverage " +"FROM Rating " +"WHERE ItemID = '" + _ItemID + "'";
int _ratingscore;int _newscore; _ratingscore = int.Parse(getscore);
_newscore = _ratingscore + myrating; //add new rating score to old scoreint _ratingcount;
int _newcount;_ratingcount = int.Parse(getcount);
_newcount = _ratingcount + 1; //increase count by 1int _ratingaverage;
int _newaverage;_ratingaverage = int.Parse(getaverage);
_newaverage = _newscore / _newcount; //calculate new average rating
otherwise
otherwise would i be best off to do the following?...
string[] dbRatings = SQLstring.Split(',');
??
Any help would be appreciated.
Many thanks in advance.
Phil
</p>
View 2 Replies
View Related
May 21, 2008
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure wher eit occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Item) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
Procedure:
Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis]
(
@stateList CHAR(2),
@employerIdList VARCHAR(4000),
@payerIdList VARCHAR(4000)
)
AS
SELECT
p.PAT_ID,
p.PAT_FirstName,
ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,
p.PAT_LastName,
p.PAT_Gender,
CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,
p.PAT_AddressStreet1,
ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,
p.PAT_AddressCity,
p.PAT_AddressStateProvince,
p.PAT_AddressPostalCode,
ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,
p.PAT_PhoneNumber,
ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,
ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
ISNULL(p.PAT_Comment,'') AS PAT_Comment,
ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,
ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,
ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,
ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,
ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,
p.PAT_LastEandMCodingDateTime,
p.PAT_Desceased,
p.PAT_PCP_ID,
p.PAT_LastUpdatedDateTime,
ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,
ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,
ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,
ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,
ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,
ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
pcp.PRIMCP_ID,
ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,
ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,
ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,
pcp.PRIMCP_DateDeactivated,
ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,
ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,
ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,
ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,
ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,
ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,
ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,
ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,
ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,
ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,
ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,
ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,
ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,
pati.PATINS_InsuranceTypeID,
ISNULL(pati.PATINS_Account,'') AS PATINS_Account,
ISNULL(pati.PATINS_Group,'') AS PATINS_Group,
ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,
ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,
ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,
ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,
ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,
ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,
ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,
ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,
ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,
ISNULL(ip.INSP_Name,'') AS INSP_Name,
ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,
ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,
CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,
ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,
CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,
CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate
FROM dbo.patient AS p
LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID
LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID
LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID
LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1
LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID
LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID
WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,','))
AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))
AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))
Is there a faster / more efficient way to accomplish the above?
Any insight would be appreciated!!
View 9 Replies
View Related
May 18, 2015
I have a table Alert_Types with these fields :
alert_id,
alert_source,
body,
Now whenever a new alert is registered record goes in table like
alert_id = 1,
alert_source= document_detail_view,
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.the converted string was then sent forward using a webservice.now my requirement is to add conditional values in body field e.g:
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
IF isnull(@rate, 0) > 0 Rate: @rate
IF isnull(@rate, 0) > 0 Amount: @amount
how can i parse strings like this. I'm open to change format of values for body field.
View 9 Replies
View Related
Jul 3, 2013
parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.
declare
v_sql VARCHAR2(2000);
v_pos INTEGER;
v_differentiator VARCHAR2(10);
[code]...
View 4 Replies
View Related
May 21, 2008
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure where it occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL ZERO
-- FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Item) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
--------------------
...and the stored procedure:
CREATE PROCEDURE [dbo].[RPTPatientAnalysis]
(
@stateList CHAR(2),
@employerIdList VARCHAR(4000),
@payerIdList VARCHAR(4000)
)
AS
SELECT
p.PAT_ID,
p.PAT_FirstName,
ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,
p.PAT_LastName,
p.PAT_Gender,
CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,
p.PAT_AddressStreet1,
ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,
p.PAT_AddressCity,
p.PAT_AddressStateProvince,
p.PAT_AddressPostalCode,
ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,
p.PAT_PhoneNumber,
ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,
ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
ISNULL(p.PAT_Comment,'') AS PAT_Comment,
ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,
ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,
ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,
ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,
ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,
p.PAT_LastEandMCodingDateTime,
p.PAT_Desceased,
p.PAT_PCP_ID,
p.PAT_LastUpdatedDateTime,
ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,
ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,
ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,
ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,
ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,
ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
pcp.PRIMCP_ID,
ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,
ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,
ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,
pcp.PRIMCP_DateDeactivated,
ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,
ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,
ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,
ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,
ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,
ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,
ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,
ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,
ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,
ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,
ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,
ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,
ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,
pati.PATINS_InsuranceTypeID,
ISNULL(pati.PATINS_Account,'') AS PATINS_Account,
ISNULL(pati.PATINS_Group,'') AS PATINS_Group,
ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,
ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,
ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,
ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,
ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,
ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,
ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,
ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,
ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,
ISNULL(ip.INSP_Name,'') AS INSP_Name,
ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,
ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,
CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,
ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,
CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,
CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate
FROM dbo.patient AS p
LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID
LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID
LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID
LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1
LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID
LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID
WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,','))
AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))
AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))
Is there a faster / more efficient way to accomplish the above?
Any insight would be appreciated!!
View 15 Replies
View Related
Oct 10, 2007
I can't figure this one out. I don't have enough knowledge of the string functions I guess.
I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1.
One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1:
"\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver"
How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value?
Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package
In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example.
The actual string could be 1 to 3 characters long, even though this example has it as 3 long.
Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore.
Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points.
Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View 1 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
Apr 3, 2007
Hi,
I have a flat file, comma-delimited, with strings in double-quotes.
In the connection manager for the file, I have specified that the Text Qualifier = ""
However, in the preview tab, it still shows the strings as surrounded by the quotes, e.g. "mycol1" whereas it should show mycol1 without the quotes.
Next, when I examine the data in the database after the load, it's messed up there also.
"mycol1" ends up in the database as "mycol1
"mycol2" ends up as "mycol2
This is not right.
I have format set to delimited, header row delimiter crlf, etc.
Any ideas?
Thanks
View 3 Replies
View Related
Feb 16, 2007
Wondering what's the preferred method for this. I've got a scenario that a user is updating some content on a page and I need to update my word catalogs for my search feature. I have some code currently to filter out words that are too small, make sure there are no duplicates and to count how many occurrences there are of each. What I'm wondering is, does it make more sense to do a loop in my code to run all the insert commands to place the new words in the database, should I try sticking them together in one string and parse them when they get up there or is there a better method someone can suggest?
View 1 Replies
View Related
Nov 5, 2014
I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
View 2 Replies
View Related
Jul 21, 2014
I want to grab some key value pairs from the text in sql column
e.g.
some text[Key1=Val1]some text[Key2=Val2][Key3=Val3]some text
I want a function which takes Keyname as input & returns the value related with it if found.
GetValueFmKey('Key1') should return Val1 and like on.
View 1 Replies
View Related
Aug 7, 2015
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
View 5 Replies
View Related
Jun 4, 2004
I want to read a String - character by character.I mean If the string is 'SAMPLE'
then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality.
I hope I am clear.Is there a way that I can read it?
View 1 Replies
View Related
Jul 27, 2006
Hi,
I want to read xml from a string and save it in SQL. Can anyone help me plz.
Regards,
View 7 Replies
View Related
Jan 18, 2007
Hi, i'm writing a SOCKET Port Listener for a Database, it must be multi-threaded and listen on a port for a record that when it comes in, it must write the record to the SQL database (MS SQL Server). I've got the listener to read the data over the port already and write the record into a string which i have already sliced up. Now i need to create a connection to the database and insert the variables into the database.
If Someone will please be able to give me a rough idea of how i could accomplish this with some sample code, then i will be greatful, i'm new to C#, but here is my code that i have so far.
//This is the Connection that i have made and where i am currently stuck, i dunno how to go further. Any help will be welcome.
public class ConnectionToMSDatabase
{
public void InsertDataIntoDatabase(string TableName, string connectionString, string dataFields)
{
string InsertSQLStatement;
InsertSQLStatement = "INSERT INTO " + TableName + " VALUES (" + dataFields + ")";
OleDbConnection ConnectionToDatabase = new OleDbConnection(connectionString);
ConnectionToDatabase.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = ConnectionToDatabase;
command.CommandText = InsertSQLStatement;
command.ExecuteNonQuery();
command.Connection.Close();
}
}
//Here is my connection string, all the retrieved data is stored in a string array call fullRecord
ConnectionStringToDatabase = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=" +
"Administrator" + ";Initial Catalog=FORGE;Data Source=FORGE";
View 4 Replies
View Related
Dec 3, 2007
Hi:
I need to parse an regularly outputted rtf file and was wondering if it is possible in SSIS. I am trying to use the flat file connection manager to do this.
Now, I can't treat tab stops in an rtf like tab stops in a csv, since when you treat an rtf as a text file, you see the format code of the rtf. If I open the rtf in a text editor, the entire file is one line, with lines breaking with:
par}
Columns are tab delimited in the rtf, and they look like this when you treat the rtf as a text file.
plain abfs16f4cf0cb1
(or something like that, the word "tab" is the important part.)
So I use the "plain ab" part to delimit in SSIS, since that is consistent (planning to parse out all the garbage later on). The problem is, sometimes lines don't have a "city" and "state", so it "tabs" right over to the next field. So like this (looking in MS Word):
Phone <tab> City <tab> State <tab> Date <tab> Other fields.....
847-111-2222 <tab> Omaha <tab> NB <tab> 9/14/2007 <tab>
222-222-3333 <tab> 9/14/2007 <tab>
555-121-1212 <tab> Houston <tab> TX <tab> 9/14/2007 <tab>
Now, if you treat an RTF as a text file, it has only one "plain abfs16f4cf0cb1" after the phone number, so even for the missing line there is only one tab, not 3. This is because in the beginning of the row tabs for each row are defined like this:
tql x90 ql x840 ql....etc...
with "tql" and "tx" tags basically saying where all the tab stops are for that row. So for the row above with missing info, it lists fewer tab stops. So the "date" (and associated garbage) ends up under "City" for this row. All of the "Houston" row's data starts appearing in the sql server output table's 2nd last field, as you might expect.
Any suggestions how to pull this in in SSIS during the transformation? I could deal with it after I pull it in, I still have all the data. I'm thinking the logic to do this could be complicated though. I take the data out of the last two fields of the missing row into some other table, use UPDATES to shift the values 2 fields to the right, and then figure out a way to take the data I just put in a temp table back in, but it all sounds a bit complicated.
Let me know if this makes sense--I've almost got it going, I just need to sort this last bit out.
Thanks,
Kayda
View 4 Replies
View Related
Jan 19, 2008
i am trying to read a qfx file from quicken.
it looks like xml, but its not, but i cannot figure out how to grab what ive got to parse the line.
i put this into a derived column, but its not getting it
SUBSTRING([Column 0],FINDSTRING("<STMTTRN>",[Column 0],1),FINDSTRING("</STMTTRN>",[Column 0],1))
because inside the data, it lools like that's what brackets a tranasction; the data looks like this and varies by trntype, but the columns are tagged like so
<STMTTRN>
<TRNTYPE>POS
<DTPOSTED>20070129160000
<TRNAMT>-0000000000026.50
<FITID>20070129011
<NAME>SUNOCO
<MEMO>01/24 ENGLWD CLIFF NJ 8015V200006
</STMTTRN>
<STMTTRN>
<TRNTYPE>POS
<DTPOSTED>20070129160000
<TRNAMT>-0000000000023.47
<FITID>20070129012
<NAME>KFC
<MEMO>01/26 NANUET NY 8015V215116
</STMTTRN>
i tried the xml transform and unpivot, but have not cracked it.
thanks for any light you can shed
drew
View 1 Replies
View Related
Dec 4, 2014
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
View 7 Replies
View Related
Dec 5, 2007
I have a tab delimited file with 122 columns. Can any one let me know if there is a better way of parsing/extracting few columns (say about 15) from the file and loading it into a table using SSIS.
View 1 Replies
View Related
Mar 19, 2008
Hello all,
I have a question regarding importing text file data into SQL Server. I'm hoping someone can point me in the right direction, as my searches haven't turned up anything specific enough.
I'm trying to parse a large (24MB) text file. It's a fixed-width file, with multiple columns. I need to parse this file, check if a record already exists, and then import the data into the database. But I don't need to insert every column. There's only a few columns from the file I need to insert. This parsing also needs to occur at regular intervals (daily).
I looked at BULK INSERT, but I can't find an example that uses only some of the columns. Every example uses all columns, and the file is delimited, not fixed-width.
Is there anything within SQL Server that can accomplish this? I haven't turned up anything that will solve my problem. The only other solution I can think of is an application that parses the file for me and inserts the data into the database. But can I schedule that application to run every night at midnight (for example) through SQL Server?
I'm not too familiar with SQL Server, so I appreciate any help offered.
Thanks,Jay
View 7 Replies
View Related
Nov 28, 2007
Hello,
I have a file that looks like this:
Summary
A ABCD
A Category MarketValue Margin
A category1 1.0000000 1.000000
A category2 2.0000000 2.000000
H Totals Total Cash Net
H 2.00000 200000 2000000
Another Summary
B BCDE
B Activity MarketValue Margin
B activity1 3.00000 3.000000
B activity2 4.00000 4.000000
The items in blue are headers. I don't want to capture those. However, I want to capture all the data in black, and put it into 3 separate tables (or maybe the same table, under the appropriate column names)
This situation differs from anything I've done before in that you can't identify what row contains what data by what's in the row itself. That is, what's in the data rows is random and subject to change. So you can't search the row itself to determine which table it goes to.
However, if there's a way to capture all the rows after a certain header before the header changes again, that might work.
That is, get all rows between A Category MarketValue Margin and H Totals Total Cash Net
and
get all rows between H Totals Total Cash Net and Another Summary
and
get all rows after B Activity MarketValue Margin
Any examples of how I might script this?
Thanks
View 2 Replies
View Related
Dec 5, 2006
Hello All!
I know this has come up before and I have tried several of the solutions found within the forum but I just can't seem to import my file correctly and could use some input, please.
Sample file (less fields than actual file):
Name (str), Phone# (str), Description(str), Resolved(bool), Met(bool)
"Kay, Mary","123-4567","Used a "."not a"," in text", "1", "1"
The text is qualified with " and columns delimited with commas but the description field has embedded quotes and commas. Normally it works except if there embedded quotes and commas.
I have tried unqualified data and undouble, but that does not work either because of the embedded commas in quotes.
Do I need to do something before the data flow? Do I need to do custom code similar to undouble (I tried modifying undouble but using unqualified fields caused the source file to not like the data and go red)? Should the row be read as one field and parsed?
Thanks in advance for any help you can give!
View 12 Replies
View Related
Feb 28, 2007
I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions? Thanks!
View 1 Replies
View Related
Oct 1, 2015
I have a CSV file with roughly 6 million rows. The file is unstructured; that is, some rows have 5 fields, others have 15, and there are as many 50 fields in one row.
I am using bulk insert to read the entire file into a table in database, with each row being a database record. With that, I have one column that contains a row of comma delimited fields. All fields are character string and I want to find a quick way of parsing each row and placing each comma-delimited value in a column. For example:
CREATE TABLE MyTable
(
CSVString varchar(1000),
C1 varchar(20),
C2 varchar(20),
...
C50 varchar(20),
)
Column CSVString contains the a CSV row (I don't know how many filelds (no. of commas + 1) in the row, but if the row contains 10 fields, I need to populate columns C1-C10. If the row has 15 fields, I populate columns C1-C15.
How can I do this in a very efficient way? I tried CTE but performance was not very good.
View 8 Replies
View Related
Jun 7, 2007
Hi,
I have a situation where I'm having to extract key data from a financial file. Problem is, the columns are not nice and tidy.
Basically the file looks like this:
row 1: "788","Company","OPENING BALANCE:", 2084587.76
row 2: "313947","04/01/07","3","CS","FF", 170.00,"AZT","XYC INC", 20.8, 351.00
row 3:"788","06/06/07 CLOSING BALANCE:", 206203893.03
So, I'm going to need to get the OPENING BALANCE and CLOSING BALANCE figures, as well as all the data in between, ie) row 2 through n.
Does anyone have an example of a script that can be used for extracting very specific values from a file?
I have a script that checks for incomplete rows, but it is not sophisticated enough for this situation.
Thanks much
View 9 Replies
View Related
May 12, 2006
Hello All,
I have come across this issue with the Flat File Source when the delimiter is set to a comma.
"""KAILUA KONA,HI""","CA",
In the data snippet above and with the setting of using a comma as a column delimiter
and a " as the text qualifer.
the data will be parsed in this fashion:
"""KAILUA as a column:
HI""" as a column
CA as column
when it should be
"KAILUA,HI" as a column
CA as column.
Is there a way to let the Flat File Source to let it know not to parse the data in multiple quotes ?
Thank you
Eric Flores
View 5 Replies
View Related
Feb 9, 2000
I have some records that have been deleted. I need to find out who did it and to do that I need to read the logs. Are there any utilities that will allow me to read login 7.0? How about 6.5?
Chris
View 2 Replies
View Related
Jun 10, 2002
Hi
How can I read an ini file entry and pass this parameter to a stored procedure which will be run in a DTS Package?
Thanks for the input
mipo
View 2 Replies
View Related
