Big Excel Data File ( 3 Deep columns ) To Text File To 3 Big VBA 1 Dimensional Arrays

Hello,
This Thread is strongly related to this one_....
http://www.excelforum.com/excel-prog...de-please.html
http://www.excelforum.com/excel-prog...-please-3.html
_...I am looking for support here to help me solve that one...

_Very briefly what I am trying to do there is:
_ take data from 3 very deep columns in a large database File, “NutritionalValues.___”, and sort the data into very long rows in another file. ( “BDSept2016.xls__” )

_ We are coming to the conclusion in That that Thread, that Excel has a “ “Memory” of the Memory “ it uses, which cannot be cleared without closing and restarting Excel. Closing the big data file after extracting the data does not help. In effect once I have opened up the data File, then I have hardly any memory left ( in Excel 32Bit at any rate ) . So I do not get far in producing my sorted rows in File “DBSept2016.xls__” before it bombs out.
_ I do have a fairly efficient VBA code based on Arrays that sorts and makes the rows in the File “DBSept2016.xlsx” ( I cannot get far with it on my 32 Bit Excel, but one Member did get it to work up to the full 8790 rows with his 64 Bit Excel )

_ My latest thought to help me get that Thread Solved is the following:

_ Open just the big data file and write ( Print ) out the columns to a simple text File. I should usually have enough Memory left to do that !!

_ Close and restart Excel , ( But don’t open the data File !!! ). Open just the File for sorted data, “DBSept2016”, ( make it a macro “DBSept2016.xlsm – no big memory difference there I think. Or maybe it is *** ) –

_ Use a VBA code to get the columns of data into the three 1 Dimensional Arrays ( which can then be used in a version of my original fairly efficient VBA code based on Arrays that sorts and makes the rows in the File “DBSept2016.”

_ So over in the Test Area Sub Forum
_ This Post shows again what I want to do, ( That is to say make a .txt File from the three deep Columns of data in the big excel data File, “NutritionalValues.---”, and then put those three columns in three VBA Arrays):
http://www.excelforum.com/showthread...t=#post4484311


_ This Post gives a code that does exactly what I want in terms of making the Text File:
http://www.excelforum.com/showthread...t=#post4484331


_ This Post gives a code that does uses the text File to make the Three Arrays exactly as I want. ( BTW. they have the headers in, but I do not necessarily need those – I am principally interested in the data in ).
http://www.excelforum.com/showthread...t=#post4484332

( All the relevant and complete Files are also uploaded in those posts )

_.....
_ For the codes I wrote, I am using the basic VBA type stuff of the form like_...
Open FullTextFilePathAndName For Input As___
Open strFullPathAndFileNameForPrint For Output As ___
_.......and looping to read in or print out a line at a time_...

_ What I am asking here is for better ways to do this. ( preferably still using code ). I expect Columns can be “chucked out” in some very efficient ways to a Text File. And in particular I expect putting a column from a Text File efficiently into an Array is possible. ( I did have the idea of putting and getting the data with a Microsoft Scripting Runtime Dictionary, or a VBA Collection – but I believe you only have the possibility of looping stuff in and out of those...
_ There are a lot of “Queer” ry Query things and “ADODBy” things out there I think, but I have little idea about them_....

_No rush, - I am getting clued up now on the whole ADODB und similar type scene now, but I am posting the question here as someone may see immediately a way to do this. If anyone can help it would also be great if they can explain how / what is going on in any code suggestion. – I can handle standard VBA quite well, but using some of these external libraries, “Queer” ry Query things and the such are a bit of a mystery to me. ..
( _ By the way I realise I could tidy , simplify and speed up my codes a little bit, Mostly here I am asking for fundamentally different ( and most likely much better ) was of doing it )

Thanks
Alan

***P.s. A small related question.. If I have learnt one thing from that other thread it is that how “Big” a File is and how a File effects VBA Memory limits are two different things!! – Now I have noticed that the size difference between a moderately big .xlsx File and the same File after putting a few c codes in it and saving as .xlsm is quite small. I am being fooled again by Excel here ? Has Excel “put aside” ( for as long as Excel is open ) an extra bit of memory once I save a File as .xlsm, which does not necessarily show up in the File size ?

Thanks Kenneth and Kyle
That sort of confirms what I was thinking. I even answered a few Threads myself some time ago using the ADO ADOBD thing.
But I am out of practice and forgot. And the files were nowhere near the size I am playing with now.

I did what I want with a different method as explained in Post #1 using the VBA Open For Output__, Open For Input__ etc... stuff. - Worked well, just slow.

I will try to get back clued up on ADOBD, ADO again and have a go. But i never understood back then what i was really doing )

If anyone, however, anyone can do that for me without too much trouble than that would be great, - otherwise I should be able to do that ...(.... eventually )

As for the other things outside Excel. I think I get the point that things like ACCESS, SQL’s and the such would be better. ( The data I am playing with I sort of ripped out of a weird ACCESS relationaly god knows what Queer queriy thingy. )

But I think manipulating text files is as far as my brain and other resources are capable of... or equipped to understand....

I am just trying to get a big list of all data I need in a bit table, and be done with it. I would have thought a simple 2 D Table using the rows and columns of a Excel File would not be too much to ask for. But I am having loads of issues with dealing with how Excel is playing silly Buggers with what it does with its memory. ( I expect all part o a trick to keep us dependant on the newest “ 4096 Bit Excel God knows what fang Dango versions......” )

_...... But using “external” things like text Files , Diktionaries, and the such will help a bit I think


Alan

Hi
The solution to this Thread could be useful to me in another thread.
https://www.excelforum.com/excel-pro...liarities.html
I didn’t have a solution here at the time.
I probably know enough in the meantime to solve it myself. Which I am having a go at now..

So I thought I would work through the solution in this Thread to
_a) Share it,
_b) Have it conveniently stored for me here
_c) I thought in doing so by “bumping” the Thread back up and leaving it unsolved for a bit, someone might comment or suggest any other alternative solutions . I’d very much welcome any of that. I would feedback with some time comparisons etc...

