After the Digitech Datagroup file is converted to .csv in Access the following code may be used to convert to a Laserfiche Import List (.lst) file.

    1 Imports Microsoft.VisualBasic.FileIO
    2 Imports System.IO.StreamReader
    3 Imports System.Text.Encoding
    4 Imports System.IO
    5 Imports System.Data.OleDb
    6 Imports System.Data.SqlClient
    7 Imports sConverter1.FormsConverter
    8 Module Convert
    9     Dim cnOleDb As OleDbConnection
   10     Dim PathDat As String
   11     Dim PathImg As String
   12     Dim PathLst As String
   13     Dim Dat As String
   14     Dim Lst As String
   15     Dim LstFile As File
   16     Dim filename As String
   17     Dim lstSavePath As String
   18     Dim dumpfile As String
   19     Dim currentRow As String()
   20     Public Function LdgZero(ByVal x)
   21         If x < 10 Then
   22             x = "00000" & x
   23         ElseIf x >= 10 And x < 100 Then
   24             x = "0000" & x
   25         ElseIf x >= 100 And x < 1000 Then
   26             x = "000" & x
   27         ElseIf x >= 1000 And x < 10000 Then
   28             x = "00" & x
   29         ElseIf x >= 10000 And x < 100000 Then
   30             x = "0" & x
   31         Else : MsgBox("Does not compute.")
   32         End If
   33         Return x
   34     End Function
   35     Public Sub OpenCSV()
   36         FormsConverter.OpenFileDialogDat.ShowDialog()
   37         If FormsConverter.OpenFileDialogDat.FileName <> "" Then
   38             PathDat = FormsConverter.OpenFileDialogDat.FileName
   39             Dat = My.Computer.FileSystem.ReadAllText(PathDat)
   40             FormsConverter.TextBoxQC.Text = Dat
   41             FormsConverter.TextBoxDat.Text = PathDat
   42             ' cnOleDb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & PathDat & "'"
   43         End If
   44     End Sub
   45     Public Sub ConvertCSV()
   46         ' Create an instance of StreamWriter to write text to the dump file.
   47         dumpfile = "C:\dumpfile.lst"
   48         Dim sw As StreamWriter = New StreamWriter(dumpfile, False, ASCII)
   49         ' Create an instance of the TextFieldParser called DatReader
   50         Using DatReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(PathDat)
   51             ' Set the field type to delimited
   52             DatReader.TextFieldType = FileIO.FieldType.Delimited
   53             ' Set the delimiter to comma
   54             DatReader.SetDelimiters(",")
   55             ' Create an array of strings called currentRow
   56             ' The array starts at currentFields(0)
   57             ' Start writng to the dumpfile
   58             sw.WriteLine("LASERFICHE IMPORT LIST")
   59             ' Execute this loop until the end of the file.
   60             While Not DatReader.EndOfData
   61                 Try
   62                     'ReadFields reads all fields on the current line, 
   63                     'returns them as an array of strings,
   64                     'and advances the cursor to the next line 
   65                     'containing data
   66                     currentRow = DatReader.ReadFields()
   67                     sw.WriteLine()
   68                     sw.WriteLine("STARTFIELDS")
   69                     ' The fist line after the STARTFIELDS directive
   70                     ' indicates the template name. The name is provided by the user.
   71                     sw.WriteLine(FormsConverter.TextBoxTemplate.Text)
   72                     ' This is customizable for the csv format
   73                     ' and represents the field values for the template.
   74                     ' For i As Integer = 1 To 2
   75                     sw.WriteLine(currentRow(1))
   76                     ' i = i + 1
   77                     sw.WriteLine(currentRow(2))
   78                     ' Next
   79
   80                     sw.WriteLine(LdgZero(currentRow(6)))
   81                     sw.WriteLine("ENDFIELDS")
   82                     ' The document name is provided by the data file in field 6
   83                     sw.WriteLine("DOCUMENT(" & currentRow(1) & " - " & currentRow(2) & ")")
   84                     sw.WriteLine()
   85                     ' STARTLIST indicates the begining of the image file locations
   86                     sw.WriteLine("STARTLIST")
   87                     ' This loop parses data field 5 and separates the digits by 8, forms the file path and name and etc.
   88                     Do While Len(currentRow(5)) > 0
   89                         Dim PathFlr As String = Format(Int(Val(Mid(currentRow(5), 1, 8)) / 1000), "00000")
   90                         sw.WriteLine(PathImg & "\" & PathFlr & "\" & Mid(currentRow(5), 1, 8) & ".tif")
   91                         currentRow(5) = Mid(currentRow(5), 9)
   92                     Loop
   93                     sw.WriteLine("ENDLIST")
   94                 Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
   95                     MsgBox("Line " & ex.Message & _
   96                     "is not valid and will be skipped.")
   97                 End Try
   98             End While
   99             sw.Close()
  100         End Using
  101         Lst = My.Computer.FileSystem.ReadAllText(dumpfile)
  102         FormsConverter.TextBoxQC.Text = Lst
  103         FormsConverter.ButtonSavelst.Enabled = True
  104         FormsConverter.ProgressBar1.Value = 100
  105     End Sub
  106     Public Sub SaveLst()
  107         FormsConverter.SaveFileDialoglst.ShowDialog()
  108         If FormsConverter.SaveFileDialoglst.FileName <> "" Then
  109             PathLst = FormsConverter.SaveFileDialoglst.FileName()
  110             Dim sw As StreamWriter = New StreamWriter(PathLst, False, ASCII)
  111             sw.Write(Lst)
  112         End If
  113     End Sub
  114     Public Sub ImagesLoc()
  115         FormsConverter.FolderBrowserDialog1.ShowDialog()
  116         PathImg = FormsConverter.FolderBrowserDialog1.SelectedPath
  117         FormsConverter.TextBoxImageFolder.Text = PathImg
  118     End Sub
  119 End Module
Advertisements