Retrieve Text File Data In SSE For Data Acquisitio
Oct 24, 2007
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.
The error message that comes while I preview the source data:
Error:
"The Preview sample contains embedded text qualifiers. The flat file parser doesnot support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at runtime"
Is there any alternative to remove these text qualifiers from the file. Do we have any utility that can convert these files into text qualifier free CSV file?
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!
I want to use sql server as a back end for my asp.net shopping cart using vb.net..i have created an "news.mdf" under app_data folder of my website in visual studio2010...but i tried to connect it to database using below command...but showing error...
I need to retrieve data from a flat file and load it to a database table. I know how to do that. But what if I want to retrieve 3 fields from another table for each row read from the flat file source based on a date from each row in the flat file data source.
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.
basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.
i read a thread here which is similar to what i am trying to do but it was in xml format..
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
If this is the wrong place for this question, would someone please tell me so. I am new to SQL Server and still feeling out resources. I have a few books on SQL Server but none cover this question.
I have a text file of dates and numbers that I want to insert into a table. There are way too many rows of data in the file to do this by hand.
Question == How can I essentially insert the text file into my table?
I am trying to find a convenient way to export parts of tables to text files.
One way I see is BCP: Is there a way to avoid writing the command and options into the command prompt by hand? I.e. a way to write the commands into a text file and then to execute them?
Are there other ways? I`d like to find a way that a user who uses a web interface can use.
Is there a way to send the text files via mail to a remote user?
Hi, I just started using SQL Server 2005 and I'm trying to find out how to do a sql dump on a table, but this is proving more challenging then it should be.
I usually use mysql with a program called navicat, and all you do is right click on the table and select dump... Inserting the data back in is just as simple. I have also used sql server 2000 awhile back and I know there was a dumping utility for it.
Can someone point me in the right direction on how to dump data to a .sql file and reinsert that data? Thanks!
I am new to ssis. I try to create a package completely by vb.net to export a table in sql server to text file. i got the following error while i run the package,
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". The AcquireConnection method call to the connection manager "OLEDBSrc" failed with error code 0xC0202009. component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. One or more component failed validation. There were errors during task validation.
i have posted my code below,
Dim pkg As New Package
Dim OLEDBConMgr As ConnectionManager
Dim FileConMgr As ConnectionManager
Dim SrcComponent As IDTSComponentMetaData90
Dim SrcInstance As CManagedComponentWrapper
Dim DesComponent As IDTSComponentMetaData90
Dim DesInstance As CManagedComponentWrapper
pkg.PackageType = DTSPackageType.DTSDesigner90
Dim e As Executable = pkg.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = e 'as Task Host
Dim DataFlowTask As MainPipe = thMainPipe.InnerObject 'as MainPipe
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.
Hi everyone 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 wayColumn1, 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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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.
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
I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.
My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?
Hello, I'm beginner in SQL and I would like to do a simple thing : Extract data from different table to a text file. I would like an automatic schedule job to extract these data and also I need that the result are "append" in this text file. Could you help me and give me the process to follow. Thanks in Advance
I am running SQL Server 2000 and need to output query data to a text file. If I run the following query (Below) using XP_CMDSHELL and BCP, it runs fine and creates a text file with the data output.
However, I need to change the WHERE Field1=10 to a string value WHERE Field1='abc'. When I try to do this I get a general error on the Field1='xyz'. I tried to change the quotes, etc but I am still getting the error.
Command: Exec master..xp_cmdshell 'bcp "SELECT Field1 FROM Table WHERE Field1=10" queryout c:filename.txt -U UserName -P Password /S SQLServerNam /c'
"[Flat File Destination [13]] Error: Data conversion failed. The data conversion for column "SDATA" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". " "[Flat File Destination [13]] Error: Cannot copy or convert flat file data for column "SDATA". " "[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Flat File Destination" (13) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. " "[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020A0. There may be error messages posted before this with more information on why the thread has exited. " "[DataReader Source [207]] Error: The component "DataReader Source" (207) was unable to process the data. " "[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (207) 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. " "[DTS.Pipeline] Error: 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. "
I am selecting data from an OBDC then copying it to a text file an I always get this error, but when I change the destination to a excel file it works perfectly. But the whole point of the package is to copy to a text file.
Please could you help me, your replies will be greatly appreciated.
I have the data of ACTTAB, APTTAB and etc, how to i export this data from SQL and insert into a text file??? this is the first time i need to do. as for the 2nd thing is that after export the data from SQL into text file, i need to import this data into mongoDB. So basically how to export this following data (ACTTAB, APTTAB and etc) into text file?
Using ADO to conect to a text format data file to gather data. File is a .dat file, tab delimited, no header row.
Got all my ducks in a row: 1) Write a schema.ini 2) Make sure the extension is not excluded in the Jet Text registry key 3) Setup the connection properly (per MSDN Scripting help)
Works nicely, except the fist line of data is missing. File contains 78 rows of data, Jet is skipping the first line every time and reporting 77 rows.
Notes: 1) Adding a blank row, as suggested in this forum, is not an option - the data files are machine generated are not to be altered by other processes. 2) I could do it the old fashion way of importing and splitting into arrays, but this is a database function and ADO should be able to handle it.
Code samples:
Code Block
adoCON.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=""text;HDR=NO;FMT=Delimited;"";" On Error Resume Next rsSrc.Open "Select * From " & sFile, adoCON, adOpenStatic, adLockOptimistic, adCmdText sPath is a string containing the path, including the trailing sFile is a string containing the file name.
I have an application taht requires the use of a table. The device that this application works on, has a local memory that does not allow me to insert the 800,000 records that I need. Therefore I have two approaches:
1. To insert less records into my local memory database e.g 40,000 but not row by row, bulk insert is better. How do I do the bulk insert?
2. This is the most prefferable way: To find a way to insert all 800,000 records into a table on the storage card which is 1GB. What do you suggest? Will using threads be helpfull? Any ideas?
I use C# from VS 2005, SQL ME, compact framework 2.0 and windows 4.2.
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.
I have a table with text data with more than 8192 character in it. When I tried to select or into a file I could only see the first 8192 after setting 'Max character = 8192' properties. Please tell me how to select the entire data.