Here we go ..
As far as I can make out ( and I don’t relish wading through my long posts much more than anyone.. lol... ) , I did in this post last September a not particularly efficient code to
_ put three very long columns into a text file, and then
_ bring the columns back into three separate arrays()
( Those codes were based on a simple ““VBA” Text to and from Excel way” that one often learns first )
Those columns were from deep columns ( lots of rows ) in a File of the form of one of those “irrational DerangedAnal rational databases type thingy” –Lol-... )
My current data file is a bit different. It is my more sanely organised final data File that I painstakingly made form one of those “irrational DerangedAnal rational databases type thingy” __ With Hindsight I have had good advice suggesting that I am going about it the wrong way, but for now , for completeness , I will stick with it.. may be )

This is a current File example....
Data File ( has 8810 rows of data ):
"DBSept2016 - first AlanSucces.xlsx" http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t

It has a lot of columns , and less rows, ( 1766 columns x 8810 rows ) , than the previous example I did. I do not expect then my simple codes of the types discussed in post#1 and this post to be very fast... but the point of me answering now this thread is to do the ADO alternative. ( at least initially in reading – In my final application I probably would only make the file once, but would probably want to read bits of it frequently .. at least that is a current plan.. So slow making of the file is not such a problem. But it would be handy to read from it quick. )
_............

So I have done a couple of codes to remind me what’s going on here...

Make Text File
So here we go a turning "DBSept2016 - first AlanSucces.xlsx" ( http://imgur.com/D9knusB ) into a text file .. or rather here we go a making a big text file from a big XL .xlsx
Full code and detailed explanations I have done here:
https://www.excelforum.com/developme...ml#post4637378

In brief:
The code uses the simple “VBA type code” whereby you have a line like, pseudo code:
Open up _ FullPathAndTextFileName _ ForAnOuput _ ReferenceNumberForThat”Route”
That somehow allows you Print or Write out a line at a time, - every time you do that it seems to automatically move down where it outputs, ( or maybe it just every time puts another row on the end of what is already there, - in effect it is the same , I guess )

So the way you do it is you build up a string, typically using each cell from a row in a spreadsheet and concatenate ( add ) something in between so that you can distinguish later the individual data entries. ( The thing you add between is called a delimiter or Separator ). Often it is a comma _ , _ – ( hence the term comma-separated values (CSV) ). I prefer a pipe _ | _ )
Then you Print or Write that out .
Pseudo the text file should look like finally
Row1Column1data | Row1Column2data | Row1 ...... etc.

So in a loop, at each loop, you build the string, ( typically by looping along the columns from a worksheet ) then you Print or Write that string out.

I did a couple of codes , one of which limits the size of any cell text given to the text file to 255 characters. I have done this in case I need to make that restriction somewhere later. That sort of limit often crops up.

