Greetings all!
I have a dilemma that I'm trying to have resolved within the next week or so.

The short of it is the following. I need to create a VBA code that will import a fixed width .txt document that has record lead indicators (at the start of each records) to indicate what that particular records pertains too. I need the VBA code to understand what the indicator is, then input the data into the correct cell of the table.

That's the short of it, now the long part.

Here is a test .txt file that I need to import:
http://www.geocities.com/diretemus/testfile.TXT

And here is a description of the file layout:
http://www.geocities.com/diretemus/t...sionlayout.rtf

The table this information will be imported into will have the following fields:
ProxyVoteID (Primary Key)
VoteDate (DataType - Date)
CUSIP (DataType - Number)
Class (DataType - Number)
RecordDate (DataType - Date [Format = YYMMDD])
MeetingDate (DataType - Date [Format = YYMMDD])
BRClientNum (DataType - Number)
DTCNumber (DataType - Number)
NomineeName (DataType - Text)
TotalShares (DataType - Number)
ProposalFOR01 - ProposalFOR99 (DataType - Number) 99 Columns to accommodate up to 99 Proposals
ProposalAGAINST01 - ProposalAGAINST99 (DataType - Number) 99 Columns to accommodate up to 99 Proposals
ProposalABSTAIN01 - ProposalABSTAIN99 (DataType - Number) 99 Columns to accommodate up to 99 Proposals
Director01 - Director99 (DataType - Number) 99 Columns to accommodate up to 99 Directors


Now as you can see each record has a lead record indicator:
I.E. If a record is lead by 401, we know the corresponding numbers to the right refer to Proposal1. We can then pull (based off of the fixed width) the values for ProposalFOR1, ProposalAGAINST1 & ProposalABSTAIN1.

What I need this VBA code to do is, once it hits a record lead by 250, it should import the data in the text document based off of the next records until it hits another record lead by 250, at that point it should start importing the information into a new record of the table.


I've been trying to find information inside these WROX VBA Access books specific to importing text documents that are fixed width and I can't seem to get it.

If anyone can lead me in the right direction, I would greatly appreciate it! :)

Thanks for all the help!

You are going to need to use a Scripting.FileSystemObject from your VBA code, then open the text file, then read one line at a time. That can be found in basic Windows Scripting books. I can post that code if you need it. As you read each line, you would do this:

Dim fso As Variant
Dim objStream As Variant
Dim sLine As String
Dim sClass As String
...

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\testfile.txt") Then
    Set objStream = fso.OpenTextFile("C:\testfile.txt", 1, False, 0)
End If
Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    sClass = Left(sLine, 3)
       Select Case sClass
         Case = "250"
           'ReDim 250Array
           'Parse string using <TAB>
         Case = "401"
           'ReDim 401Array
           'Parse string using <TAB>
         ...
       End Select
Loop

This will get your data and classify it. I am not sure if there is an end of record mark, etc. But this will get you started. Then open a recordset and start putting data in based on the array.

Alternatively to the array, you can create an unconnected recordset in memory and build your table there, then put the unconnected recordset into your table. etc.

There are lots of ways to do this. We just need to know what each line is significant of, and if you will need to parse the strings that have the "{" in them, etc.

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com

MMC,

Thanks for the help.

Below is the list of what exactly each Record Lead means and in what section of the table the value should be imported.

ProxyVoteID = AutoNumber (Primary Key)
VoteDate = Record Lead by "100" - Position is 55-60
CUSIP = Record Lead by "200" - Position is 6-11
Class = Record Lead by "200" - Position is 12-14
RecordDate = Record Lead by "200" - Position is 56-61
MeetingDate = Record Lead by "200" - Position is 62-67

