 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / June 2008Should i use ADO to open a text file?
Hello. I've developed a VB 6.0, SP6 program to import a fixed length fileds, ascii, text file into a new Excel 2003 workbook. I programmatically design a recordset and import the text file into the recordset using these kinds of statements: With casInputFileRecordset .Fields.Append "ItemNumber", adChar, 16, adFldUpdatable Set casFileSystemObject = CreateObject("Scripting.FileSystemObject") Set casInputFile = casFileSystemObject.GetFile(casInputPathString) Set casInputStream = casInputFile.OpenAsTextStream(ForReading) Do While Not casInputStream.AtEndOfStream casInputFileLineString = casInputStream.ReadLine() With casInputFileRecordset .AddNew !ItemNumber = Trim(Mid(casInputFileLineString, 73, 16)) There are other fields, just not listed for brevity sake. Then in the business logic, i populate the worksheet with: newExcelWorkSheet.Range("A2").CopyFromRecordset casInputRecordset All this works and i'm very happy that i was able to complete such a task. Should/can i try to use ADO to connect to the text file using a connection string and a dataset instead of a recordset? I want to learn about ADO and thought this would be a good way to start. Am i attempting to overkill for a simple text file? Any help would be gratefully appreciated. Thanks, Tony Tony wrote: > I've developed a VB 6.0, SP6 program to import a fixed length fileds, > ascii, text file into a new Excel 2003 workbook. [quoted text clipped - 29 lines] > > Any help would be gratefully appreciated. My own opinion is that FileSystemObject is not as efficient as ADO. This would be especially true if you are reading the data into a recordset anyway. I've used code similar to below to read csv files: ========== Option Explicit Dim adoConnection, adoRecordset Dim strPathToTextFile Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001 Set adoConnection = CreateObject("ADODB.Connection") Set adoRecordset = CreateObject("ADODB.Recordset") strPathtoTextFile = "C:\Scripts\" adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & strPathtoTextFile & ";" _ & "Extended Properties=""text;HDR=YES;FMT=Delimited""" adoRecordset.Open "SELECT * FROM Users.csv", _ adoConnection, adOpenStatic, adLockOptimistic, adCmdText Do Until adoRecordset.EOF Wscript.Echo "First: " & adoRecordset.Fields.Item("First") Wscript.Echo "Last: " & adoRecordset.Fields.Item("Last") Wscript.Echo "IP: " & adoRecordset.Fields.Item("IP") adoRecordset.MoveNext Loop adoRecordset.Close  SignatureRichard Mueller Microsoft MVP Scripting and ADSI Hilltop Lab - http://www.rlmueller.net -- Hello Richard. Your code really shows how simple it should be to import a text file. I'll probably convert over to a csv file since that seems to be a more prcatical and popular way to use text files. I was under the impression that the provider in your connection string is using DAO. Isn't that what Microsoft.Jet.OLEDB.4.0 does? Thanks for all your help. Tony > Tony wrote: > [quoted text clipped - 66 lines] > > adoRecordset.Close > Hello Richard. > [quoted text clipped - 4 lines] > I was under the impression that the provider in your connection string is > using DAO. Isn't that what Microsoft.Jet.OLEDB.4.0 does? <snipped> No. "OLE DB" is the mechanism behind ADO. I see. Being a novice at this stuff, I need to reread about that in Francesco Balena's book. He has a chart showing the relationship between DAO, RDO, and ADO. There is one block "Jet engine" coming from "DAO 3.5" and going to "ODBC Driver Manager". Is "Jet engine" the same as "Microsoft.Jet"? Also, does the "OLE" in OLEDB" stand for Object Linking and Embedding? Thanks, Tony >> Hello Richard. >> [quoted text clipped - 10 lines] > > No. "OLE DB" is the mechanism behind ADO. > I see. Being a novice at this stuff, I need to reread about that in > Francesco Balena's book. He has a chart showing the relationship between [quoted text clipped - 6 lines] > Thanks, > Tony To find what you need on the web this quick 40,000 foot view might help. All database access from VB can be done by using a variety of components, technologies, interfaces, and protocols. These components form a 'stack'. Programming Lanugage (VB) Data Access Library (DAO, ADO, OO4O, and RDO) Data Provider/Driver (OLE DB, ODBC, OCI) Database Engine (Jet, SQL Server, Oracle) The Data (MDB, SQL Server, Oracle, ...) Note: I included MDB (Jet formated flat-files), SQL Server (a RDBMS), and Oracle (yet another RDBMS) to demonstrate that this applies to ALL database access. You can pop in and out various components at either level. In your case: Your Code -> Uses an Data Access Library (ADO) The Data Access Library -> Uses a Provider (OLE DB) The Provider/Driver -> Uses a Database engine (Jet) The Jet engine -> Uses the MDB data store (a Jet formatted file) OLE does have roots with an early inter-process technology termed "Object Linking and Embedding". Over time with enhancements this evolved into an advanced IP technology with its own specification called just "OLE" (or OLE 2). It is associated with COM. COM is a distributed object specification. OLE is Microsoft's implementation of COM. (But it isn't 'COM' - its the machinery behind COM.) "Jet" refers to the proprietary Microsoft Jet Database Engine. It is also often used to refer to "Jet Formatted" database files. When reading about any of these technologies it is important to note the context within which the term is used - all of them can be seen from various points of view - conceptually from its interface to something else, its specification, rules, or methods, or as its implementation or how it works internally. But don't try to understand everything at the beginning. Many a programmer has done quite well working with ADO without a clue of what the engine or provider is actually doing. That's the beauty of all these technologies - they abstract the whole process down to a simple interface. hth Hello Ralph. Thanks for all of that. You certainly have a better handle on it than i do. I tried the ADO routine that Richard supplied, but i'm having a problem with it. It's not putting the data from the text file into the recordset. I'm creating the recordset as i go along, then i'm executing these statements: casInputFileConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & casInputPathString & ";" _ & "Extended Properties=""text;HDR=NO;FMT=Delimited""" casInputFileRecordset.Open "SELECT * FROM dlcasout.csv", _ casInputFileConnection, adOpenStatic, adLockOptimistic, adCmdText It doesn't give an error, it just doesn't give me the data. I took it through the debugger and stopped it on the casInputFileConnection.Open statement. The watch window shows the recordset fields as i appended them. Once i execute the casInputFileRecordset.Open statement, all of the field names go back to "F1", "F2", etc. and their values are null. That explains why the recordset does not get populated, but why is the recordset losing all of the field names? Any help would be gratefully appreciated. Thanks, Tony >> I see. Being a novice at this stuff, I need to reread about that in >> Francesco Balena's book. He has a chart showing the relationship between [quoted text clipped - 50 lines] > > hth > Hello Ralph. > [quoted text clipped - 23 lines] > > Any help would be gratefully appreciated. I can't see anything you are doing wrong. But occasionally VB can become confused, especially if you have been banging around on it for a bit. Clean your project and have another go. Also you didn't meantion what O/S or what version of ADO you are using. If you are using something less than XP SP2 you might want to download the latest 'n greatest MDAC and Jet. Here is sample code that works: http://www.buygold.net/v05n09/v05n09.html See if you can get it to work within your problem domain. -ralph I have to correct what i stated in my previous reply. The data *is* in the redordset. It's just that the field names are changed after executing the casInputFileRecordset statement. However, the data is not populated into the Excel workbook when i execute this statement: newExcelWorkSheet.Range("A2").CopyFromRecordset casInputRecordset This whole program was working before i converted over to the ADO method of opening the text file. Maybe there is another way to add data to the workbook because i'm now using an ADO recordset? Any help would be gratefully appreciated. Thanks, Tony >> I see. Being a novice at this stuff, I need to reread about that in >> Francesco Balena's book. He has a chart showing the relationship between [quoted text clipped - 50 lines] > > hth > I have to correct what i stated in my previous reply. > [quoted text clipped - 14 lines] > Thanks, > Tony My apologies. I lost focus on what you were eventually trying to do. And wasted your time on "ADO/VB" issues. You are using VB to invoke Excel Automation to create a Workbook. As for the lack of a Header, you can simply add one and use a Schema.ini file. http://msdn2.microsoft.com/en-us/library/ms974559.aspx Adding a header is simple enough. Create a file that contains the header and then "Shell header.txt + dlcasout.csv dlcasout.txt (or csv)" The schema.ini also gives you additional control over datatypes, etc. Using Headers: Dim Target As Range Set Target = Target.Cells(1, 1) Dim idx As Integer ... For idx = 0 To rs.Fields.Count - 1 Target.Offset(0, idx).Value = rs.Fields(idx).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs hth -ralph Hi I have recently come across the same problem and wanted to ask you if your excel output was only missing some entries or was completely blank? a response would be highly appreciated. Thanks David David mugela's wild thoughts were released on Wed, 18 Jun 2008 02:47:54 -0700 bearing the following fruit: >Hi I have recently come across the same problem and wanted to ask you if your excel output was only missing some entries or was completely blank? a response would be highly appreciated. Thanks >David I think you meant to reply to a thread but you appear to have started a new one. -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.