So the code makes a couple of files. They look OK. The codes take ages, especially the code to make the text file, but never mind that for now..
So I end up with text files looking like this:
Row1Column1data | Row1Column2data | Row1 ...... etc
Row2Column1data | Row2Column2data | Row2 ...... etc
Row3Column1data | Row3Column2data | Row3 ...... etc
.... etc...
_.......................

Read File to Excel or VBA Array
Using the same sort of things as above you can do a pseudo code
Open up _ FullPathAndTextFileName _ ForAnInput _ ReferenceNumberForThat”Route”
The speed of this code is not a major concern, as am expecting or hoping that the ADO or similar alternative will be better. ( That was the point of starting this thread – to get such a better code version ). But I will make some attempt to make the simple “VBA code way” fairly efficiently, As I want it as a realistic comparison.

So for the purposes of this test I will do a Function similar to the ones I am considering in the Thread here: , https://www.excelforum.com/excel-pro...liarities.html
The basic code idea is the reverse of the make text file code. You can do like pseudo code this:
A Line Input the _ ReferenceNumberForThat”Route” _ Put It In ThisStringVariable
Once again the VBA seems to automatically get the next one each time. ( Actually Line , I think, “goes” to the next line )
So you would do that in a Main loop and then also in the loop, usually, then you would _ Split ( ThisStringVariable , byIt’sSeperator ) to get all the column entries.
( In the code above for making the text file , the separator I chose was a pipe, _ “|” )
Typically you use the VBA Split Function which would take a row looking like this_..
Row3Column1data | Row3Column2data | Row3Column3data
_..and return you the data you want in an array, pseudo like:
Array( “Row3Column1data” , “Row3Column2data” , “Row3Column3data” )
I do it a bit differently by separating by the _ Separator _ just as far as I need to reach the column that I want to bring in from the data into an Array. In the example given above, say I want just the first column of data, that would mean I would split just as far as to get two bits returned from the Split Function ( https://www.mrexcel.com/forum/genera...ml#post4468324 ) and so end up with
Array( “Row3Column1data” , “Row3Column2data | Row3Column3data” )
From that I would just take the first element, “Row3Column1data” , and put that in the next row of my Array to which I am Referring the data to....
_....
_.... but check that all out here for more exact details, and description of the actual code example that I have done:
https://www.excelforum.com/developme...ml#post4637998
_ I have included a calling Sub routine, Sub TestieInputText() , so as to make a full standalone demo. Also the text data file from which the Array is filled is included.
So that is a good start point for anyone wanting to do a similar reading of a column of a text File into a VBA Array.






_........................

So those two codes were by way of a refresh and alternative code examples to the original ones I gave in this thread in post #1 last September.
But they are all based on the simple basic “VBA type” codes.
The main original question of this Thread was to do it better... for example with ADO.
So I A DO have a go at that now..... then report back ( or ask for help if I get stuck.... )


Alan


_......
EDIT for my later reference : some initial speed comparison for the last read text code
https://www.excelforum.com/developme...ml#post4641796

So here is my first attempt at a ADO code to read the text from .txt File ( with pipe, _ | _ , separators )
Clearly it has been done a lot before, but the syntaxes all seem a bit different and jumbled up...
I am almost there... This Post is and attempt by me in a fairly light hearted and from a Layman’s view point to explain what the “ADO way” is all about..
If you are one of the gifted few that understand everything already, then you might want to skip this and go to the next post, where I run more briefly through a specific example run and discuss some remaining issues....

Here is a description of the basic codes I have done ...
( The codes I have here from about Post # 290 to post # 296 https://www.excelforum.com/developme...ml#post4641842 https://www.excelforum.com/developme...ml#post4643062 )

Introduction General code approach Function and calling Sub routine.
It suits me to do a Function, but..
For this thread I will keep the code “standalone” for the benefit of future reference for anyone lurking this post. So to aid that I have a calling Sub routine , Sub TestieInputTextADO()
That Sub routine takes into the Function as main argument the Full Path and File name of the text file. A few other things, some optional are also taken it:_..
the FullPathandFileName of the text file that I want to get my data out of, ____ strFullPathAndFileName
an array As somewhere to Refer the data, ( the data from text file is put in this) arrBk()
the separator ( I use typically a __ | _ ) , _________________________ Sep
and then three Optional arguments for info on the Header and range of data I might want.

Please Login or Register  to view this content.

( The last three arguments I may experiment a bit with, and the separator still has some issues. )

Introduction getting at the programming processes of the ADO form...
General approach ( The following dribble is a bit light hearted based on what I learnt recently in the other thread... but it helps me get a feel for what is going on... )
This is all background stuff in attempt to explain what an “ADO” code is as opposed to any other sort.
Rem 1) ADO
So in this code version I want go partly outside of Excel to use a Windows application program interface (API) to do with having a sort of active at the time database. These API programs are often supplied , for example with Windows. ( The idea is that people will agree to keep their software “callable through these interface programs, regardless of if they change them , so that you have a better chance of using them through this route as directly )
But I am still fundamentally in Excel, or at least Excel VBA, ( and personally am not too clued up on how to use directly these API things.. ) ...
So .. what is available to me ???
There is since about the 1990’s an openly available database connectivity (ODBC) application program interface but I want something a bit more optimum for Office stuff, .. - a bit more in the direction of object orientated programming.
Microsoft have done some object linking and embedding database (OLEDB) programs , but they are not so easy for an end user to get at and use.
So Microsoft did try to make their Excel Files generally more compatible and readable with the internationally agreed structural query language ( SQL) for databases , and about that time they also tried to come up with something that would somehow work with those commands on various of the API’s and similar software. By this time all the computer Graduates were already totally confused. They had no idea where what they were doing fitted in anywhere, and started using terms like “ Driver “ , “ engine “ or “ provider “ just to fill in the missing spaces in sentences: Think of what drives a car. The driver or the engine. Who is the provider of, or driver of what. Is the engine belonging to? - the car, a series of cars, a series of engines, and even so, it can be used, whether or not planned originally , to drive something else ..They got mixed up with what opening turning on and connecting meant also ... Hmm.. .. Well, So .. then .. Microsoft .. also tried to come up with something that would somehow work with those commands on various of the API’s and similar software:...They came up with a new name ...
joint .. “engines” .. for want of a better word .. programming technologies (JET) for “driving” .. for want of a better word these things.. ( A bit later they did some similar stuff and optimised it a bit towards Microsoft ACCESS, and called it Access Connectivity Engine (ACE) )
So what I need is a way of getting to use the oledb from Microsoft through the Microsoft Jet . Pseudo I need
________ Jeton _ . _ oledb
So I need to have access, in my VBA object orientated programming environment of some library if possible which will give me things like objects which will let me present initially some statement in an object orientated hierarchy stylio of pseudo

Microsoft’s . Jeton . OLEDB . XXX activeopenedatthetimemytextFile ; and argumentsto ; ; pass in sql Format:=

I need ideally a suite of objects in a Library to make this main “connection” as it were, _..

1 (ii) ObjConnect ___ ”{ Microsoft’s . Jeton . OLEDB . X C: \ \ \mytextFile.txt ; _ ; _ ; ; }”

( I want to then apply that to my database, so the objects I need will have to associate or refer to my active X .xx Q&*?&%* database data object database. ) _..followed by some processing on that “opened up active at the time” database... or working with that ObjConn*** ... So a general area of interest to me now, and many people previously, is/ was active database objects.. so there is the word ADO. I think I know what I might be talking about now ##

There seems to be one available , an Microsoft ActiveX Data Objects 2.5 ,
1 (i) ADO DataBase Objects so in my code I will add reference to that by putting a check against it under Tools -- References , and look for some ADO DB type things/ objects... of the form I am looking for....
I expect I need initially like a main thing / object to do some sort of
ADO DB connection of / laying wide open a active “highway / route – the previous:

1 (ii) ObjConnect __ ”{ Microsoft’s . Jeton . OLEDB . X C: \ \ \mytextFile.txt ; ; ; }” = ObjConnected/OpenedTurnOn

1 (ii) ObjConn _ . _ TurnOnOpenConnect___ ”{ ___ JET.OLEDB.Info On File }” = ObjConnected/OpenedTurnedOn
( “Hey” , said Bill Gates , “Quick, let’s call that “{ __ }” a connection string just to keep them all confused )
Then I expect I need another object that is not much more than a set of data , a subset or all of what is available. I expect this object, whilst a VBA / OOP compatible thing, to have integrated into it a lot of, if not all possible SQL commands. The SQL commands I expect to be needed somehow to define the what data I want.

1 (iii) Set/Open DataSet _ sqlcmd _ ObjConnected/OpenedTurnedOn

Rem 2) Do stuff with the RecordDataSubSet , RuedtrSet

