Transact SQL :: Delimited Data Extraction
Jul 29, 2015
I have a database that has a table with a lot of delimited data. I need to extract some of this data, preferably into a sql view, that I can name the fields. The data looks like this:
|7700|6|0|work center|24|8|XLT
What I need is:
field name 1= 7700  field name 2= 6  field name 3= 0 field name 4= work center  field name 5=24, ETC
I'm not sure how to count the delimiter | and get the data between | & |. Or maybe that isn't the way to do this.
Here is a sample of what I did in Crystal Reports but I need a view to make this usable.Â
Split ({usrc1.application_data}, "|") [22] Â This gives me the data between delimiters 22 & 23.
View 5 Replies
ADVERTISEMENT
Nov 12, 2007
Hi guys!
How can I extract data from a SQL server 2000 to TEXT Files. On which the data length that I have is different from the new format.
sample: database1: employee_ID = 10
database2: ID_employee = 8
May I know what query can I execute to provide an 8 length for the incoming database?
Thanks! Hope u'll help me!
View 5 Replies
View Related
Nov 13, 2007
Guys can you help me to extract my database data to text files using different format:
sample
ClientId9ClientNo12
How can I extract ClientNo with a length of 12 to ClientID with a length of 9 to text files.
View 3 Replies
View Related
Jan 31, 2008
Hi,
I am new to the whole concept of ASP.NET and codebehind (i am using c# and i am also new to that) i currently trying to get my codebehind to connect to a SQL Server express 2005 database on page load however i am getting this error thrown back at me when i run the aspx page on my localhost,
Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'.Source Error:
Line 18: {
Line 19: SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
Line 20: conn.Open();
Line 21:
Line 22: }
Source File: g:Level HRevivalDefault.aspx.cs Line: 20
i believe it has something to do with the conn.Open() funtion or the connection string it's self the acutal full code is here,
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
conn.Open();
}
secondly once i get the connection working i want t be able to check a user email and password against those in a table to allow the log in, through my experience with PHP i would of done this by putting what the user entered into a variable and then checking the variables against an sql query, how do i go about this with aspx and C# my textfield is called uname and my passsword is call pword. Any help of example code would be greatly appreciated.
Many Thanks
S_ainley87
View 6 Replies
View Related
Sep 24, 2014
I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586
I want result on the basis of first 2 digit the repeatation of code is count as 1.
code1 code2 count
12 34 2
21 34 1
45 78 1
View 2 Replies
View Related
Oct 8, 2008
I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.
For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.
Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
Â
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?
View 24 Replies
View Related
Mar 22, 2007
So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.
This first one returns no results at all when I should get about 7 rows or so.....
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS'
AND student.name = Attendance.studentid
This one is returning all of the names in the database 6 times each.
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS' order by name
I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!
View 4 Replies
View Related
Apr 12, 2007
Hi,
Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).
Thanks
Amir
View 7 Replies
View Related
Mar 19, 2007
Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorB
View 6 Replies
View Related
Jul 9, 2015
All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.
Data in Tables
Test1, Test2, Test3
Test4;Test5;Test6
Desired output
Test1
Test2
Test3
Test4
Test5
Test6
Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.
View 7 Replies
View Related
Aug 4, 2015
I am simplifying things here. Basically I have:
SELECT ......... (my selection criteria)
   where Id in (@Haulers);
