If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
parsing from a txt file into a table
i have received a bit of help in other categories trying to get to the route
of the problem.. a solution was developed but i don't really know how to program. i can import my TXT files fine into the table and i receive 2 fields.. field 1 for left of the colon and field 2 for right of the colon everything looks great.... is there a simple script to run that will convert the data from the 2 fields in to records? so that i can have them on my table? like the field columns would look like.. Company|Status|BTN|Contact|Term|Start|End|MARC and the data from the right of the colon would parse into those fields?.. using Contract Information: as the delimiter to begin a new record? here is what my data looks like..... Contract Information: Customer Name: client Contract File Number: 1111111111111 Status: Expired Master BTN: 9999999999999 Customer Information: Customer Contact Name: bob bob Customer Contact TN: 1111111111111 Contract Dates: Term Months: 60 Start: 10/29/1998 End: 10/29/2003 Received Date: 10/27/1998 Effective Date: 10/29/1998 Expiration Date: 10/29/2003 Input: 10/29/1998 Signed: 10/19/1998 Revenue and Contract Information: MARC: $4,713.80 Business Unit: Large Business Services Type of Customer: Authorized Distributor: Revenue: $23,569.00 Promotion: 0 Product: DS1 Contract Information: Customer Name: client Contract File Number: 1111111111111 Status: Expired Master BTN: 9999999999999 Customer Information: Customer Contact Name: bob bob Customer Contact TN: 1111111111111 Contract Dates: Term Months: 60 Start: 10/29/1998 End: 10/29/2003 Received Date: 10/27/1998 Effective Date: 10/29/1998 Expiration Date: 10/29/2003 Input: 10/29/1998 Signed: 10/19/1998 Revenue and Contract Information: MARC: $4,713.80 Business Unit: Large Business Services Type of Customer: Authorized Distributor: Revenue: $23,569.00 Promotion: 0 Product: DS1 |
#2
|
|||
|
|||
parsing from a txt file into a table
may be other ways to skin a cat... if I was doing this, I'd import the file
into Excel then reformat it as a database then pull it into your access fields. Look on the Excel Disc Groups for 'formatting columns into rows for a database'? or somehting like that... may take a while to figure it out but I learned that doing some pre-import formatting in Excel was easiest for me to import odd data constructs... maybe someone else here can help you format that columnar data into access tables... I'd like to know how also. "brokerluke" wrote: i have received a bit of help in other categories trying to get to the route of the problem.. a solution was developed but i don't really know how to program. i can import my TXT files fine into the table and i receive 2 fields.. field 1 for left of the colon and field 2 for right of the colon everything looks great.... is there a simple script to run that will convert the data from the 2 fields in to records? so that i can have them on my table? like the field columns would look like.. Company|Status|BTN|Contact|Term|Start|End|MARC and the data from the right of the colon would parse into those fields?.. using Contract Information: as the delimiter to begin a new record? here is what my data looks like..... Contract Information: Customer Name: client Contract File Number: 1111111111111 Status: Expired Master BTN: 9999999999999 Customer Information: Customer Contact Name: bob bob Customer Contact TN: 1111111111111 Contract Dates: Term Months: 60 Start: 10/29/1998 End: 10/29/2003 Received Date: 10/27/1998 Effective Date: 10/29/1998 Expiration Date: 10/29/2003 Input: 10/29/1998 Signed: 10/19/1998 Revenue and Contract Information: MARC: $4,713.80 Business Unit: Large Business Services Type of Customer: Authorized Distributor: Revenue: $23,569.00 Promotion: 0 Product: DS1 Contract Information: Customer Name: client Contract File Number: 1111111111111 Status: Expired Master BTN: 9999999999999 Customer Information: Customer Contact Name: bob bob Customer Contact TN: 1111111111111 Contract Dates: Term Months: 60 Start: 10/29/1998 End: 10/29/2003 Received Date: 10/27/1998 Effective Date: 10/29/1998 Expiration Date: 10/29/2003 Input: 10/29/1998 Signed: 10/19/1998 Revenue and Contract Information: MARC: $4,713.80 Business Unit: Large Business Services Type of Customer: Authorized Distributor: Revenue: $23,569.00 Promotion: 0 Product: DS1 |
#3
|
|||
|
|||
parsing from a txt file into a table
You importing that data into a table, but each field + one line of data is
going into a different record, and how can you stitch this all back together that way? The only realistic approach to this is you going to have to sit down and write some code to do this, there's no simple way out of this. Given that you now shown what your sample data looks like, you can see that it's not one row data that makes up one record, but "many" rows of text data needs to makes up one record. because of this type of data structure, you're not going to be able to use the built in import routines to parse out this data. I would suggest that you ask the people to send you this data in some format like excel and therefore it would be able to be imported into MS access, if you don't have control over this data, then the next step is you're going to have to sit down and simply write code to parse out this data. in other words this solution you seek is going to require you to have some programming skills. the basic approach to open a text file in code is as follows: Sub ReadTextFile Dim strFile As String Dim intF As Integer Dim strLineBuf As String Dim lngLines As Long Dim lngBlank As Long strFile = "c:\my data\MyData.txt" intF = FreeFile() Open strFile For Input As #intF Do While EOF(intF) = False Line Input #intF, strLineBuf If Trim(strLineBuf) = "" Then lngBlank = lngBlank + 1 Else lngLines = lngLines + 1 End If Loop Close intF End If MsgBox "Number non blank lines = " & lngLines & vbCrLf & _ "Blank lines = " & lngBlank & vbCrLf & _ "Total = " & lngBlank + lngLines End Function In addition to doing the above, you'll have to write code to parse out each line of code into one record. This is not trivial task if you don't have coding experiance. the following code would give some of the basic idea of the challenge you have he Public Sub ReadTextFile() Dim strFile As String Dim intF As Integer Dim strLineBuf As String Dim lngLines As Long Dim lngBlank As Long Dim rst As DAO.Recordset strFile = "c:\my data\MyData.txt" Set rst = CurrentDb.OpenRecordset("tblName") intF = FreeFile() Open strFile For Input As #intF Do While EOF(intF) = False Line Input #intF, strLineBuf Do Until strLineBuf = "Customer Information:" And EOF(intF) = False Line Input #intF, strLineBuf Loop ' start a new record... Line Input #intF, strLineBuf rst.AddNew strBuf = Split(strOneLine, ":")(1) rst!CustomerName = strBuf Line Input #intF, strLineBuf rst!ContractFileNum = strBuf ' .etc .etc .etc rst.Update Loop Close intF rst.Close End If End Function End Sub The Above is by no means a solution, but is only a general shell or skeleton of an outline of the type of approach and code that you'll need to develop to solve this problem. (so, the above is "air" code that I written off the top of my head when writing this post..but, it is the general idea and are approach to the type of code you'll need here). -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
Thread Tools | |
Display Modes | |
|
|