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
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
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!
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 RelatedHi,
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
Hi,
I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc.
Can anybody tell me, How I can achieve this.
Thanks
I NEED TO READ A TEXT FILE INTO A SQL SERVER 6.5 TABLE. THE FILE HAS VARIABLE LENGTH FIELDS AND THE FIELDS ARE SEPARATED BY PLUS SIGNS ("+"). ANY IDEAS ? THANKS FOR YOUR TIME.
View 1 Replies View RelatedI would like to export SQLServer data from a table to a text file within a stored procedure that is fired from an after insert trigger. In Oracle the is a UTL_FILE Package that does this. Is there any way to do this in a SQLServer stored procedure.
View 1 Replies View RelatedHas anyone written a SQL script to import ASCII comma or space delimited?
I am trying to load a PROGRESS exported file (Unix) into SQL SERVER 6.5 (NT).
Much appreciated,
Nickd
Hi ppl,
I'm trying to import a fixed lenght text file into a sql 7.0 table using the SQL command "BULK INSERT".
The text file is fixed length and end with a carriage return and linefeed. Fields aren’t delimited.
Wich definition of the "FIELDTERMINATOR" should i use ?
I need some Help here!!!
Thanks.
I have to import 18000 text files into a sql database.
Each file contains 10 fields and around 5000 records.
I am currently doing this with DTS.
What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.
I would appreciate the benefit of somebody elses experience when dealing with this type of thing.
Cheers,
Brookesy
Hello!, I have two applications that import data from text files to Access 97 tables. One of this applications is in Access 97 and the other in VB6, know they are requesting me to import this text information to a SQL table. I do not have any experience with SQL Server, can some one give a idea of what I could do to accomplish this?, I would appreciate a lot anyone's help!, thanks! ...
View 4 Replies View RelatedOk, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.
I have a table set up with my parameters and output file names.
Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?
Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).
PS: The file data cannot include cilumn headings
TIA -
bpd
I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.
Help is appreciated.
I created a stored procedure with the below
declare @filename varchar(50),
@bcpcommand varchar(2000)
set @filename = 'c:
eportmedia.txt'
print @filename
set @bcpcommand = 'bcp "select * from table" queryout "'+ @filename -U -P'
exec master..xp_cmdshell @bcpCommand
Hi All
I have a situation in my SSIS Project where I have a folder in which contains 10 text files, what I need to do Loop through all these files and select the data from those text files and insert them in SQL Database table. I will be able to get the names of those file using For Each Loop Container but I don’t have idea how to insert the data in SQL table using for each loop container. I don’t want to use multiple data flow task. Any help will really appreciate
Many thanks
hi friends ,
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Thanks $ Regards
Nevin
hi friends ,
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Thanks $ Regards
Nevin
Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.
I have a load (180,000+) of text files whose contents need to go into a SQL server database.Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.
View 1 Replies View RelatedI created a package to import records from a fixed length 700 byte text file to a table in a SQL Database. I used the wizard to set it up and note the byte where each column ends. I need to customize the process, as the name of the text file will change each night, so I want to be able to set the file name from the VB front end app.
I have tried modifying the Datasource property of the DTS connection to the flat file, without success. I have also tried setting a global variable for the datasource property in DTS, and assigning that from VB, similarly without luck.
Do I have to create a custom package in code from VB? If so, how do indicate where each column in the text file ends? If I can customize the existing package, is there a specific reference that I need to set in my project that will let me control the value of the global?
TIA
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
Hi all,
I have around 140 text files in a folder where all the file entries are delimited by tabs. Currently i am copying the data and then pasting it in to the tables and it is perfectly working fine. I have already created tables for those 140 files. But now i want to load those files in to the table at a single shot. How can i do it? I am using SQLServer 2005.
Sarvan
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 RelatedHi allCould someone help me with the following problem? Hours of googlingyesterday couldn't get me the answer. I'm using SQL 2000 and DTS andtrying to import a huge fixed width text file.File is >1m rows and >200 columns and is defined by a proprietory (i.e. notbcp produced) format specification of the formName Start LengthFld1 0 20Fld2 19 5Fld3 24 53and so on.Tbe only way I've found to define the columns so that DTS can import thefile properly is to go through the wizard and click on the starts of eachcolumn. I don't want to use bcp if possible (I did enough of that onSQL7) - but surely there's a way to get DTS to read from a format file so Idon't have to click 200 times (with all the ensuing errors I could make).Any help greatly appreciated.CheersRob
View 2 Replies View RelatedHiI need create, write and read text files in a procedure transac sqlfrom sql server 2000How i do this ??Thanks,
View 3 Replies View RelatedI have created a DTS package which imports text file into single sql server table with 8 columns (SourceData). The DTS package uses 'Test1.txt' file. Now i have around 200 text files (Test1,Test2,.....Test200). I need to import them one by one into 'SourceData' table. Could you pls. help me out in getting solved.
Thanks,
Hemal
hey everbody,
i'm absolutely new to any sort of data management
here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are
1. how do we store files in ms-sql (binary format or normal text)?
2. how do we match the files?
3. what code we write in c# for this purpose?
4. has this nething to do with pattern recognition?
My request to all new n active experienced user's to participate because Plzzzzz help me?
Hi,
I'm trying to join outputs of two text files combined in to one text file. The lenght of both the input text files are same (590)
For example,
If contents of text_file_1.txt is: aaaaaaaaaaaaaaaaaaaaaaaaaaaa
and contents of text_file_2.txt is: bbbbbbbbbbbbbbbbbbbbbbbbbbbb
My final text file should be :
final_text.txt
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
I use flat file source for both the input file and then use "Union all" and then write out to final text file destination.
The issues is:
The second file (bbbbbbbbbbbbbbbbbbbbbb) is coming up first in the output and then there is no carriage return.
My output looks like:
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb...(goes up to 590)aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaa
What is that I'm missing here. Could some one plese help.
Thanks
Sri
I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.
How do go through the recordset to create a file for each customer?
I am having a scenario where all the stored procedures are stored in a folder (one sql file per sproc). Stored procedure does not have 'IF Exists .... DROP Procedure' in the script so before creating them we have to drop all sproc manually.
Can anyone help me writing a script / SSIS process to loop through each file in folder and write "IF EXISTS ... DROP PROCEDURE" with the procedure name in it ??
I can create a package that loop through each file in FOR each Loop task but dont know how to write in file using .net
Thanks in advance
Furrukh baig
how do i insert a large chunk of text into a table column. my project is to build a news website. where people can go and read news articles. the articles are provided by the author in word format, so how do i insert that news article into the table's column? any help would be appreciated
thanks
What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily.
View 11 Replies View RelatedHello,
I am kind of new to Sql Server 2005.
I figured out how to use the import data wizard to import a delimited text file.
But I need to find a way to import many delimited text files at once.
does anybody know if this can be done in Sql Server 2005? and how?
thanks in advance,