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