@Haulers is supposed to be a comma delimited list.  What do I need to do to make it filter correctly? Â
View 13 Replies
View Related
Dec 1, 2014
I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
View 9 Replies
View Related
May 12, 2008
Hi all,
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver.
When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below:
ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine.
Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
Thanks and Regards,
Nirmal
View 2 Replies
View Related
Sep 6, 2006
Currently we have a DTS that extracts data from a cube to a SQL Server table and then from SQL Server to a flat file.
This is taking a considerable time is there a faster way of doing this?
Thanks.
Mark.
View 1 Replies
View Related
May 12, 2015
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
View 13 Replies
View Related
Jul 21, 2015
I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
View 3 Replies
View Related
Jan 21, 2004
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
Thanks
View 14 Replies
View Related
Mar 11, 2008
I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob
View 1 Replies
View Related
Jan 8, 2008
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'.
Eg:
http://www.simpletech.com/upgrades/aopen/s661fxm/s661fxmintelp4/
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number.
I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server.
Please..advice me.
Thanks in advance.
View 17 Replies
View Related
Feb 15, 2007
Hi,
I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:
movie_title | movie_rating | category_name--------------------------------------------------------------------------------------------------Pirates of the Carribean | PG-13 | Action,Adventure,FantasyEvil Dead | Unrated | Horrorand so on ...I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift? It all seems counter-intuitive. Thoughts?
Thanks :o)
View 5 Replies
View Related
Aug 15, 2004
hi,
Im interfacing with an app that imports/exports data in a very specific hash-delimited format
like this:
#column1=Value1#column2=Value2#column3=Value3 etc.
How do i import this into a dataset so i can in my db.
Obviously ideal way would be to use the Jet text provider, but im not sure if the schema.ini can be setup to deal with this kind of data.
If anyone has ANY way of me getting this data into dataset please help.
View 1 Replies
View Related
Mar 29, 2001
SQL7 SP3
Hi.
I have a table in which I want to create a delimited list of values from one field which I will be using for validation.
How can I do this without using a cursor to build the string. The SQL would be something like:
SELECT *
FROM myCrossRefTable
WHERE SourceTable = 'FieldValueList'
I'm looking to return on string like -
~value1~value2~value3~value4~value5
Thanks,
Craig
View 2 Replies
View Related
Sep 8, 2005
Hi,
I need to export data from SQL server 2000 database into text file uisng ç Delimited. Because my destination database will be teradata. Could you let me know if you have any method for this.
Thanks
View 1 Replies
View Related
Oct 19, 2007
Hi,
I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file.
So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited. I tried finding examples but couldn't see what I should do to have a comma delimited file. (I'm getting a bit tired now, so I might be missing something!)
I have created a bat file containing the following code:
bcp "TestDB..GA_FSM_DCSF_Extract" out "C:GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:error.log" -c -T -S srckvzg2j -r
Any help / pointers would be much appreciated.
Thanks,
Henrik
View 8 Replies
View Related
Feb 29, 2004
Hello, i need to load some data from a long comma delimited text file, How can a i do that, using t-sql?, thanks for your help!!!!!
View 5 Replies
View Related
Jun 13, 2008
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl(
invoicenum int,
salesreplist [text] NULL,
reprate1 int NULL,
reprate2 int NULL,
reprate3 int NULL,
reprate4 int NULL,
reprate5 int NULL,
)
Here is some sample data:
1 A 0 0 0 0 0
2 0 0 0 0 0
3 I~~~~ 15 0 0 0 0
4 A~B 5 5 0 0 0
5 I~F~T~K~G 5 5 2 2 2
6 A~B
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
Can anyone suggest a solution?
View 3 Replies
View Related
May 21, 2007
Hi,
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1st row
1. Id = 1
2. GraphPoint = 023 (float data type)
2nd row
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
Thanks.
View 3 Replies
View Related
May 10, 2007
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
ID Products
--------------------------------------------------------------------------
1 12, 45
2 10, 20
and I would like to have results as
ID Products
--------------------------------------------------------------------------
1 12
1 45
2 10
2 20
I would appreciate if someone could offer me some guidance here.
View 11 Replies
View Related
May 13, 2015
I have a problem with a PIPE "|" delimited flat file. I have a column "Description" in which we get a string in which we have PIPE "|" as data. How we can skip this and load it as a data into the column Description.
View 7 Replies
View Related
Jan 15, 2001
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
View 2 Replies
View Related
Sep 3, 2015
I am struggling on using bcp to import data. Here is my steps:
1. I created a Test database on my localhost
2. In the Test database, I created a Test table, the query is here for your convenience:
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] [varchar](128) NULL,
[IP] [varchar](15) NULL,
[OS] [varchar](128) NULL,
[Code] ....
I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:RXieSQLTest.fmt –T
Here is the format file:
9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
[Code] ...
The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:
network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:RxieSQL20150902FullTest.rpt -f C:RxieSQLTest.fmt -T
I got the following error messages:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
[Code] ...
I do want to mention here is the rpt file contains three BOM characters EF BB BF at the beginning of the file.
View 9 Replies
View Related
Aug 9, 2007
The problem is that I have (for example) following data
AA AA LH
BB BB ML
CC CC QA
DD DD PS
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
View 7 Replies
View Related
Feb 26, 2007
I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).
Is it possible to use a single flat file data source that will work with all of the different width files?
View 1 Replies
View Related
