Importing txt delimitted file

I have my application i created for a client. Now my client needs the invoices created in my app to be uploaded into there accounting package called Pastel ver 5. Now this all cool and i can do the above no problem. The problem i have is that pastel does not warn the user if they import the same delimitted file that an invoice number already exists. Pastel justs autoincrements to the next invoice number. So i need ideas on a way around this. The client will import the file from inside Pastel. It has an import feature which the user will select the specific delimitted file and press import. I hope this makes sense. Anyway i was thinking is there anyway i can delete the contents of the delimitted file once a process or thread has opened the file for read. So if the user goes in the next day and tries to import the same file it will not allow them to.

thanx in advance


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

' #VBIDEUtils#************************************************************
' * Programmer Name  : Waty Thierry
' * Web Site         :
' * E-Mail           :
' * Date             : 15/09/1999
' * Time             : 11:55
' **********************************************************************
' * Comments         : Import text data into a database
' *
' *
' **********************************************************************
Option Explicit

Private Sub cmdImport_Click()
   Dim wks As Workspace
   Dim db As Database
   Dim fnum As Integer
   Dim text_line As String
   Dim sql_statement As String
   Dim field_names() As String
   Dim field_start() As Integer
   Dim max_field As Integer
   Dim i As Integer
   Dim field_value As String
   Dim pos1 As Integer
   Dim pos2 As Integer
   Dim line_length As Integer

   ' Get the field information.
   ReDim Preserve field_names(0 To txtField.UBound)
   ReDim Preserve field_start(0 To txtField.UBound + 1)
   For i = 0 To txtField.UBound
      field_names(i) = Trim$(txtField(i).Text)
      If Len(field_names(i)) = 0 Then Exit For
      field_start(i) = CInt(txtStart(i).Text)
   Next i
   max_field = i - 1
   field_start(i) = 10000

   ' Open the text file.
   fnum = FreeFile
   On Error GoTo NoTextFile
   Open txtTextFile.Text For Input As fnum

   ' Open the database.
   On Error GoTo NoDatabase
   Set wks = DBEngine.Workspaces(0)
   Set db = wks.OpenDatabase(txtDatabaseFile.Text)
   On Error GoTo 0

   ' Read the file and create records.
   Do While Not EOF(fnum)
      ' Read a text line.
      Line Input #fnum, text_line
      text_line = Trim$(text_line)
      line_length = Len(text_line)
      If Len(text_line) > 0 Then
         ' Build the field list.
         sql_statement = "INSERT INTO " & _
            txttable.Text & " ("
         For i = 0 To max_field
            sql_statement = sql_statement & _
            If i < max_field Then _
               sql_statement = _
               sql_statement & ", "
         Next i
         sql_statement = sql_statement & _
            ") VALUES ("

         ' Add the field values.
         For i = 0 To max_field
            pos1 = field_start(i)
            If pos1 > line_length Then
               field_value = ""
               pos2 = field_start(i + 1) - 1
               If pos2 > line_length Then _
                  pos2 = line_length
               field_value = Mid$(text_line, pos1, pos2 - pos1 + 1)
            End If
            sql_statement = sql_statement & _
               """" & field_value & """"
            If i < max_field Then _
               sql_statement = _
               sql_statement & ", "
         Next i
         sql_statement = sql_statement & ")"

         ' Insert the record.
         On Error GoTo SQLError
         db.Execute sql_statement
         On Error GoTo 0
      End If

   ' Close the file and database.
   Close fnum
   MsgBox "Ok"
   Exit Sub

   MsgBox "Error opening text file."
   Exit Sub

   MsgBox "Error opening database."
   Close fnum
   Exit Sub
   MsgBox "Error executing SQL statement '" & _
      sql_statement & "'"
   Close fnum
   Exit Sub
End Sub

