Importing External Data

[John W]

I have a question regarding importing external data into a table. The data
is in a delimited text file which normally would seem easy. The problem I
have is that each group of data I need is on two lines - the first line
contains one set of information and the second line contains the rest. When
importing the data is there any way to specify that the two lines go
together as one group?

Thanks for your help!

 

[Dirk Goldgar]

In

I have a question regarding importing external data into a table. The
data is in a delimited text file which normally would seem easy. The
problem I have is that each group of data I need is on two lines
- the first line contains one set of information and the second line
contains the rest. When importing the data is there any way to
specify that the two lines go together as one group?

Thanks for your help!

There's no built-in mechanism for doing that. Is there anything that
ties the two lines together aside from their position in the file? If
so, you can probably link the file (rather than import it), then make
two queries of it, one returning the "line 1" data and one returning the
"line 2" data, and then make a third query that joins the first two
queries on the linking field and inserts the resulting records into a
table.

If there's nothing but position to link the lines together, then it's
probably easiest to read the file directly, using basic I/O statements
(OPEN, LINE INPUT, CLOSE), parse each line, and append each pair of
lines to your table using a recordset or an append query constructed on
the fly.

 

[John W]

The only thing that might make a difference is that on the first line the
beginning test is always DST and on the second line the beginning text is
always CST. Would this help any?

Thanks again!

 

[Dirk Goldgar]

In

The only thing that might make a difference is that on the first line
the beginning test is always DST and on the second line the beginning
text is always CST. Would this help any?

No, I don't see how. You'd need some key value that appears in both
lines of a pair, and only those two lines.

Hmm, an alternative to the other ideas I mentioned is to prepare the
file for importing by doing a global replace in the file (via some text
editor, or using Microsoft Word, or even using code within Access) of
the line-end characters followed by "CST", replacing them with a comma,
thus joining each pair of lines into one line. If this is a one-time
import, that may be easiest solution.

Or you could use VBA code in Access to join each pair of lines in the
file, using code like this:

'----- start of "air code" -----
Dim iFileIn As Integer
Dim iFileOut As Integer
Dim strLineIn As String
Dim strLineOut As String

iFileIn = FreeFile()
Open "C:\YourPath\YourFile.txt" For Input As #iFileIn
iFileOut = FreeFile()
Open "C:\YourPath\YourFile_Out.txt" For Output As #iFileOut

Do Until EOF(iFileIn)
Line Input #iFileIn, strLineOut
Line Input #iFileIn, strLineIn
strLineOut = strLineOut & "," & strLineIn
Print #iFileOut, strLineOut
Loop

Close iFileIn, iFileOut
'----- end of "air code" -----

Then, of course, you'd import the output file, not the input file.,