Capture Text File Import Wizard (Access/Excel Office 97)

[sdh]

Has anyone successfully been able to invoke this wizard within one of there applications if so can you please shed
some light on how to do it.

I am importing text files from other apps and the structures and delimiters keep changing I need someway of handling that.

thanks

 

[harmmeijer]

I made the following function, the endlinetag is the tag used to indeicate the end of the line (vbcrlf most of the time) fieldsep is the carakter(s) wich seperate the fields pad is the location and name of the textfile to be inported (like "c:\inp.txt&quot therecordset is the recordset where the information of the textfile needs to go.

Function trtext(ByVal endlinetag As String, ByVal fieldsep As String, ByVal pad As String, therecordset As Recordset)
' be sure that the lines in the textfile (rows in the recordset
' are no longer than 10000 carakters else you have to make the
' harm var longer
Dim harm As String * 10000
Dim fieldscountr As Integer
Dim beginline As Integer
Dim endline As Integer
Dim begfield As Integer
Dim endfield As Integer
Dim fieldseplength As Integer
Dim endlinetaglength As Integer
Dim line As String
Dim fieldje As String
endlinetaglength = Len(endlinetag)
fieldseplength = Len(fieldsep)
beginline = 1
endline = 3
Open pad For Binary Access Read Lock Read As #1
Do While endline > 0
Get #1, beginline, harm
endline = InStr(1, harm, endlinetag, vbTextCompare)
beginline = beginline + endline + (endlinetaglength - 1)
If endline > 1 Then
begfield = 1
endfield = 1
line = Mid(harm, 1, endline - 1) & fieldsep
' We got the row now make a new record in the recordset
therecordset.AddNew
fieldscountr = 1
Do While endfield > 0
endfield = InStr(begfield, line, fieldsep, vbTextCompare)
If endfield > 1 Then
fieldje = Mid(line, begfield, endfield - begfield)
' fieldje is filled with the field
' the first field is skipped because in the recordset
' this is probebly an outonumber
' the amount of fields in the textfile and recordset
' must be:
' fields in recordset - 1 = fields in the textfile
therecordset.Fields(fieldscountr) = fieldje
Else
' if the field in the textfile is empty nothing is
' filled in in the recordset, this could couse an error
' you could try to fill the field with nothing like:
' therecordset.fields(fieldscountr) = ""
End If
begfield = begfield + Len(fieldje) + fieldseplength
fieldscountr = fieldscountr + 1
Loop
therecordset.Update
End If
Loop
Close #1
MsgBox ("einde functie &quot
End Function

 

[sdh]

I have a similar function

My problem is my data is delimited by spaces anywhere inbetween one and 10 spaces depending on the lenght of the first string

ie

241Y AU WEANED
242Y AU WEANED
243Y AU SERVED
245Y AU SERVED
247Y AU SERVED
249245D AU SERVED
250Y AU SERVED
2525 AU SERVED
254Y AU SERVED
255K4 AU SERVED
2555Y AU SERVED
256K8 AU ENTERED
257Y AU SERVED
258Y AU SERVED
259Y AU SERVED
A234262Y AU SERVED
263Y AU SERVED
264Y AU SERVED

see my problem that is why i thought the import wizard may be an idea.

 

[harmmeijer]

Are you sure this textfile can be inported with the wizzard??
Because it is not fixed length and the amount of seperators (if space is used) are variable.
The best way to go about importing this file (I think) is to read one line into a variable, keep replacing every two spaces in the string with one space untill there are no two spaces left in the variable and use the one space as a seperator.
Read the next line etc. etc..

 

[sdh]

thanks

what code would you use for identifying the two spaces ?





What I have achieved is reading a line at a time and using the mid function to split it so some of my fields contain spaces as will as characters but the doesn't seem to bother the sql query I run from this data surprisingly!!

d = Finput.ReadLine
Debug.Print (d)
If str(Len(d)) = 0 Then
GoTo 10
End If
linearray(0) = Mid(d, 1, 12)
linearray(1) = Mid(d, 15, 2)
linearray(2) = Mid(d, 20, 30)

seems to get info I need with extra spaces(unfortunately).
thanks
again

 

[harmmeijer]

I tried it with the file shown above, you should give the function the option pass a path and filename for the textfile to inport (now its c:\test.txt) and pass a recordset to it because at this moment the fields are just printed with a debug.print (in the other function the recordset is called therecordset).

Private Sub Command1_Click()
intfilenumber = FreeFile
Open "c:\test.txt" For Input As intfilenumber
Do While Not EOF(intfilenumber)
Line Input #intfilenumber, strRow
' Replace all double spaces with a single space
tempvar = strRow
While Not InStr(tempvar, &quot; &quot - 1 < 0
starttoken = InStr(tempvar, &quot; &quot - 1
endtoken = starttoken + Len(&quot; &quot + 1
front = Left(tempvar, starttoken)
back = Mid(tempvar, endtoken)
tempvar = front & &quot; &quot; & back
Wend
' now the tempvar contains the line where fields are seperated with single space
strRow = tempvar
endfield = 1
begfield = 1
fieldscountr = 1 ' again assuming the first field is the id (outonumber)
Do While endfield > 0
endfield = InStr(begfield, strRow, &quot; &quot;, vbTextCompare)
If endfield > 1 Then
fieldje = Mid(strRow, begfield, endfield - begfield)
' fieldje is filled with the field
' the first field is skipped because in the recordset
' this is probebly an outonumber
' therecordset.Fields(fieldscountr) = fieldje
Debug.Print fieldje
Else
' best to do nothing
End If
begfield = begfield + Len(fieldje) + Len(&quot; &quot
fieldscountr = fieldscountr + 1
Loop
Loop
Close intfilenumber
End Sub