Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I hope this doesn't sound idiotic. I am receiving a fixed width file with a header record, multiple detail records and a trailer record.

    The header record is formatted for 5 fields of fixed width. The detail record is formatted for 15 fields, fixed width, but different width's than the header. The trailer record contains 20 fields, again of different widths than either the header or detail record.

    Right now I have created three import specs, import the file three times into three different tables, then delete all but the header record from one table, all but the detail records in the second table, and all but the trailer record. My question is, "Is this the most effect method?".

    Thanks in advance for your ideas and contributions.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    An alternative would be to use VBA to open the text file, read it line by line and add the data to the appropriate tables. But this will probably be slower than the method you use now.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796856' date='07-Oct-2009 16:57']An alternative would be to use VBA to open the text file, read it line by line and add the data to the appropriate tables. But this will probably be slower than the method you use now.[/quote]
    Thanks Hans. Just for kicks and giggles, how do you open a text file via VBA?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Post 795434 for an example.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796860' date='07-Oct-2009 17:31']See Post 795434 for an example.[/quote]
    That looks interesting. I will play with it.

    THANKS!

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796860' date='07-Oct-2009 17:31']See Post 795434 for an example.[/quote]
    Hans,

    THANKS! I like this much better. A cleaner approach, and more control.

    As a followup, is it possible to have the user navigate to the file, instead of hardcoding it? For example the incoming file will not always have the same file name, unless I force the user rename the file once they get it.

    As always, thank you sharing your brilliance.

    Ken

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use Application.FileDialog to let the user select the file. See Post 735585 for example code.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796902' date='08-Oct-2009 04:55']You can use Application.FileDialog to let the user select the file. See Post 735585 for example code.[/quote]
    Hans,

    That worked GREAT! However, I had to use the follwing within the module and not as a sub.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim r As Long
    Dim strFile As String
    Dim f As Integer
    Dim strLine As String
    Dim lngPos1 As Long
    Dim lngPos2 As Long

    On Error GoTo ErrHandler
    Set db = CurrentDb

    With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "C:\AIR\*.*"
    If .Show Then
    strFile = .SelectedItems(1)
    Else
    MsgBox "No file selected", vbInformation
    End If
    End With


    f = FreeFile
    Open strFile For Input As #f
    . . .




    If I want to call the sub and pass the .SelectedItems(1) back to the calling module, what is the syntax? In other words instead of using the above as a part of the onclick event, I want to do something like:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim r As Long
    Dim strFile As String
    Dim f As Integer
    Dim strLine As String
    Dim lngPos1 As Long
    Dim lngPos2 As Long

    On Error GoTo ErrHandler
    Set db = CurrentDb


    strFile=ShowFiles()


    f = FreeFile
    Open strFile For Input As #f
    . . .


    Does that make any sense? I am probably not using the correct terms.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a function:

    Code:
    Function ShowFiles() As String
    	With Application.FileDialog(msoFileDialogFilePicker)
    		.InitialFileName = "C:\AIR\*.*"
    		If .Show Then
    			ShowFiles = .SelectedItems(1)
    		Else
    			MsgBox "No file selected", vbInformation
    		End If
    	End With
    End Function

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797049' date='08-Oct-2009 19:01']You could create a function:

    Code:
    Function ShowFiles() As String
    	With Application.FileDialog(msoFileDialogFilePicker)
    		.InitialFileName = "C:\AIR\*.*"
    		If .Show Then
    			ShowFiles = .SelectedItems(1)
    		Else
    			MsgBox "No file selected", vbInformation
    		End If
    	End With
    End Function
    [/quote]
    Ah! so setting the function ShowFiles as a string, that allows passing values back to calling code. Awesome.

    Hans, thank you not only for your help, but for your patience.

    Ken

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The essential line is

    ShowFiles = .SelectedItems(1)

    Because ShowFiles is the name of the function, this tells VBA that the function should return the value of .SelectedItems(1). It wouldn't have worked if the line had been

    SomethingElse = .SelectedItems(1)

  12. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797051' date='08-Oct-2009 19:22']The essential line is

    ShowFiles = .SelectedItems(1)

    Because ShowFiles is the name of the function, this tells VBA that the function should return the value of .SelectedItems(1). It wouldn't have worked if the line had been

    SomethingElse = .SelectedItems(1)[/quote]
    that makes sense.

    Thanks, Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •