For one, my files have repeating page headers. I know certain things about my text files and can build in some assumptions to make things better. Microsoft has to make their method flexible and universal, but I don’t. My importer would be significantly different than Excel’s. The worst part, by far, is the tiny window that shows a preview of your data.Īfter a few days of using this wizard, it was pretty obvious that I was going to write my own, if for no other reason than to make that window bigger. First, it’s yet another wizard and I think it would be better as a single form (but then I think everything is better as a single form). The built-in method for importing these files is terrible.
I’ve been working with fixed width text files quite a bit lately. ReDim aReturn(1 To Me.Count + 1, 1 To 12) Public Property Get OutputRange() As Variant RStart.Resize(UBound(vaWrite, 1), UBound(vaWrite, 2)).Value = vaWrite The FillFromRecordset procedure is a method in CTransaction. For strings, it automatically returns vbNullString. For numbers and dates, I specify that I want to return a zero in place of a Null. It uses an Nz function that I wrote to avoid Null problems and return a default. There’s not much to filling the CTransaction class via the FillFromRecordset method. With a good connection string and SQL statement, I open the connection, open the recordset, and start looping. But for this example, I used "//" which is a little more specific. For some of the dates I used "_/_/_" and I think it’s pretty safe. It’s pretty unlikely that header information or totals rows are going to have a Yes or No in that same position. We have three Yes/No fields and we could use any of those. Our example file has lots of options, not just PostDate. 'Loop through the rs and create CTransaction instancesĪSql ( 4 ) = "WHERE TranDate Like " "_/_/_" " Or (LotNo Like " "%" " And TranDate Like " " %" " And Not LotNo Like " "Item ID%" ")" 'Create a connection string and SQL statementĪSql(4) = "WHERE PostDate Like ""//"""ĪdRs.Open Join(aSql, Space(1)), adCn, adOpenStatic, adLockReadOnly, adCmdText SPATH = Replace$(sFile, Dir$(sFile), vbNullString) Public Sub FillFromFile(ByVal sFile As String)ĭim adCn As ADODB.Connection, adRs As ADODB.Recordsetĭim vaConn As Variant, aSql(1 To 4) As String Using the column headers from the file, I create a CTransaction class module.
That means I need a Transaction class to hold each of them. If you couldn’t tell, the text file in question contains transactions from a general ledger. Didn’t this use to be a family friendly blog? I don’t take a crap without a class module, so we’ll be using class modules here. Go read the MSDN article if you want the specifics, but basically you need a file called Schema.ini that tells ADO what’s what in your file. But I went from setting this up for one specific report to a dozen reports very quickly. Usually, fixed width text files are not arranged as normalized data, so there are some challenges. The bonus third reason I love ADO is that I can replicate it for different text files easily.
#Vba recordset loop through records wizard code
Ninety seconds still stinks, but it beats having to get a coffee every time the code runs. I had a huge text file that took several minutes to read using the Input$ function. First, it allows me to only import the data I want and easily ignores headers, totals, and other non-record data. But, like me, you may never have considered using to import text files or even knew that it could.īefore I get into the specifics, there are two things that endeared me to this method. You may remember ADO from such database objects as Connection, Command, and Recordset. This post is about importing that file with ADO. If you haven’t been following along, I wrote a post about creating a sample fixed-width file and one about importing said file. It did, however, lead me to this MSDN article, which was very helpful. I happened upon a post that dealt with using ADO for this purpose. Remember 1995? Me neither, but I’m living it every day. A few weeks ago I was struggling with fixed width text files.
0 kommentar(er)
