importing text file 1 line at a time

[CptTom]

I have dat files that require looking at the first 3 characters of each line to determine the specname to use & the table they will tranfer into. Previous programmer ran transfertext multiple times (each specname & table) creating huge overgrown tables. I have temp added a cleanup function to reduce them to only their required records. There has to be a smarter way.

I want to:
Dim strLine 'As Variant
Dim strType As String
Open "c:\data\MyFiles.dat" For Input As #1

'Read first line into a variable
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strLine ' Read line into variable.
Debug.Print strLine ' Print to the Immediate window.
strType = Left$(strLine, 3)
Debug.Print strType
'Select Case strLine
If strType = "XYZ" Then
Debug.Print "Importing " & strLine
DoCmd.TransferText acImportFixed, "nmcdatafixed", "Imported NMC Data", _
strLine, False


However, Access will not allow the use of a vaiable (strLine) in the transfertext command, it must be a file. Won't that put the entire file into the table?

If I read it into a temp file with 2 fields, the first being 3 characters & the 2d 300 (max) and then import using the mid$ command, would require a lot of coding.

Any sugggestions?


Larry

 

[jebry]

Hi Larry!

One possibility occurs to me. Maybe you can import the data into a 'holding' table. You will need to have designed the appropriate number of append queries which select records based on the three character code and then add them to the proper table. Your code could then import the data, run the queries and erase the table to start fresh the next time.

hth
Jeff Bridgham

 

[CptTom]

Considering the fact that the first 3 characters could be any one of 8 preset possibilities or just garbage, that could still result in a lot of coding. Is there not a way to look at each line as a record and import it accordingly using the specification name?

larry

 

[GeekGirlau]

Have you considered writing only valid records to a second text file, then importing that second text file in one hit?

Dim strLine 'As Variant
Dim strType As String
Dim strImportFile as String


strImportFile="c:\data\ImportFile.dat"

Open "c:\data\MyFiles.dat" For Input As #1
Open strImportFile For Output As #2

Do While Not EOF(1)
Line Input #1, strLine
Debug.Print strLine

strType = Left$(strLine, 3)
Debug.Print strType

Select Case strLine
Case "ABC","123","XYZ"
Debug.Print "Importing " & strLine
Write #2, strType
End Select
Loop

Close #1
Close #2

DoCmd.TransferText acImportFixed, "nmcdatafixed", _
"Imported NMC Data", strImportFile, False

Kill strImportFile

 

[CptTom]

Here is what I have come up with:

Dim strLine As Variant
Dim strType As String
Dim strXMFFile As String
Dim strXMHFile As String
Dim strXMJFile As String
Dim strXMLFile As String

strXMFFile = "c:\data\MyXMFfiles.txt"
strXMHFile = "c:\data\MyXMHfiles.txt"
strXMJFile = "c:\data\MyXMJfiles.txt"
strXMLFile = "c:\data\MyXMLfiles.txt"

Open "c:\data\MyFile.dat" For Input As #1
Open strXMFFile For Output As #2
Open strXMHFile For Output As #3
Open strXMJFile For Output As #4
Open strXMLFile For Output As #5

'Read first line into a variable
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strLine ' Read line into variable.
strType = Left$(strLine, 3)

Select Case strType
Case "XMF"
Debug.Print "Writing " & 2 & " " & strLine
Write #2, strLine
Case "XMH"
Debug.Print "Writing " & 3 & " " & strLine
Write #3, strLine
Case "XMJ"
Debug.Print "Writing " & 4 & " " & strLine
Write #4, strLine
Case "XML"
Debug.Print "Writing " & 5 & " " & strLine
Write #5, strLine
End Select
Loop

Close #1 ' Close file.
Close #2
Close #3
Close #4
Close #5
DoCmd.TransferText acImportFixed, "nmcdatafixed", "Imported NMC Data", _
"c:\Data\MyXMFFiles.txt", False

DoCmd.TransferText acImportFixed, "usage", "Imported Usage Data", _
"C:\Data\MyXMHFiles.txt", False

DoCmd.TransferText acImportFixed, "Imported XMJ Data", "Imported XMJ Data", _
"C:\Data\MyXMJFiles.txt", False

DoCmd.TransferText acImportFixed, "Imported XML Data", "Imported XML Data", _
"C:\Data\MyXMLFiles.txt", False

MsgBox "Files Imported"
End Sub


THIS then rewrites them to separate txt files based on the set criterion. The QUESTION then is, why does it add quote marks at the beginning and end of each line?

Larry