Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a file and saving in Native application (Access 2003)

    (Edited by HansV to provide link to post. It is very easy - see <!help=19>Help 19<!/help>)

    Hello,

    Currently I have an import sub that takes a .txt or .csv files and loads the data into Access tables one at a time. 95% of the time, the .csc files come from our PDM system (via manual or scheduled export). When running the import sub against these files without opening them first in Wordpad or Notebook, the progress if very slow and sometimes crashes my import sub. However, if opening them up and saving them in Wordpad (no alteration to the data) then running the import sub, the progress is MUCH faster. If anyone knows why this happens, it would be nice to know, but it's not a big deal.

    Just recently, I've needed to load more external data into my Access database and have started to schedule data exports from our PDM system. What I would like to do, is have my import sub called for each given scheduled .csv file in a certain network location, however the problem that I'm having is that I have to open the .csv file in Wordpad and save it before I can use the import sub. I've read <post#=457311>post 457311</post#>, and it gives me a few pointer like using the Shell function, but I still don't know how to setup VB code to open the file in Wordpad, save it, and then close Wordpad. Any help with this is greatly appreciated!

    Regards,
    Drew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    Wordpad is much more primitive than Word, Access etc. It doesn't have a programming interface, so you cannot use VBA to open, save and close a file in Wordpad.

    Without seeing one of the files, it is impossible to say what causes the problem.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    Hans,

    Does Notepad fall into the same category as Wordpad?
    Attached is a zip file which includes a raw export from our PDM system.

    Thanks,
    Drew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    Notepad is even more primitive than Wordpad.

    The file contains line feeds (ASCII 10) as line delimiters; Windows applications expect carriage returns (ASCII 13) or carriage return/line feeds (ASCII 13 + 10). You can use the following code to convert:

    Sub ConvertFile(strFileIn As String, strFileOut As String)
    Dim f1 As Integer
    Dim f2 As Integer
    Dim s As String * 1
    f1 = FreeFile
    Open strFileIn For Binary Access Read As #f1
    f2 = FreeFile
    Open strFileOut For Binary Access Write As #f2
    Do While Not EOF(f1)
    Get #f1, , s
    If s = vbLf Then
    Put #f2, , vbCr
    End If
    Put #f2, , s
    Loop
    Close #f2
    Close #f1
    End Sub

    Call it like this

    Sub TestConvert()
    ConvertFile "C:TestPDM Export.csv", "C:TestPDM Export2.csv"
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    Hans,

    The file I used for a test was from our manufacture table, which included approximately 2370 records. I let the code run for around 3 minutes before I stopped it. The new file was created, however only 280 records were added. Is this reasonable to expect? If so, I don't believe that this will be feasable due to some of the files having ~200,000 table records.

    Another person here had tried to open a raw file in MS Word and then saving it. Everything seemed to work the same as when we save the raw file in Wordpad. I'll have to do a few more test, but this seem like this may work(?).

    Thanks,
    Drew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    By far the best solution would be to get the PDM system to export the data in a usable format.

    280 records in 3 minutes is extremely slow. I tested on a file (constructed from your sample) with 1540 lines; processing took 0.35 seconds. Have you tried it with files on a network share or on a local hard disk (I did the latter).

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    Unfortunately, we are a small division of a large corporation. Requesting a fix is painful and takes a long time to complete, sometimes over a year. However, now that you have pointed out one of the problems with the PDM export (vblf), I will submit a formal request to have it fixed. In addition, from what I've read, they (the creators of our PDM system) did not setup the csv format correctly. They don't include double-quotes around all data in the text file; only strings that include a comma. I've setup my import sub to work around this, however I'm thinking it needs to be included in the same fix.

    I've created a new database located on C: and pasted the code you wrote into a module. I've also saved 2 PDM export files to C:, one with the manufacture table (~2,370 table records; 8 fields/rec) and one for our document ID table (~90,000 table records; 11 fields/rec). The following is the time it took to complete each.

    Document ID = 11 minutes, 50 seconds
    Manufactures = 32 seconds

    This is still too long. Any idea why it's running so slow?
    Computer attributes:
    Pentium 4, 2.40GHz
    2 GB Ram
    MS Window XP (SP2)

    On a side note, I noticed that you were able to run this in 0.35 seconds. Currently I measure the time it takes code to run by using the now() function, subtracting the difference between the StartTime and EndTime, but only pulls out seconds. Is their a way I can measure more accurately?

    Thank you for you help!
    Drew

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening a file and saving in Native application (Access 2003)

    I was testing on a machine with

    Pentium 4 2.5 MHz
    512 MB Ram
    Windows XP SP-2
    Access 2002 SP-3

    I can't understand why the code runs so slow - Get and Put are quite fast.

    I used the Timer function to measure execution time. Timer returns a Single precision number representing the number of seconds since midnight, with hundredths of seconds (accuracy is about 1/60th of a second)

    Dim t As Single
    t = Timer
    Call ...
    t = Timer - t
    Debug.Print t

Posting Permissions

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