Private Sub Form_Load()
   ' Enter default file and database names.
   txtTextFile.Text = App.Path & "\testdata.txt"
   txtDatabaseFile.Text = App.Path & "\testdata.mdb"
End Sub
Does the Accounting Package - Pastel V5 a GUI application or does it use Emulation Software (Attachmate or Client Access)?
CraigLazarAuthor Commented:
Hi tommy
it has a gui application.


The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

This is alittle "old-style" but how about this:

Launch Pastel in your program, using sendkeys to send the keystrokes to import the file, then deleting the file and terminating Pastel.

That way, you have control over what gets imported...

CraigLazarAuthor Commented:
Hi mcrider
thanx for the imput but i do not think pastel supports com. I have been in my references and there are no files belonging to pastel. My client is very happy with goinf file import, select the file and press process. The only problem is how do i effectively mark that file now that it has been imported ?


Well I would try the SendKeys approach using a quick VB program, however, you can also do this...

In your program, popup a window telling the user that you will be starting Pastel, and give them the name of the file they should be importing.

Have your program launch Pastel like this:

   ExecCmd("C:\PASTEL.EXE") 'Put the path where pastel is...

You program will launch pastel and then go to sleep until they exit pastel.

When your program comes back, popup another message box that says something like "Did you import the file?  Yes or No".

If they say yes, you have the name of the file and you can delete it or mark it as imported or whatever you want to do...

To use the ExeCmd function, put the following code in a MODULE in your program:

Public Const INFINITE = -1&

Private Declare Function OpenProcess Lib "kernel32" (ByVal _
    dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
    ByVal dwProcessID As Long) As Long

   Private Type STARTUPINFO
      cb As Long
      lpReserved As String
      lpDesktop As String
      lpTitle As String
      dwX As Long
      dwY As Long
      dwXSize As Long
      dwYSize As Long
      dwXCountChars As Long
      dwYCountChars As Long
      dwFillAttribute As Long
      dwFlags As Long
      wShowWindow As Integer
      cbReserved2 As Integer
      lpReserved2 As Long
      hStdInput As Long
      hStdOutput As Long
      hStdError As Long
   End Type

      hProcess As Long
      hThread As Long
      dwProcessID As Long
      dwThreadID As Long
   End Type

   Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
      hHandle As Long, ByVal dwMilliseconds As Long) As Long

   Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
      lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
      lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
      ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
      ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
      lpStartupInfo As STARTUPINFO, lpProcessInformation As _

   Private Declare Function CloseHandle Lib "kernel32" _
      (ByVal hObject As Long) As Long

   Private Declare Function GetExitCodeProcess Lib "kernel32" _
      (ByVal hProcess As Long, lpExitCode As Long) As Long

   Private Const NORMAL_PRIORITY_CLASS = &H20&
  Public Function ExecCmd(cmdline$)
      Dim start As STARTUPINFO

      ' Initialize the STARTUPINFO structure:
      start.cb = Len(start)

      ' Start the shelled application:
      ret& = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
         NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

      ' Wait for the shelled application to finish:
         ret& = WaitForSingleObject(proc.hProcess, INFINITE)
         Call GetExitCodeProcess(proc.hProcess, ret&)
         Call CloseHandle(proc.hThread)
         Call CloseHandle(proc.hProcess)
         ExecCmd = ret&
    End Function

CraigLazarAuthor Commented:
Hi mcrider
thanx allot this is great and i like the idea. I have increased the points to 80. If u do not mind i am waiting for my client to accept the quote. If they do i moght need to ask u a couple of questions on the concept and code you have posted here (if u do not mind)

I will find out if i must go ahead in the 2nd week of january but i am going to grade the question now. Thanx allot mate
have a good new year and xmas


CraigLazarAuthor Commented:
Hi mcrider
sorry please post the answer
i do not know what happened for some reason it rejected your answer but i accepted it.

Please answer again so i can give u a A grading

Posting for points...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thanks for the points! Glad I could help!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.