Now the Record Lead by "250" (as shown in the text file) will appear multiple times (there can and will be many different records lead by "250"

The trick is this. Every time a line starts with "250", it indicates the need to start a new record in the table because this indicates the start of a New Banks/Brokers Voting. The above information will stay constant. (i.e. VoteDate, CUSIP, Class... etc.) but the Proposals Votes pulled from the text should be unique to each "250" (if that makes sense)

Moving on:

BRClientNum = Record Lead by "250" - Position is 6-8
NomineeName = Record Lead by "250" - Position is 19-38 (Left Justified)
DTCNumber = Record Lead by "250" - Position is 73-77

TotalShares = Record Lead by "3__" - Position is 19-33 (TotalShares is constant no matter how many Records Lead by "3__" there are)

Proposal = Record Lead by "3__"
ProposalFOR1 would be Record lead by "301" - Position is 35-49
ProposalAGAINST1 would be Record lead by "301" - Position is 50-64
ProposalABSTAIN1 would be Record lead by "301" - Position is 65-79

Proposal = Record Lead by "3__"
ProposalFOR2 would be Record lead by "302" - Position is 35-49
ProposalAGAINST2 would be Record lead by "302" - Position is 50-64
ProposalABSTAIN2 would be Record lead by "302" - Position is 65-79

Proposal = Record Lead by "3__"
ProposalFOR50 would be Record lead by "350" - Position is 35-49
ProposalAGAINST50 would be Record lead by "350" - Position is 50-64
ProposalABSTAIN50 would be Record lead by "350" - Position is 65-79

Directors = Record Lead by "5__"
Director1 = would be Record lead by "501" - Position is 36-50
Director2 = would be Record lead by "502" - Position is 36-50
Director40 = would be Record lead by "540" - Position is 36-50


That's the meat and potatoes of this import!

Also,

One last thing to note, the "{" should not be imported.

I will also post a .mdb of my table design and I want to see if this is possible. (There would be three tables)

Can you send me the .mdb of the table design in a zip file? Also, put a sample text file in there too.

I am not sure what this means:

VoteDate = Record Lead by "100" - Position is 55-60
CUSIP = Record Lead by "200" - Position is 6-11
Class = Record Lead by "200" - Position is 12-14
RecordDate = Record Lead by "200" - Position is 56-61
MeetingDate = Record Lead by "200" - Position is 62-67

What is "Position is..."?



mmcdonal

Look it up at: http://wrox.books24x7.com

Hey MMC,

I'll send you that sample MDB ASAP.
When I mean by "Position is..." is the position where the text is located.

For instance:
CUSIP = Record Lead by "200" - Position is 6-11

The data that needs to be entered into the field called "CUSIP" is located on the line that starts with a "200" and the actual date is located from space 6 to 11.
(e.g if the string of data says, 324141234560439, the data to be entered into the CUSIP field would be 123456)

Hopefully that clears things up.

Joe

That would be easy to take those characters then, you would use:

Mid(String, 6, 6) to take "123456".

Of course, first you have to break that string out.

Will this ever change?

mmcdonal

Look it up at: http://wrox.books24x7.com

The format is consistent. The only way the file would change is that there can be multiple records lead by 250. Then under each line lead by a "250" you will have multiple lines lead by records 300's and 500's.

The try is this.
Whenever a line starts with "250" think of it as a new record in the table, the lines lead by "300's" or "500's" are the data that needs to populate that record in the table. When the new line lead by "250" is hit, you need to start a new record in the table and start the process over again, until you hit the line lead by 900 (End of Record)

Hope this helps!

MMC,

The Database Table design + text file is at the below URL.

Take a look.

Thanks,
http://www.geocities.com/diretemus/db1.zip

One other thing MMC, when you said will "this ever change"... I just wanted to clarify that the format of the data will stay the same, but the data itself can and will change.

So the script needs to look for data between a certain range on a line and grab that information.

Any other further assistance would be GREATLY appreciated, I need to have something working by Friday if possible. :(

If anyone is even willing to make a few bucks off this I do not have any quams about it!
Contact me at [email protected] if you desire!

Thanks,