469,306 Members | 2,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

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

49
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.

Expand|Select|Wrap|Line Numbers
  1.     Open strFile For Input As #1 'Open file for input.
  2.     Do While Not blnTheEnd 'Loop until end of file.
  3.         Line Input #1, strLine 'Reads the row within the file.
  4.         'If strLine = (Chr(13) + Chr(10)) Then
  5.         '    strLine = ""
  6.         'End If
  7.         If strLine = "!-!-!REALLY THE END OF THE FILE!-!-!" Then
  8.             blnTheEnd = True
  9.         Else
  10.             rs.AddNew
  11.             rs(0) = strLine
  12.             rs.Update 'Writes data into the table.
  13.         End If
  14.     Loop
  15.     Close #1 'Close file.
  16.  
  17.     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. ;-)
Oct 24 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Const conPATH_TO_TEXT_FILE As String = "C:\Test\Test.txt"
  2.  
  3. Open conPATH_TO_TEXT_FILE For Input As #1       'Open File for Input.
  4.  
  5. DoCmd.Hourglass True
  6.  
  7. Do While Not EOF(1)
  8.   Line Input #1, strLine                        'Reads the Row within the file.
  9.     If Len(strLine) <> 0 Then
  10.       'Append to Open Recordset here
  11.     End If
  12. Loop
  13.  
  14. DoCmd.Hourglass False
  15.  
  16. Close #1

7 10185
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Const conPATH_TO_TEXT_FILE As String = "C:\Test\Test.txt"
  2.  
  3. Open conPATH_TO_TEXT_FILE For Input As #1       'Open File for Input.
  4.  
  5. DoCmd.Hourglass True
  6.  
  7. Do While Not EOF(1)
  8.   Line Input #1, strLine                        'Reads the Row within the file.
  9.     If Len(strLine) <> 0 Then
  10.       'Append to Open Recordset here
  11.     End If
  12. Loop
  13.  
  14. DoCmd.Hourglass False
  15.  
  16. Close #1
Oct 24 '10 #2
JeremyI
49
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...
Oct 24 '10 #3
ADezii
8,800 Expert 8TB
If you need a hand with the Analysis, we're here...
Oct 24 '10 #4
JeremyI
49
Oh, I don't doubt it! ;-) But I'll give it a fair shot first.
Oct 24 '10 #5
JeremyI
49
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:

Expand|Select|Wrap|Line Numbers
  1. ItemID        AutoNumber, Primary Key
  2. ItemFKey      Long Integer (this represents the sequential line number I generated with code)
  3. ItemDate      Date/Time
  4. ItemType      Text(10)
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. qry11SelectDates
  2. ----------------
  3. SELECT tblItem.ItemID, tblItem.ItemFKey, tblItem.ItemDate
  4. FROM tblItem
  5. WHERE (((tblItem.ItemDate) Is Not Null));
  6.  
Expand|Select|Wrap|Line Numbers
  1. qry12DatesLessThanItems
  2. -----------------------
  3. SELECT tblItem.ItemID AS NonDateID, qry11SelectDates.ItemID AS DateID, qry11SelectDates.ItemFKey AS DateFKey, tblItem.ItemFKey
  4. FROM tblItem, qry11SelectDates
  5. GROUP BY tblItem.ItemID, tblItem.ItemDate, qry11SelectDates.ItemID, qry11SelectDates.ItemFKey, tblItem.ItemFKey
  6. HAVING (((tblItem.ItemDate) Is Null) AND ((qry11SelectDates.ItemFKey)<[tblItem].[ItemFKey]))
  7. ORDER BY tblItem.ItemID, tblItem.ItemDate;
  8.  
Expand|Select|Wrap|Line Numbers
  1. qry13MatchItemsDates
  2. --------------------
  3. SELECT qry12DatesLessThanItems.ItemFKey, Max(qry12DatesLessThanItems.DateFKey) AS MaxOfDateFKey
  4. FROM qry12DatesLessThanItems
  5. GROUP BY qry12DatesLessThanItems.ItemFKey
  6. ORDER BY qry12DatesLessThanItems.ItemFKey, Max(qry12DatesLessThanItems.DateFKey);
  7.  
Expand|Select|Wrap|Line Numbers
  1. qry14TabularItemsOnDates
  2. ------------------------
  3. SELECT tblItem_1.ItemDate, tblItem.ItemType, tblItem.ItemValue
  4. FROM (qry13MatchItemsDates INNER JOIN tblItem AS tblItem_1 ON qry13MatchItemsDates.MaxOfDateFKey = tblItem_1.ItemFKey) INNER JOIN tblItem ON qry13MatchItemsDates.ItemFKey = tblItem.ItemFKey
  5. ORDER BY tblItem_1.ItemDate, tblItem.ItemType;
  6.  
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.
Oct 28 '10 #6
ADezii
8,800 Expert 8TB
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.
Oct 28 '10 #7
JeremyI
49
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).
Attached Files
File Type: zip LogicalTest.zip (11.8 KB, 149 views)
Oct 29 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Joey Martin | last post: by
3 posts views Thread by puzzlecracker | last post: by
24 posts views Thread by rudranee | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.