how to build an Access database from a external report

[jimjims]

History/background: We have an older system that generates txt reports. I take these txt reports, then using Excel, I append the report headers to each record... creating a datatable. I then import this table to Access. I have not been able to figure out how to have Access perform this. In Excel, I use an "if" statement.



Please... any thoughts or direction would be extremely appreciated. Even "off the top of the head" stuff. I'm stuck.

[Robeen]

It is not clear exactly what you need to do.

Could you clarify the following?
1.

I append the report headers to each record

Do you mean you add 'Field' headers for each field?

2.

I have not been able to figure out how to have Access perform this. In Excel, I use an "if" statement.

What do you want Access to perform? The Import - directly from the .txt file?
What are you using the IF statement in Excel for?

Could you give a couple of examples of your data?

[rpeare]

I thought I'd replied to this but my response seems to have vanished!

You are not creating a database by inserting a row of headers, you're just creating a file with headers. In Database terms you're making a TABLE. If you are attempting to import an excel file into access you do not need the header row. Assuming the original import file is always in the same layout you can use the:

docmd.transfertext

method.

You would just have to define the table you want to import the data into, the data types of each field and create an import specification to reference when you performed the import.

[jimjims]

Thank you for the response! Here is basically what I'm trying to accomplish. I'm trying to re-engineer the data table that was used to build the report. So that I can query the information differently and use it with other table I've built.

to help answer your follow up questions... The report is large. It's a system access report. The Header information shows an "area/application". Below that are the users that have access to the "area/application". In order to create the data table, I need to append the "area/application" information, next to each user listed. When importing the txt in either Excel or Access, all the lines in the report are treated as seperate records all in one field. I use an if statement in a column next to the imported txt records. The if statement tests if the record immediately to the right is a header record, if so, then it returns the header record value, if not it returns the value of the if statement directly above. It's hard to explain, but the first if statement is put next to a header record, thus returning it's value. then when I copy the formula down, it will place the header information next to each record, until it encounters a new header record and the process repeats.

I do this because the old system doesn't have a clean interface and we have limited IT support. The excel step works, but it is not clean. I've looked into macros and SQL, but these are not my strong points in Access. Hoping someone else might have had this issue.

[rpeare]

Can you give an example of your starting data and what you want your end result, in the access table to look like? You will, in all likelyhood, use vba to parse your data depending on the complexity of the formula.

[jimjims]

Here is the txt file and excel files examples.

Thank you so much for your time with this. I've been trying to figure this out, off and on for about 2 years.

[rpeare]

I'm sorry but these screen shots are really really blurry to the point I can barely make out any text, can you upload the same two files (zip them together) with your sample data included?

[jimjims]

amzatpfp mod.txt mapic file fromated by mod.zip

Sorry about that... still learning this sites tools.

[rpeare]

Ok I've got the two files. I see you're doing some text manipulation in Excel, but what you're showing can't possibly be what you're attempting to import into access. What information on the actual text file are you attempting to get into an access table. There are ways to parse text files, extract the values you want and insert them into access tables but you'll have to have a good idea of how you are going to recognize text. There are a lot of 'blank' values on your text file which I assume are actually present on the real report (Application, Security Area, Authorized to Tasks, Task, Task Locked, Task Access, Task Authorized, User) Are you just trying to extract from the AOR line

AOR Display Production Receipts SEC CAS Y as a single string or is it your goal to have those as separate fields in an access table, further, are all of the values in ACCESS, AUTHORIZED, USER ID and NAME going to be part of that table?

If your goal is to get anything from the AOR line to the end of the report into an access database table you'll have to do some text file manipulation and I'd suggest using filesystemobject.

Also, what happens if you have a report that goes for multiple pages, are there formatting breaks on the text file?

the basic setup for parsing a text file with filesystemobject commands is this:

dim fs
dim fsFile
dim sline as string
dim sTaskID as string
dim sDesc as string
dim sType as string
dim sApp as string
dim sLocked as string
dim sAccess as string
dim sAuth as string
dim sUser as string
dim sName as string
dim db as database
dim sSQL as string


set db = currentdb
set fs = createobject("Scripting.filesystemobject"
set fsFile = fs.opentextfile("C:\amzatpfp.mod.txt")

sline = fsfile.readline

do while fsfile.atendofstream <> true
     If left(trim(sline),3) = "AOR" then
          sTaskID = Trim(Mid(sline,23,10))
          sDesc = trim(mid(sline,46,40))
          sType = Trim(Mid(sline,98,4))
          sApp = Trim(mid(sline,104,4))
          sLocked = Trim(mid(sline,112,1))
          fsfile.readline 'Skips the line with the column headers for the detail of access, authorized userID and name
          fsfile.readline
          do until (insert a test if there are page breaks with other text any other reason to kick out of this loop)
               sAccess = trim(mid(sline,48,1))
               sAuth = trim(mid(sline,61,1))
               sUser = trim(mid(sline,74,10))
               sName = trim(mid(sline,86,12))

               sSQL = "INSERT INTO MyTableName (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9) VALUES( "
               sSQL = sSQL & "'" & staskid & "',"
               sSQL = sSQL & "'" & sdesc & "',"
               sSQL = sSQL & "'" & sType & "',"
               sSQL = sSQL & "'" & sApp & "',"
               sSQL = sSQL & "'" & sLocked & "',"
               sSQL = sSQL & "'" & sAccess & "',"
               sSQL = sSQL & "'" & sAuth & "',"
               sSQL = sSQL & "'" & sUser & "',"
               sSQL = sSQL & "'" & sName & "')"

               db.execute ssql

               sline = fs.readline
          loop
    Endif
    sline = fsfile.readline
loop

set db = nothing

You'd obviously have to put in your own table and field names to make it work AND put in a secondary test that will kick you out of the SQL portion IF this report has mutliple sections. I haven't tested the code so there may be typos or syntax errors but it's the basic framework.

[jimjims]

I'll give this a try. I was doing the parsing in Excel. My thoughts were that replicating my Excel "if" statement process would be the most difficult part to replicate in Access. But you are correct, I have to "clean up" the data by deleting all non-data records that this process creates (page number lines, blank lines, etc.).

Thanks again!