Looping Through Data And Outputting Text Files
Mar 20, 2008
I have this following code here...
Code Snippet
SET @SQL = 'Select * FROM IdentipassNew.dbo.CBORD_Interface_Final'
SET @BCPBody = 'bcp "' + @SQL + '" queryout "d:smartcardcbordudfcbordbody.txt" -T -fc:cpbody.fmt'
Problem is, there is over 85,000 records in that set and that is too big for the text file, so I was wondering if it would be possible to select like 30,000 records output those to a text file, then select the next 30,000 and create another file, then finally get the remaing records and put that in another text file. Can someone point me in the right direction as to how to accomplish this?
Thanks in advance.
View 3 Replies
ADVERTISEMENT
Sep 1, 2007
Currently looping through the set of flat files like CHK0604, CHK0611, CHK0618, and CHK0625 from the source folder C:SOURCE
OBJECTIVE within the flat file if any records/rows cause error i have to move the bad data into separate folder C:ERROR
STEPS TAKEN
1) In FOREACH LOOP component i specified the variable User:: sourceFilePath for my source file CHK0604 etc. location C:SOURCE. The loop walkthrough each file in C:SOURCE and if no error then moves the flat file into another folder C:ARCHIVED. This task is perfectly working.
2) Within the dataflow I am diverting the the bad rows from "conditional component" into "Flat File Destination" Component.
3) "Flat File Destination" Connection manager i set the expressions as @[User:: sourceFilePath] +"_Error.TXT".
ISSUE
Because of point (3) the error file is created in the SOURCE flat file location C:SOURCE.
QUESTION
1) My error file name should be CHK0604_Error, CHK0611_Error, CHK0618_Error, CHK0625_Error created in another folder C:ERROR.
2) How to move the bad data into another directory while looping through a set of FLAT FILES ?
3) If i have to create another variable like @[User:: ErrorFilePath] where to create ? How to use the source file title as the title of error file.?
Thanks for the help
View 4 Replies
View Related
Jan 19, 2008
HI:
I am trying to loop through files in a directory. The tricky part here is that the last part of the filename has seconds, and I never know what that is. I need to use some sort of wildcard I think here. Basically I have a directory with these files:
Export20080112_01_00_08.csv
Export20080113_01_00_06.csv
Export20080114_01_00_03.csv
Once you have got the YYYYMMDD part you have a unique file, so the rest I don't care about. If I could just open the files the way you use a wildcard when you do Windows file searches I could store these in a table:
Export20080112*.csv
Export20080213*.csv
Export20080114*.csv
then use the For Each Container in SSIS for ADO and loop through the filenames. The other option is to use the For Each File but that has to loop through all the files.
Is there a good way to do this using a wildcard?
Thanks,
Kayda
View 3 Replies
View Related
May 22, 2006
I'm downloading zip'd files and would like to loop through each file that was downloaded. I'd also like to unizip each file and append all of them to one file. I have a dos batch that is fairly simple and would like to emulate it using ssis. Here is what the dos batch file looks like.
DATE /T >%TEMP%D.txt
FOR /F "usebackq tokens=2,3,4 delims=/, " %%i IN ("%TEMP%D.txt") DO SET fname=TAMF_162%%i%%j%%k-%%k.zip
ECHO xxx>zzzzz
ECHO xxxxx>>zzzzz
ECHO BINARY>>zzzzz
ECHO GET %fname%>>zzzzzz
ECHO QUIT>>zzzzz
FTP -s:zzzzzzz ftp.aaaaa.com
PKUNZIP -o -xxxxxxx downloadedfile_1~1.ZIP
DEL TAMF_1~1.ZIP
DEL zzzzzzzz
EXIT
View 11 Replies
View Related
Mar 31, 2008
how to loop through files by date using a for each loop container
for example if i have files with names
filex_20080405103050
filex_20080405103055
filex_20080405103520
then the file filex_20080405103050 should be picked and loaded first.
View 4 Replies
View Related
Jul 20, 2005
Hi AllIm generally a vb programmer and am used to referencing multiple recordsreturned from a query performed on an sql database and im trying to movesome functions of my software into sql stored procedures. So far ive beenable to move the functions relatively easily but im unsure about how tooutput multiple values from an sql stored procedure. By this i mean forexample one of the stored procedures may take your username and return thecontents of a single field in a record of one of the tables, but i wouldlike to be able to return for arguement sake the contents of a single fieldfrom two records if possible. Under VB im used to referencing the recordsetwith a (1) after it to reference the corresponding record from the query. Iwas wondering if there is a way to do something similar to this with storedprocedures if possible ?Thanks for any help
View 15 Replies
View Related
Mar 10, 2008
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
View 1 Replies
View Related
Jan 22, 2006
Hi,
I have problem I'm hoping someone can give me some pointers with.
I need to load data from several text files into one table. The format of the files are simple - each line is comma separated, with double quotes around each element e.g.
"parameter 1","value 1","parameter 2","value 2"....
"parameter 12","value 12","parameter 13","value 13"...
However, the files themselves will have different numbers of columns e.g file 1 may have 8 columns, file 2 may have 16 columns.
I'm going to load the data into a table that has at least as many columns as the longest file. The table columns are all varchar, and are named simply as [Col001] [Col002] [Col003] etc...
The first two columns of this table must be left empty during the load (I use these later on), so the data entry will start at [Col003].
My question is what is the best way to do this? I thought perhaps using a BULK INSERT in a stored procedure might do the trick, but I haven't used it before and haven't got very far. I gather another approach might be to use bcp utility. Someone has also suggested a DTS package, but the filenames will be suffixed with current date/time stamp, so i don't think that will work.
My preferred appraoch would be the BULK INSERT..but i'm open to any pointers.
Many Thanks
Greg
View 2 Replies
View Related
Aug 4, 2006
In MySQL, I use "LOAD DATA INFILE 'my_path/data_file.txt'" to load datafrom a plain text file. Of course, the actual statement is a bit morecomplex once one considers the various options (e.g. comma delimited vstab delimited, record termination strings, &c.).My problem is that I have yet to find the equivalent within MS SQLserver. I did find a LOAD statement in T-SQL, but at first glance itseems to do something completely different.How does one normally load data from a plain text file into a table inMS SQL? This needs to be relatively efficient since, once inproduction, it will be used to load tens of megabytes of data into thedatabase (a feed from a data provider). Is it flexible enough to allowme to specify whether the fields are tab delimited vs comma delimited,optionally enclosed by quotes, record termination charactors, &c.?All I really need is direction to the right part of the T-SQL reference(MS SQL Server 2005). Anything else, such as examples, is icing on thecake.ThanksTed
View 2 Replies
View Related
Jun 6, 2005
Hi,i wanna develop an web-database application with ASP.NET,C#, SQL server 2000.i already have some data whichs been in text format(text file) and now, i want to import the same into my database.the problem is, the text file has got many line breaks and also its not well formated to import it using DTS.Can any one help me out in importing the same.thanks in advance
View 3 Replies
View Related
May 1, 2002
I have 6000+ text files, average size 400 kb, that I need to load into 1 table in Sql Server 2000. Does anyone know of an easy way to do this? I thought I would just write a little VB app to loop through all the files in the directory and insert the data into an existing table but there must be an easier way.
Any help would be appreciated.
View 1 Replies
View Related
Feb 17, 2007
I need to extract data from text files (around 200) and import into sql server tables. I tried using SSIS foreach loop container but could not manage it. Can anyone guide me how this can be done?
All help appreciated.
Thanks,
View 4 Replies
View Related
Jun 17, 2015
writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?
View 1 Replies
View Related
Sep 10, 2001
Hi all,
I got a situation here.....
From a source table (in SERVER1) I get ids of candidates and from another source (in SERVER2) I get their CVs (text files stored in various Folders). My destination table (in SERVER3) has two fields, CandidateId & CandidateCV.
I have to transfer the data in above fashion for nearly 1 million records.
How can I write a DTS package which picks up the text file from SERVER2 based on the CandidateId which comes from SERVER1? Probably I need some kind of looping mechanism which changes the candidate id & his CV file.
Can anyone help???
Thanks...
View 2 Replies
View Related
Feb 12, 2008
I am learning SQLServer Integration Services.
I created a file People.txt containing firstName, LastName seperated by a pipe.
------------------content-----------
John | Doe
Mike | James
Adam | Smith
-----------------------------------------
and another one called gender.txt
------------------content-----------
M
---------------------------------------
I will would like to create integration services package that compines each record of the first file with the record of the second file and inserts the result into table.
--------------Result table content------------------
John
Doe
M
Mike
James
M
Adam
Smith
M
-----------------------------------------------------------
Thanks
View 5 Replies
View Related
Jun 24, 2014
I have a master table containing details of over 800000 surveys made up of approximately 400 distinct document names and versions. Each document can have as few as 10 questions but as many as 150. Each question represents one row.
My challenge is to create a separate spreadsheet for each of the 400 distinct document names and versions containing all the rows and columns present in the master table. The largest number of rows would be around 150 and therefore each spreadsheet will not be very big.
e.g. in my sample data below, i will need to create individual Excel files named as follows . . .
"Document1Version1.xlsx" containing all the column names and 6 rows for the 6 questions relating to Document 1 version 1
"Document1Version2.xlsx" containing all the column names and 8 rows for the 8 questions relating to Document 1 version 2
"Document2Version1.xlsx" containing all the column names and 4 rows for the 4 questions relating to Document 2 version 1
I assume that one of the first things is to create a lookup of the distinct document names and versions assign some variables and then use this lookup to loop through and sequentially filter the master table data ready for creating the individual Excel files.
--CREATE TEMP TABLE FOR EXAMPLE
IF OBJECT_ID('tempdb..#excelTest') IS NOT NULL DROP TABLE #excelTest
CREATE TABLE #excelTest (
[rowID] [nvarchar](10) NULL,
[docName] [nvarchar](50) NULL,
[Code] .....
--Output
rowIDdocNamedocVersionquestionblankField
1document11q1NULL
2document11q2NULL
3document11q3NULL
4document11q4NULL
5document11q5NULL
6document11q6NULL
[Code] .....
View 9 Replies
View Related
Mar 11, 1999
Hi,
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
Any takers,
Thanking you in advance.
Bye for now,
Himauhu
View 1 Replies
View Related
Feb 2, 2010
I am transferring data from Oracle tables into text files, and facing these errors.
1. I have a varaible working as an expression and my query goes into that variable and onwards that variable is passed to dataflow task, which parse the query. my query is simple saying "Select * from PLS.ABC" where PLS is my schema, but the task generates error "Opening a rowset for "Select * from PLS.ABC" failed. check that the table exists in the database. and surely the table is there.
2. I have a foreach loop that iterates through all the table names and the table names are passed onwards to the varaible query, the dataflow task inside the foreach loop gets the variable query and will generate text files based on tablenames which i have supplied in another variable to the connectionstring property of the flatfile destination. Is it possible or not. all the tables have different columns and i need the output in text files.
View 13 Replies
View Related
Jul 14, 2006
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp
View 15 Replies
View Related
Apr 22, 2004
Hi Guys
I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ‘^’. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET
I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesn’t like SQL code)
I am doing this within a data driven query.
Thanks in advance.
Cheers
Chris
The code I have so far looks like this(but doesn’t work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********
' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")
DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))
DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute(DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))
DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute(DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database
' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")
Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )
DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript
If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If
If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If
If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If
string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop
Main = DTSTransformStat_OK
End Function
View 5 Replies
View Related
May 30, 2007
I'm a newbie so I'll explain what I'm trying to achieve the best I can ...
I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.
How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?
Thanks!
View 3 Replies
View Related
Oct 5, 2013
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 2 Replies
View Related
May 10, 2006
I am attempting to use the foreach loop structure in an SSIS package toloop through however many Excel files are placed in a directory andthen perform an import operation into a SQL table on each of thesefiles sequentially. The closest model for this that I was able to findin the MS tutorial used a flat file source rather than Excel. Thatinvolved adding a new expression to the Connection Manager that set theconnection string to the current filename, as provided by the foreachcomponent. That works just fine, but when I attempt to apply the samemethod to an Excel source, rather than a flat file source, I cannot getit to work. I see the following error associated with the Excel sourceon the Data Flow page: "Validation error. Data Flow Task: Excel Source[1]: The AcquireConnection method call to the connection manager "ExcelConnection Manager 1" failed with error code 0xC020200." I think thatit's just a matter of getting the right expression, and I thought thatperhaps I should be constructing an expression for ExcelFilePath ratherthan the Connection String, but I have fiddled with it for hours andhaven't come up with something that will be accepted. Has anybody outthere been able to do this, or can perhaps refer me to somedocumentation that contains an example of what I am trying to do?Thanks for any help you can give.
View 1 Replies
View Related
Sep 3, 2007
Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
View 1 Replies
View Related
Sep 15, 2015
I'm trying to create a report that will be data-driven and produce multiple reports based on a parameter. For example, 5 reports go out today based on invoice numbers from yesterday. Each invoice can have multiple trasnsaction lines and each line contains the invoice number. What I have so far is only taking the first invoice number (let's say it has 10 transactions) and sending me the same report 10 times and stopping.
I get nothing for the remaining 4 invoices/reports. Here is what I have. To me this should enter the distinct invoice no's from yesterday into #temp, while a invoice no exiists begin query with one invoice selected from #temp then delete that invoice no and select another one and repeat till no more invoice no's. But it's only going through the one invoice no.
select distinct InvoiceNo
into #temp
from table
where Invoicedate between getdate()-1 and getdate()
declare @InvoiceNo VARCHAR(25)
[Code] ....
View 0 Replies
View Related
May 14, 2015
I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
View 3 Replies
View Related
Sep 19, 2005
Hey guys,
Little bit of a newbie question here...I have a database with about 20
or so tables in a relational model. I am now working on an output
scheme and had a quick question regarding best practices for
outputting. Would it be best to
1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?
Thanks for the help!
e...
View 2 Replies
View Related
Apr 25, 2007
I continue to try and find "easy" solutions to what should be a straightforward problem of outputting the results from a stored procedure to a file.
I tried using both XML task and file system task with the thought that one of those would actually be able to output a file from a variable, but both of those tasks threw fits when I tried using different variable types (file system required a string, but the XML result set never seemed to throw anything but an object) so I decided to just try a script task and do everything "manually".
So my latest gyrations have been thus:
1) Set execute sql task to output XML and push to a script task to write a file
2) Set execute sql task to output a full result set and push to a script task to write a file
Number 1 was the only one I could get working, because I kept getting this error with Number 2 that said the variable wasn't a recordset (maybe it was null?)
I can actually create files now via the script task, but it seems like the variable that should get the results from the stored procedure isn't getting anything. I tried using a MsgBox to see what was actually being passed to the script task, and all I got was the number 0 which I'm assuming is the default for the object type.
What's the best way to debug this? The package runs without errors, and I'm not familiar with debugging in SSIS. How can I tell if the stored procedure is returning results into the result set variable?
View 22 Replies
View Related
Aug 1, 2007
I have a problem when returning address information from a table that contains foreign/extended characters. The results combine different words as one result, e.g. 'Angouleme' and 'Angoulême' are returned as a single result. Is there a way to ensure that these two values are returned separately?
thanks
View 3 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Sep 26, 2006
I was asked at work to create an audit log to track user changes to our SQL Server 2005 database. My plans were to use a trigger to store the changes in a database table. I was just told that rather then storing these changes the database, that I should write these changes to a text file. I am having problems finding a good example of how to write to a text file in T-SQL. Does anyone have an example of how to do this? Thanks
View 2 Replies
View Related
Jan 16, 2004
Hello DBA's:
I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.
What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.
Thanks
Vivek
View 9 Replies
View Related
Oct 9, 2007
Hi,
I have a table T1 with three Cols.
Code VARCHAR(10),
INISetting TEXT,
TSystem VARCHAR(10).
Right now I are storing the INI Files directly in the database.
I would like to move this to text files in to a SAN box and just store the Pointers in the IniSetting Column and name the text files
some thing like Code.txt
When I do a SELECT Code , INISetting , TSystem FROM T1.
I want to read the Contents as is from the Text files stored in the SAN Box?.
Can some one help me with this.
Thanks
Venu
View 5 Replies
View Related
