Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47

    Question Importing Flatfile with Header and Trailer that needs to be updated and exported


    Hi All:

    I have a database that's designed to pull in a flatfile. This flatfile already has a header in it that has a 3-digit code that doesn't change with yyyymmddhhmmss. So the first line in this text file looks like this: CC5201607151400.

    The rest of the data is fixed width. Then there is a trailer record that has 2-digit no changing code and six 0's (zeros) , the record count, and twelve 0's(zeros), a decimal point and two more 0's(zeros)

    I have a fixed width import spec that works fine for the actual data in the file but it also splits up the header and trailer into nonsensical breaks for a user to look at. After the user updates the file in the form in the DB, only the changes are exported in the SAME format as it was received, but the header has to have the new date/time and updated record count.

    Any ideas that would be simple for a novice to implement?

    Thank you for any help

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    When user selects the file, have vb create a default import file.
    it removes the first line and writes the rest to the import file.
    then it imports the default file.

  3. #3
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Thanks Ranman, but I'm not sure I follow. The user does not actually select the file. The user only sees the form. The form has a button that executes the import (in the specification format) that populates the form's textboxes. Are you suggesting that there is a way to setup the import into a default format that can allow the user to modify the header and trailer on import? How would I go about that?

    Thanks again for your response.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The "flatfile", is it a txt/csv type file??

    Since you have, in effect, 2 different formats in the same file, I would use VBA to read each line and do whatever the requirement is.
    VBA can also write in whatever formats are needed, plus it can create the new required header and trailer.

  5. #5
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    @ssanfu, it's a text file. It looks like this (note: it's not pipe-delimited, it's fixed width, using the pipes for legibility):

    CC3201609082100
    BB|000325.12|MNB| |Collision
    AV|000012.15|SAP|-|Subcell
    EN0000002000000000000.00

    Where "CC..." is the header and "EN..." is the footer of the text file.

    Sorry for being kinda ignorant, but when you say
    use VBA to read each line and do whatever the requirement is
    are you talking about not using the import and export specifications and trading that in for VBA code? If so, is there an example of this that I can refer to?

    Thanks for the response

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I created a text file, removing the pipe-delimiters - see the accompanying text file in the zip file.
    I created a test dB.
    Questions??
    Attached Files Attached Files

  7. #7
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Thanks Steve!

    Got it working, import is good. User can update loaded data in form...but it's not exporting the header and trailer, just the updated lines.... Using this code for export:

    Code:
    Private Sub cmdExportUpdates_Click()
      Dim db As DAO.Database
      Dim qdf As DAO.QueryDef
      Set db = CurrentDb()
      For Each qdf In db.QueryDefs
        If InStr(qdf.Name, "qry_FileUpdate") <> 0 Then
            DoCmd.TransferText transferType:=acExportFixed, SpecificationName:="Qry_FileUpdate Export Specification", TableName:=qdf.Name, Filename:="C:\Report Database\" & qdf.Name & ".txt", hasfieldnames:=False
        End If
      Next qdf
      Set qdf = Nothing
      Set db = Nothing
    MsgBox ("File has been exported.")
    End Sub
    Can anyone jump in and give a suggestion on how to get the header and trailer appended on the exported text file?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you don't need the original header/footer line you have a few options but the easiest is likely to use filesystemobject commands to edit the newly created file.

    For instance

    Code:
    dim sFileName
    dim sFinalFileName
    dim fs
    dim fIn, fOut
    dim sContents
    
    sfilename = "c:\report database\" & qdf.name
    sfinalfilename = replace(sfilename, ".txt", "_Final.txt")
    
    set fs = createobject("scripting.filesystemobject")
    set fOut = fs.createtextfile(sfinalfilename)
    
    set fIn = fs.opentextfile(sfilename, 1)
    scontents = fIn.readall
    set fin = nothing
    
    fout.writeline "THIS IS MY HEADER"
    fout.writeline scontents
    fout.writeline "THIS IS MY FOOTER"
    set fout = nothing
    set fs = nothing

  9. #9
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Hi Rpeare and thanks!

    This is the setup for the header:

    CC420160909121500

    Where the first 3 digits never change but then it's YYYYMMDDHHMMSS

    So if I understand correctly,
    Code:
    fout.writeline "THIS IS MY HEADER"
    is where I would put this. Is there a way to dynamically create the header? It's really just a 3 digit code that never changes plus the current date in the YYYYMMDDHHMMSS format.

    The footer is a count of all records being exported. First 2 digits never change. Like: EN0000002000000000000.00
    Is there a format to do the count of the records before export and then have the footer give that count?

    Thanks again for your reply!

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    fout.writeline CC4 & format(now, yyyymmddnnss)

    and

    fout.writeline EN & format(dcount("*", [SOURCETABLENAME]), "0000000000000000000.00")

    assuming you are importing your original data into a table you can count the records in that table when you do your export and fill that in

  11. #11
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Thanks, rpeare:

    When I try to run the code to export the file, I get the error "variable not defined"

    and it highlights the below code (see in red)

    Code:
    Private Sub cmdExportUpdates_Click()
    Dim sFileName
    Dim sFinalFileName
    Dim fs
    Dim fIn, fOut
    Dim sContents
    Dim qdf As DAO.QueryDef
    Dim FSO As Object
    sFileName = "C:\Report Database\" & qdf.Name
    sFinalFileName = Replace(sFileName, ".txt", "_Final.txt")
    Set fs = CreateObject("scripting.filesystemobject")
    Set fOut = fs.createtextfile(sFinalFileName)
    Set fIn = fs.opentextfile(sFileName, 1)
    sContents = fIn.readall
    Set fIn = Nothing
    fOut.writeline "HDX8" & Format(Now, yyyymmddhhmmss)
    fOut.writeline sContents
    fOut.writeline "TR" & Format(DCount("*", [UpdatedFile]), "0000000000000000000.00")
    Set fOut = Nothing
    Set fs = Nothing
     
    MsgBox ("File has been exported.")
    End Sub
    I tried creating a module with the following

    Code:
    'format date to "yyyymmddhhmmss"
     Public Function formatDateToString(Datetemp As Date) As String
     Dim strMth As String, strDay As String, strHr As String
     Dim strMin As String, strSec As String
     If Len(Month(Datetemp)) = 1 Then
     strMth = "0" & Month(Datetemp)
     Else
     strMth = Month(Datetemp)
     End If
     If Len(Day(Datetemp)) = 1 Then
     strDay = "0" & Day(Datetemp)
     Else
     strDay = Day(Datetemp)
     End If
     If Len(Hour(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
     strHr = "0" & Hour(FormatDateTime(Datetemp, vbLongTime))
     Else
     strHr = Hour(FormatDateTime(Datetemp, vbLongTime))
     End If
     If Len(Minute(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
     strMin = "0" & Minute(FormatDateTime(Datetemp, vbLongTime))
     Else
     strMin = Minute(FormatDateTime(Datetemp, vbLongTime))
     End If
     If Len(Second(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
     strSec = "0" & Second(FormatDateTime(Datetemp, vbLongTime))
     Else
     strSec = Second(FormatDateTime(Datetemp, vbLongTime))
     End If
     Dim strTemp As String
     strTemp = Year(Datetemp) & strMth & strDay & strHr & strMin & strSec
     formatDateToString = strTemp
    But I am not sure if that is even relevant and maybe I am going (way) down a dark path...

    Anyone that can help, I appreciate it

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the abbreviation for minutes is n no m

    you can alternately try to concantenate items individually

    i.e.

    dim dDateStamp as date

    ddatestamp = now

    debug.print format(ddatestamp, "yyyymmdd")
    debug.print format(ddatestamp, "hhnnss")

    see if either or both return values

    if both return values try

    fout.writeline "PREFIX" & format(ddatestamp, "yyyymmdd") & format(ddatestamp, "hhnnss")

    or

    fout.writeline "PREFIX" & format(ddatestamp, "yyyymmdd") & format(datepart("h", ddatestamp), "00") & <minute and second time portions>

  13. #13
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Now getting error "Run-time error 62 Input past end of file"

    highlights this code (in red)

    Code:
    Set fIn = fs.opentextfile(sFileName, 1)
    sContents = fIn.readall
    Set fIn = Nothing
    fOut.writeline "HDX8" & Format(Now(), "YYYYMMDDHHNNSS")
    fOut.writeline sContents
    fOut.writeline "TR" & Format(DCount("*", [UpdatedFile]), "0000000000000000000.00")
    Set fOut = Nothing
    Set fs = Nothing
     
    MsgBox ("File has been exported.")
    End Sub

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    are there any empty lines at the end of the file, or is the file blank?

    that error only occurs (when I have encountered it) when you try to perform a task on an empty line.

    If it's possible for you to have empty lines at the end of your file or have a completely blank file you'll have to test for the empty file first then use a line by line read/write command instead of a bulk read/write like I did.

  15. #15
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Thanks, rpeare. The exported textfile should never be blank. It is supposed to only send updated information only. If there are no updates, no files get sent. Ideally, I need to be able to do what Steve's example database does-only in reverse now. I need the header and the footer to accompany the updates to be exported in textfile format. I am really not that good at VBA coding, but I can hack at example code to get things to work.

    I would be open to a way that the user inputs the header and footer into a form and then that somehow gets kicked out to the textfile export. Just need header and footer with correct format put on the output of the query's textfile.

    Thanks, again. I appreciate everyone's help thus far

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing an updated file from Excel to DB
    By Radtastic10 in forum Access
    Replies: 2
    Last Post: 07-07-2016, 12:45 PM
  2. Replies: 8
    Last Post: 03-12-2016, 02:06 PM
  3. Importing an excel file that must be updated daily
    By apetriella in forum Import/Export Data
    Replies: 9
    Last Post: 02-21-2015, 06:23 AM
  4. importing header and body information from text (csv) file
    By JamesL in forum Import/Export Data
    Replies: 6
    Last Post: 04-10-2012, 09:47 PM
  5. Importing new time for header and update report
    By spamvalley in forum Reports
    Replies: 2
    Last Post: 02-16-2006, 11:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums