I have a directory that contains a lot of text files that have data I need to draw from. I want to know if it is possible to write a program that will read all of the text files in the directory and pull out data and save it to a new textfile.
For example: Each text file is formatted this way
Column1, Column2, Column3
"1","xxxx","yyyy"
"2", "xxxx", "yyyy"
"3", "XXXX", "yyyy"
I want to put all lines that begin with 1 in one text file, all the lines that begin with two in another text file, and the same with all lines that begin with 3.
my problem is I want to be able to point at the folder that contains those files and have it read every text file in the folder and perform the operation. If this is possible can someone point me in the right direction on how to get started.
Thank you for any help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I have text output files which are semi-structured.(Headers + irregular length tables below)
Is there a simple method of getting them into sql format(line by line) to try and extract data from them?
I know this won't be easy but its been worrying me for a long time. I have a method of importing the data into excel, but although difficult, it must be possible to get a system to get it into sql server. This must be a fairly common issue.
How would I be able to query a table (ie. all people with last name 'Smith'), have that set of data outputted to a regular text file (in a formatted way)
And what's the best way to manipulate that set of data to let's say update a Yes/No field in that table to mark that that those individuals('Smith') which were outputted in that text file?
What about the reverse? If I got a regular text file with Last Name, Social Security(delimited by tab), etc is there a way I can get SQL Server to read that text file and make an update to the database based on the Social security in that text file.
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
I need to know how to import a text file into a stored procedure as one big varchar. I don’t want to import the data straight into my tables. I need to be able to work with it in the stored proc.
Guys, need help! I know this is not area for VBScript question, but possible I will find someone to help. Here is my question.
How can I read a text file of product IDs (ProductID contain only the first three character at the bigining of each line -- for example 220)and retrieve just those lines that meet a specified pattern?
Is there anyway Sql Server reads a "Tab Delimited Text File" and Compare each record with the Column in a table..
my question is..
I've a Country_Code table which has 3 letter Country Code and the Actual Country names are listed in a Tab Delimited Text File "Country Data" with Country Code and Country Name, how do i read each record and compare to get the Actual Country Name for Display.
Hi All, I Need to load the text(CSV) files into sql server using text reader. Please can any one give me the code for that. I want read that file in web page only. I can't use Bulk Insert. First I will read the file into data set. Then i wanna update that in sql server table. Thank you,
I'm wondering if there is any way to get SSIS to notice, in the Flat File Source, that a "Ragged right" text input file has a record that is too short to populate all the specified columns.
I am reading data from a file that is supposed to be fixed length records, but record 193,591 (out of approx. 500,000) is 20 bytes short of the fixed length (60 bytes). So I changed the input to "ragged right" and found that I can thereby continue to read the file, and load the data (after setting the "maximum errors" to a number greater than the initial "1"). (Without this change to "ragged right", every record after the bad one was "out of synch" with the column arrangement -- so they never made it into the database table destination.)
But the "failures" I am now getting are during the Data Conversion step, when I try to convert some columns to integers (from text, in the input stream). And by looking at the data with a "Redirect Row" setting for the Data Conversion step, I am able to see that the Flat File Source is reading "right past the end of the row."
Is there a way to get the Flat File Source to honor the CR-LF record terminator, and decide that some text columns should contain "nothing" (NULL or zero-length strings), rather than the bytes that contain the CR-LF and the initial text from the next record? Can this somehow be noticed as an error condition?
I'm trying to read in a text file, fixed width, very long records (over 7000 characters) in an ssis package. The first record appears ok in the 'preview' in the connection manager setup, but each record after that is offset by 2 characters (record 2 offset by 2, record 3 offset by 4, record 4 offset by 6 and so on), like it's inserting special characters.
I would like to read from a Text File using SSIS Integration Package.
The file has a fixed number of columns, let's say 3 columns. There is no row header and each columns length is fixed. There is no delimiter as well.
Here is the sample of the file contents: John Doe USA Mary Monroe UK Andy Archibald Singapore
Here is the hints to read the file contents 123456789 0123456789 0123456789 ============================== John Doe USA Mary Monroe UK Andy Archibald Singapore
If you notice, from the 1st column until the 9th column, it's reserved for the first name. The 10-th column until the 19th column, it's reserved for the last name. Finally the 20-th column until the 29th column is reserved for the Origin Country.
Since there's no delimiter inside the flat file contents, i have difficulty in parsing this text using SSIS Package.
Please let me know if you need any necessary information.
Hello friends.... I am looking for 2 things(using c#.net or vb.net and sql svr 2000) 1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space) 2.Insert the data from text file back to database. Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
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?
I'm trying to export data from one of the table in my SQL 7.0 database into text file. Can someone tell me how can i do this using SQL Query instead of using BCP (command line) ?? Thank you in advance.
Hi All, i have mutiple text file. let us say,a1.txtb1.txtc1.txt i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table) now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3 using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process. so, can any one give ur valuable sugession to solve this issue.
Halo, I am a bit new to this Please can someone help me, I would like to write a file(Any type) to a SQL database like a attached document(s) for the current record and be able to detatch the document when needed. I use VB.NET for a ASP.NET app. I basicly would like to attach documents to a piece of equipment may it be any kind and if the user views the equipment he will be able to detatch the documents for that piece of equipment and open it with the correct software. PLEASE HELP!!!!!!!
Hi Everyone, I am looking for some help, as i am pulling my hair out looking for information. I have been using asp for many years and am now starting to learn .net. so far so good.... I am now wanted to connect to a database, execute a simple select statement and then read/write the information out. I can't help but think in old asp code and i am having a hard time finding what i need to perfom this simple task. I have used the grid controls etc, and these are very good - however, i need to connect to a database in the code-behind file and perfom various functions in the background. If any of you could be so kind as to perhaps show me some demo code i would be grateful. I would like to do: A) Connect to a database (sql server 2000)B) Execute a simple SQL select statementC) Read the returned informationD) put this information into variables used elsewhereE) how do you check if no records are returned? such as the .EOF in asp? Many thanks Darren
I have a database that contains news items. There's a column that contains the actual article. In that field, there are paragraphs with page breaks. The page breaks in the database are represented as squares (unrecognizable characters I guess). When I try to read in the data, it doesn't recognize the page breaks, and it comes out all in one large paragraph. Is there any way to get around this?
Hi, I have a data structure called 'Quote' which contains a number of different variables and controls ranging from text boxes, check boxes and radio buttons, i need to be able to read and write this from a database.
First I think a description of my overall project is needed:
Project Description I have been given a brief that basically says: i have to create a programmed solution in VB to solve a problem. This problem can be anything we like, and I personally have chosen to create a program that manages quotes for building Log Cabins (this is very contrived and far from anything someone would do in the real world).
My solution will allow a generic user to create a quote (using a form with controls such as text boxes, check boxes, radio buttons) , and then save this to file. These users may then wish to load/edit this quote at a later date, from another form.
Whilst completing this project, i'll only have up to about 5 records (quotes) within the system, so i dont need the ability to store hundreds of records. And each record will be relatively short, with only about 10-15 data items within the data structure.
Also the Admin (or business owner in this case) need to be able to view all saved quotes in a presentable format, and edit them if needs be, from within this same program.
This solution does not need to be absolutely perfect and 100% efficiently coded, or have all the bells and whistles a real-world program would have. This is for an A level computing project by the way.
So basically, i need to be able to read from the database (to populate a Data Grid (i imagine this is best way?)) and so Admin can access any quote and edit it (editing is not vital, but viewing/printing is. Maybe i should stop at just viewing any quote?). Also i need generic users to be able to fill in the Edit Quote form and then save this data into the database.
And is a data structure really required for me to use a database?
I've never used databases in VB before (but have used them elsewhere, mainly Access) and so am completely new to this. Any help will be much appreciated. Thanks
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 + ")";
Hi all,I'm a complete newbie on ASP.Net.I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.When I try to open the site, I always get the same error:Invalid object name 'Members'I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.Here is the Code of the page so far:<%@ Page Language="VB" Debug="True" Strict="True" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SQlClient" %><script runat="server">Sub Page_Load (ByVal Sender As Object, _ ByVal E As EventArgs) Dim connStr As String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" connStr += "database=tempdb;" connStr += "Truster_Connection=yes" Dim conn As New SQLConnection(connStr) conn.Open() Dim sql As String sql = "SELECT COUNT (*) FROM Members" Dim cmd As New SQLCommand(sql, conn) Dim ergebnis As String ergebnis = cmd.ExecuteScalar().toString() Dim t As String t = "Die Tabelle Members hat " & _ ergebnis & " Zeilen. <br>" & _ "Das Kommando lautet: " & _ cmd.CommandText & "<br>" & _ "Der Kommandotyp ist: " & _ cmd.CommandType ausgabe.innerHTML = t End Sub</script><html><head><title>Demo zu SQLCommand.ExecuteScalar</title></head><body><h3>Demo zu SQLCommand.ExecuteScalar</h3><p runat="server" id="ausgabe" /></body></html>Thanks for your help an sorry for my english.GreetsFlash_Prince
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.
Hello,I have a column (text datatype) and has to send an email as a text(not attachment) using cdonts. I am reading the data from text columnstoring in a varchar field and saying cdonts.body = [data].This way I can send email to as a text format. Now, my problem is whenlength of data is greater than the 8000 chars it truncates the rest ofthe data.......and email I send is a truncated email.......loosingimporatnt data.How should I resolve this situation.......I am trying some differentideas but not worked yet. Finally, I am writting the entire content ina file and sending it as attachment but the reaquirement is to send itas a body text.Any ideas?Let me know if you need more details!Thanks,-Hayatwww.mysticssoft.com
The ERP manufacturer used an image data type to store large text data fields. I am trying to move these data types from one database to another database using either Sql Queries or MS Access. I can cast them as an 8000 char varchar to read them directly but have no luck importing into these image data fields.
Access and Crystal are not able to read these fields directly.
Any suggestions? Most information about these fields has to do with loading files but I am just moving data.
Hello!! searching information about how to migrate some date from an old data base (any tipe) from SQL I´v found this: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)] Does anybody know how does it works and how to use it????I´d like to know because I have to load data from a text file to a SQL Data Base and this seems to be te fastest an easiest way to do it...Thanks!!!!bye!
Hey All, I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form. I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Hey All, I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form. I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
A section of this company's intranet site where I just started interning at has little company anniversary and birthday sections that look like (for the anniversary section.. in the birthday section, it looks the same, except it doesn't say how old the comployee is):
-Steve Cunningham 6/1 - 6 yrs -Andrew Brown 6/3 - 11yrs -Lisa Stone 6/4 - 3 yrs
How can I get it so instead of manually changing that text every month, it will look at a SQL database and automatically change that text every month? I'm guessing the pseudocode would be if the b-day or anniv. month matches the current month, display the first and last name, the date, and number of years (which would have to be calculated maybe?) Any help would be GREAT! Thanks!!
Hi€¦ During my web search looking for a solution I ran across SQL CE 3.5 articles. My questions about SQL CE 3.5 are: 1) Can SQL CE 3.5 handle a 4 €“ 6 GB file - Read - Parse (SQL) 2) Can SQL CE 3.5 act as a standalone client that a user can view a large (4-6 GB) text file? - Will I need a .NET (small) client to read the large (4-6 GB) text file? More info: The text file will reside on the machine where the SQL CE 3.5 is installed. There is no pull to get the data.
We have an interface where we receive data from an external supplier. One of the fields in the interface is of type BLOB (the source is an Oracle database), which would be read into our MSSQL database as image. This can also be converted to varbinary, and a typical field value looks something like:
0x70697A5F8F000000789C0DCCBD0DC2301....etc. etc.
However, we know that the origin only contains text, and we even know the text from the GUI they supply us with. The text could typically be "Delayed by 3 minutes because of water damage" or something like that.
What I want to do, is to extract that text from the field.
First, I have stored the incoming data stream in a table, where one column is of type varbinary(max). It looks like this goes swell. But I don't know which command to use in order to get the text extracted.
I have tried these:
1) select master.dbo.fn_varbintohexstr(Myfield) from Mytable -> Returns just the text "0x70697A5F8...." which I have no interest in
2) select cast(Myfield as varchar(max)) from Mytable -> Returns just Chinese signs.
3) select cast(Myfield as nvarchar(max)) from Mytable -> Returns just Chinese signs.
4) declare @ptrval varbinary(16) select @ptrval = TEXTPTR(MyField) from MyTable  -- with MyField defined as image READTEXT MyTable.MyField @ptrval 1 30 -> Returns just the text "0x697A5F8...." which I have no interest in
Of course, since only text is stored in this field, the field should never have been defined as BLOB in the first place. But the source system is external, and it's a standard system, so we may not alter it in any way.