_.............Googling and playing around I seem to have all what I am looking for ..... all my made up names seem to actually exist in the Microsoft ActiveX Data Objects 2.5 Library ......
The only thing Microsoft got wrong in there naming was Recordset. I can see no reason why not to call it DataSet or RecordAndFieldSet.
But I guess with all the random use of words they use having forgot what they meant, (if they ever did mean anything) , then I guess we should be relieved they got any where...
_............

Next post, the same, a bit simplified... and more details to the actual first code attempt which .. well sort of works..

So I did a “ADO” .. after i’d figured out what that was all about in the last post.. (But it don’t like me pipe )

Following on from the last post, here is a more concise and brief run through the “ADO” code I have done for getting data from a “column” in a text file. ( “ column “ here would be regarded as all row data for between the separator used.
I will run thought the codes ( calling Sub routine and Function ) and discuss some issues I am still having. Someone may be able to help me with the issues here, or in the other Thread which I need the working example from here to proceed with..

I had/ had issues with untidy large strings ( which included commas and carriage returns in ) in the first 20 rows in my original Excel data File, that I use for multiple spelling of headers.
So for now, to get further, I re did the codes to write to the text file ( from post #5 Make Text File https://www.excelforum.com/excel-pro...ml#post4641706 ). I rewrote the code to produce me a simple text file which only has the data from line 21. ( I also had to do a find and replace on the main Excel data file ( “DBSept2016 - first AlanSucces.xlsx” https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t to replace all commas in text with a underscore _ as one of my current issues is that Ii can’t seem to customise the separator. ( That new Excel data file is “DBSept2016 - first AlanSucces_.xlsx” https://app.box.com/s/636s865mu8qx17q813xpb1wtp0t3egbj
I have all File and code details to that here: https://www.excelforum.com/developme...ml#post4642687 and here https://www.excelforum.com/developme...ml#post4642889

So
code run through.( Codes are here https://www.excelforum.com/developme...ml#post4642984 and here https://www.excelforum.com/developme...ml#post4642994
I assume for those reading this you know at least as much as me, about “”ADO stuff”. ( That should qualify most.. Lol... )

Sub TestieInputTextADO() is the start point, once you have this text File ( "DBSept2016_FromLine21Comma.txt" https://app.box.com/files/0/f/254647...f_165476771304
( boxDBSept2016_FromLine21Comma.JPG http://imgur.com/R1dKUJb )
in a Folder named "DBTextFiles" . That Folder should be in the same Folder as the File from which you run the codes.

The sub routine takes into the Function the _ Full Path and Text File name , _ then the _ Path to Folder DBTextFiles _ and the name _ DBSept2016_FromLine21Comma.txt _ are derived This is done because the “ADO” code seems to need the entire Folder as the thing it has as a main “layed .Open” object, from which a sub data Set ( Recordset) can be referred to in terms of selecting parts thereof the actual text File.

'1(ii) “Conncetion String”
So for informed readers I have a sql connection string looking like this:_..

Please Login or Register  to view this content.

_.. that string , I think, defines what is to be “.Opened up/ layed open”)

(**Because I am using a OOP library of objects ( Microsoft ActiveX Data Objects 2.5 ) for doing this, I took the liberty of doing an alternative Function version so as to assign Properties to the “to be “.Opened up/ layed open”” object.
Function InputTextADOOB https://www.excelforum.com/developme...ml#post4643061 https://www.excelforum.com/developme...ml#post4642994

Please Login or Register  to view this content.

)


_.__________________________-

'1(iii) DataSet:
A couple of options for the sqlRecorDataSubSet command string I have tried with partial success are shown here in line 440 and 450

Please Login or Register  to view this content.

So I was quite relieved when all that worked.... well sort of worked...._....

_.....These are the issues....if anyone can comment / help here then great.. otherwise I will continue this later today or tomorrow in this Thread ( https://www.excelforum.com/excel-pro...ml#post4640951 )

Remaining Issues:

_ A) If I choose the ##header option , then .MoveNext seems to be ineffective, and my Loop never ends.. ( I never seem to move on )

_ B) .RecordCount always returns -1 ( Line 490 )
( So I could not use that to determine the data row size and my final Array size for the data. - To get the code to work, I Loop while I am not at End of the data file, and put the data into an Array List. Finally I do a .ToArrray to make my array ( I am aware of the .GetRows() or .CopyFromRecordset but I would like to use the records loop way first.... ) )

_ C) I can’t seem to change my separator from a comma to a pipe. Strangely this syntax is accepted
FMT=Delimited(|). But it seems to have no effect.
I have spent sometime reading up on the Schema.ini File. I have experimented with that quite a bit ,
For example in Folder "DBTextFiles" which contains the text files I have a file named , SCHEMA.ini
SCHEMA_ini.JPG http://imgur.com/14LZxIJ

Example 1) - It has this in it:

[DBSept2016_FromLine21Comma.txt]
Format=Delimited(|)

For the above combination , the inclusion of the SCHEMA.ini file causes my the code to error (“DBSept2016_FromLine21Comma.txt” is a comma delimited file. http://imgur.com/OeydZVw ) The error says that for at least one parameter no value is given. I was expecting it to take a single line as the entire [F1] “column” in that case. The error occurs at line 460

Please Login or Register  to view this content.

Example 2) It has in it:

[DBSept2016_FromLine21.txt]
Format=Delimited(|)

(“DBSept2016_FromLine21.txt” is a pipe delimited file. http://imgur.com/OeydZVw )
I run the code but use text File "DBSept2016_FromLine21.txt" . Once again an error says that for at least one parameter no value is given. Once again the error occurs at line 460

_........
I will keep trying...







https://www.autoitscript.com/wiki/AD...tring_TextFile
http://windowsitpro.com/windows/read...iles-using-ado
https://docs.microsoft.com/en-us/sql...xt-file-driver
https://www.connectionstrings.com/textfile/
https://www.exceltip.com/import-and-...oft-excel.html
http://www.freevbcode.com/ShowCode.asp?ID=2180
http://www.java2s.com/Code/VBA-Excel...ilewithADO.htm
https://msdn.microsoft.com/de-de/library/ms974559.aspx
http://www.excelforum.com/developmen...ml#post4484332
https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/ms972319.aspx
http://www.snb-vba.eu/VBA_Arraylist_en.html

Hi
I am sharing the solutions, at least to a large part of the “ADO” side of things, as I can do that now thanks to some answers in another Thread ( https://www.excelforum.com/excel-pro...ml#post4645694 )

First the brief answers to the outstanding 3 issues in the last post, then a code and descriptions to probably wrap up the thread to a large extent
From last post Remaining Issues:
_ A) The was some combination of the other issues...
_B) Getting .RecordCpunt to work. -- Solved – works!
It was probably more a question of why it did work on some previous codes. The detailed solution and explanation will tell us that we are dealing with a cursor which goes “along” Fields ( “columns”) and then “down” Records ( “rows” ) . That needs to be understood and defined appropriately. ###
It is interesting to note here that some googled answers suggested this was not possible. Those were most likely incorrect.
_C) Change separator to pipe | -- Solved – Done!
Use and understanding of a “schema.ini” is required.

