How can I skip blank lines when importing a TXT file?

[JeremyI]

Today I've been trying out a personal project I have been interested in for a while. MS Access 2000.

I get a daily list of currency exchange rates via email, but it's a bit of a pain to go through them and extract the few I need, and I don't keep up on it very well! However, I did determine that the actual message files are nice and readable as plain text (I still use Eudora 7). So I thought it might be possible to write a bit of VBA code to do it for me.

I found a Bytes thread that gave the essential principles of the import process:
How to get external data into a table/form

Unfortunately, I have discovered that Access gives up and walks away when it hits a carriage return-linefeed combination. Not only does it stop importing; it doesn't execute any of the rest of the code, so it is not even closing my file or my recordset.

Any way I can tell it to just plow ahead? A couple of my unsuccessful ideas are in the code excerpt below.

    Open strFile For Input As #1 'Open file for input.
    Do While Not blnTheEnd 'Loop until end of file.
        Line Input #1, strLine 'Reads the row within the file.
        'If strLine = (Chr(13) + Chr(10)) Then
        '    strLine = ""
        'End If
        If strLine = "!-!-!REALLY THE END OF THE FILE!-!-!" Then
            blnTheEnd = True
        Else
            rs.AddNew
            rs(0) = strLine
            rs.Update 'Writes data into the table.
        End If
    Loop
    Close #1 'Close file.
    
    rs.Close            'Close what you opened.

If all goes well, I might spend less time on this programming project than it would take to manually extract a few hundred values. ;-)

[ADezii]

Here is basic code that will Input a *.TXT File Line-by-Line, skip the Blank Lines, and assign each Line to a Variable (strLine) for further processing. Be Advised that Line Input# will strip Carriage Return/Line Feed combinations (CR/LF) when it encounters them, and will not append them to the result.

Const conPATH_TO_TEXT_FILE As String = "C:\Test\Test.txt"

Open conPATH_TO_TEXT_FILE For Input As #1       'Open File for Input.

DoCmd.Hourglass True

Do While Not EOF(1)
  Line Input #1, strLine                        'Reads the Row within the file.
    If Len(strLine) <> 0 Then
      'Append to Open Recordset here
    End If
Loop

DoCmd.Hourglass False

Close #1

[JeremyI]

That did it, ADezii! Thanks.

It was the test for Len(strLine) <> 0 that mattered, of course. For whatever reason, I just couldn't find this information online no matter how I searched.

And thanks for the hourglass--would have been very alarming otherwise. The table has 255,736 records.

Now if I can perform the analysis it will be a miracle...

[ADezii]

If you need a hand with the Analysis, we're here...

[JeremyI]

Oh, I don't doubt it! ;-) But I'll give it a fair shot first.

[JeremyI]

Well, I've slogged through it bit by bit this week and finally come up with a combination of queries that will work. Just as an intellectual SQL exercise, I was wondering if it's possible to structure them as one query?

The simplified table I played around with looks like this:

ItemID        AutoNumber, Primary Key
ItemFKey      Long Integer (this represents the sequential line number I generated with code)
ItemDate      Date/Time
ItemType      Text(10)
ItemValue     Double

Each line from the email contains a separate chunk of the data (the date, OR American dollars rates, OR Euro rates, etc), so my goal was to associate each different rate with the correct date. All lines are strictly in sequential order (date ascending) as they were stored in the email file.

It took me four queries:

qry11SelectDates
----------------
SELECT tblItem.ItemID, tblItem.ItemFKey, tblItem.ItemDate
FROM tblItem
WHERE (((tblItem.ItemDate) Is Not Null));

qry12DatesLessThanItems
-----------------------
SELECT tblItem.ItemID AS NonDateID, qry11SelectDates.ItemID AS DateID, qry11SelectDates.ItemFKey AS DateFKey, tblItem.ItemFKey
FROM tblItem, qry11SelectDates
GROUP BY tblItem.ItemID, tblItem.ItemDate, qry11SelectDates.ItemID, qry11SelectDates.ItemFKey, tblItem.ItemFKey
HAVING (((tblItem.ItemDate) Is Null) AND ((qry11SelectDates.ItemFKey)<[tblItem].[ItemFKey]))
ORDER BY tblItem.ItemID, tblItem.ItemDate;

qry13MatchItemsDates
--------------------
SELECT qry12DatesLessThanItems.ItemFKey, Max(qry12DatesLessThanItems.DateFKey) AS MaxOfDateFKey
FROM qry12DatesLessThanItems
GROUP BY qry12DatesLessThanItems.ItemFKey
ORDER BY qry12DatesLessThanItems.ItemFKey, Max(qry12DatesLessThanItems.DateFKey);

qry14TabularItemsOnDates
------------------------
SELECT tblItem_1.ItemDate, tblItem.ItemType, tblItem.ItemValue
FROM (qry13MatchItemsDates INNER JOIN tblItem AS tblItem_1 ON qry13MatchItemsDates.MaxOfDateFKey = tblItem_1.ItemFKey) INNER JOIN tblItem ON qry13MatchItemsDates.ItemFKey = tblItem.ItemFKey
ORDER BY tblItem_1.ItemDate, tblItem.ItemType;

There are some duplicate date lines that I will have eliminated (via other queries), so the assumption is one date line per block of data.

[ADezii]

To be honest with you, I would actually need the DB in front of me and work with it, before I would venture any kind of opinion.

[JeremyI]

Fair enough! I'm attaching my little sample version of the problem, to spare you the 100Mb compilation of all those emails (which probably wouldn't even be legal).