Detailed solution based on the code discussed in the last post that did not quite do what I wanted due to the issues. Now it is better.
Codes are here: ( https://www.excelforum.com/developme...ml#post4646145 https://www.excelforum.com/developme...ml#post4646144 )
This codes are I give here are intended to be a standalone to share and help others, so the Function has a calling sub routine and all files are given.

The code uses the external library Microsoft ActiveX Data Objects 2.5. This is an Object orientated for DataBase work library package intended to help use the “ADO” stuff within a VBA environment. ( Sometimes referred to generally as ADODB stuff )
( “ADO” stuff was discussed in detail here https://www.excelforum.com/excel-pro...ml#post4643207 and here https://www.excelforum.com/developme...ml#post4641842 - But note: the first code introduction bit can be ignored as I got the stuff to do with the separator, | , wrong )
A by product from use of ADODB is that a sql code string containing information can be built up through assigning properties to ADODB Objects. Pseudo: this Method _...
obADODBcon.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=_.........etc...”
_... can be alternatively written alternatively in Properties and method so
obADODBcon.Provider= “Microsoft.Jet.OLEDB.4.0”
obADODBcon.ConnectionString = “Data Source=_.........etc...”

obADODBcon.Open
This is then an alternative to using a Method which contains the string of information. This can help in a learning and demonstration code, so that approach is done in this code.

Introduction new/Improved
Using a schema.ini.
You didn’t get ya “schemi.ini” right. That was the main issue. So: A Do OB Pipe it right :-)
Clearly such codes are used somewhat “blindly”. It would appear that default Attributes/Properties associated with databases may be present at some location, only some of which can explicitly given in a code, and can / or must be given in an extra “schemi.ini” file.
It seems a good idea, now that I know about it to use this option rather than rely on the Default. Indeed it comes out that only with this can I change the default separator to pipe _ | _ as opposed to the default comma _ ,
The File needs to go in the same Folder as the text File to which it refers. This makes sense as the Layed .Opened connection object for a text to Excel VBA “ADO” code is that Folder. So effectively you are adding to the effective database. ( *** @ Kyle – hey watch me go! ... I am using databases ! )
I can make an entry for any test data File, for example my currently used pipe delimited File discussed in the last Post as well as tested initially here:
DBSept2016_FromLine21.txt Pipe Delimited: ( 17.8MB ) https://app.box.com/s/3bbscsxgfpv3afbne8ndukcx7elflp96
https://www.excelforum.com/developme...ml#post4643065
This File “schema.ini” is in the Folder DBTextFiles , and so is the data File “DBSept2016_FromLine21.txt”
Once I have such a fschema.ini file, and refer to a .txt file in it, then usually , ( depending on things like registry settings ), many attributes/Properties given in the sql string for defining the Layed .Opened connection will be ignored. They must therefore be included in the schema.ini File.
Some more detailed understanding is therefore required of “ADO” things, so as to know what must be included.
“Schema.ini” attributes / properties to be given.
Delimiter
The main issue regarding the previous code not recognising the | as delimiter is that this can only be given in “schema.ini”, at least that may appear to be the case for typical computer settings.
Column ( Field ) Info
The “ADO” object used in reading / returning the actual data that I want is organised in a typical database way, whereby a cursor is used to refer to a specific data element. At any one time the cursor “stands” at a point within a current record ( row ) . The current record ( row ) in which the cursor “stands” is the actual object Record set, oRst. So at any time , the object Record set, oRst refers to a particular Record (“row”).
Columns must be defined up until at least the column required, or the “cursor”” is unable to navigate. ( The “connected route” is broken: oADOpey Connection Wonks: https://www.excelforum.com/the-water...ml#post4636789 )
This should be satisfactory for defining. ( Well I got some satisfaction http://listenonrepeat.com/watch/?v=nrIPxlFzDi0 )

Please Login or Register  to view this content.

Brief Codes descriptions
Codes here: https://www.excelforum.com/developme...ml#post4646144
https://www.excelforum.com/developme...ml#post4646145
Calling code Sub TesiesADOOBP()
The main difference to the previous code is that the separator is no longer passed to the Function, as this cannot be assigned usually in the assignment of attributes/ Properties to the sql string used in the making of the .Opening to lay open / make available / connection open main full data object, ( sql connection string ). We discussed above that this can be defined in an included “schama.ini” File.
The main parameter taken in is the Full path to and including the Text File name
_...............
Function InputTextADOOBP(

Rem 1) “ADO” Things.
‘1a) Laying .Opening up of the main databaseic object, oCon
ADODB.Connection object
The first main object used was described in some detail in the previous posts. I might just add that this will clearly contain other information as well as the main (“Table”) available data. We see now for example it includes schema info, be it as we have defined or in default Registries etc...

'1b) RecordSet object
I can explain this second object in a bit more detail now. So I will in the next post

Continued from last Post….
_.........

RecordSet object, oRst

'1b) RecordSet object
I can explain this second object in a bit more detail now. I had been concerned with the name Record set as opposed to data set. But CK76 set me right ( https://www.excelforum.com/excel-pro...ml#post4645593 )
The RecordSet object will generally refer to a single Record ( “row”) . generally at any one time the actual object referred to will be a Record ( “row”). One often sees things like this in organising of data in computers. For example when organising tables in HTML code we generally list rows and then talk of cells as things within such rows. Hence the use of Record in the name, which originally confused me. So, as often seen, the variable used for it will take a form similar to oRst
Having defined / assigned / Dimed the object we can consider generally two code sections in its use in such a code as that under consideration. This two stage process can be seen when considering how the usage is organised. Or in other words how generally Recordsets are handled
In some extent a similar “.Opening / revealing , making available of a subset of the main oCon object is done, but with some more emphasis on the actual ( “Table” ) data
An important concept is the idea that a cursor is “held” somewhere, but that there are some subtleties to exactly where that might be, or to what , it might be referring at any particular time. It is a sort of structure used to move/ transverse around data. In addition
The two stages can be thought of as
_1b(i) setting / .Opening up a structure for transversal
This will include
_ defining route map / matrix / path “taken” for the cursor ( affectively indirectly defining the actual data, but without actually “getting data”
_ a reference to the main databaseic[/I] .Opened currently object, oCon
_ some extra arguments to define/ control updating of the “cursor” and data.
_ Rem 2) Various Methods and Properties can be applied to the Recordset object to retrieve actual data
_.............
_ _1b(i) [I] ADODB.Recordset object

Once again I have chosen in the code to set properties through object refs instead of through arguments to Open method
As noted, I do indeed have an object here, ( see sketch: https://www.excelforum.com/excel-pro...ml#post4647404 ) .
The exact behaviour is still being discussed here: ### ( https://www.excelforum.com/excel-pro...arities-5.html )
In general we are talking about a Set of data with a defined through .Open ing cursor transversal through data Fields. Set properties through object refs instead of through arguments to Open method, as in out With End With shoes that we are defining the parameters/ attributes / properties to define this object. Clearly we are talking about the “object” as some abstract concept, although somewhat vaguely and imprecisely it is said that we are at any one time referring to a specific Record ( Row ) at any time. .. maybe that has some logic from the next code section..
_ Rem 2)
Think of a RecordSet object as a long thin boat on an ocean of data with a pre defined route, but more precisely it is on a stairway ( https://tinyurl.com/letjpns ) , but before it was there it might have had some sections cut out and then pieces welded back together. Or actually just a blue print plan where a left – right cursor goes along the horizontal, and the whole horizontal can be shifted up and down. Just like an old fashioned printer. So I suppose it is OK to sort of say two things as regards what the RecordSet Object is: At the same time it is
_ oRst_a) a Record Row
_ oRst_b) the whole mystical database thing that a Recordset object is.
In my code oRst is at and is a Record, ( row) , typically. I can apply a property of this to it, for example , to move it, for example oRst.MoveFirst – I will do that at the start of Rem2) just to be on the safe side, although I expect I am already there, at the first “row”
This in Red is a RecordSet object. ( At any one time the Recordset object, oRst, refers to a “complete row” ( Record ). Various Methods and properties can be used on the oRst. o give actual data values )

( All screenshot is an .Opened available ready and waiting” Connection object )

Cursor F1	Cursor F2	Cursor F3		Cursor F4	Cursor F5
Cursor	Cursor	Cursor		Cursor	Cursor
Cursor	Cursor	Cursor		Cursor	Cursor
Cursor	Cursor	Cursor		Cursor	Cursor

Recordset .Open will define where Cursor can traverse, - all Cursor is where a Cursor can go.

To rap up this thread, I will do a quick time comparison with and without the use of a ListArray. (The only reason I used an ArrayList is for convenience to not keep Re Dimig an Array for the data imported– In the final use I would like to pre define the size of the Array. Hence I wanted .RecordCount to work, - it does as that issue was sort of sorted – at least we know how to get it to work, even if no one yet knows why ).
So there will be two code versions, differing at this point, differing only in use of the ListArray for the data imported, or alternatively an Array , size predefined via .RecordCount, for the data imported

Some initial timings in seconds for the last two Functions, showing in particular effects of cursor options are given here:
https://www.excelforum.com/developme...ml#post4648994

Options tested in code:
With oRst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.RecordCount
Note: For Function InputTextADOOBP2( _ option .RecordCount must be used, as this is required to ReDim the Array to size of data to avoid ReDim Preserveing .
To get .RecordCount to work , .CursorLocation = adUseClient or .CursorType = adOpenKeyset is required.


Clearly the choice of the option plays some role. The winner is clearly
.CursorLocation = adUseClient

_..................................................
_.___________________________________________________

I expect that my be close to it for Solveding this Thread
I certainly have a got long way now for pulling a column of data out from a ….. bit of my data


_.............

There is still the question of getting this single column out in some VBA code driven query … I have seen it done I think..( https://www.excelforum.com/excel-pro...ml#post4336457 ) .. maybe I will re look at that in a year or two.
In case anyone is interested suggesting any other ways, then, just to remind of what I want and am testing in different ways to do this, .. It would be to get column A from an Excel File, or a pipe | delimited file of the same. Ideally the data would be put directly into an Array

Text data File "DBSept2016_FromLine21.txt"
https://app.box.com/s/3bbscsxgfpv3afbne8ndukcx7elflp96

Equivalent Excel Data “DBlc255Sept2016 - first AlanSucces.xlsx”
https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t

I already have done many variations of ways to do this ( Including a VBA driven MS Query( https://www.excelforum.com/excel-pro...-xl-files.html ) in this related Thread: https://www.excelforum.com/excel-pro...liarities.html

There are not many things left that I have not tried.

Alan