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. ;-)
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
7 11294 ADezii 8,834
Recognized Expert Expert
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
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 8,834
Recognized Expert Expert
If you need a hand with the Analysis, we're here...
Oh, I don't doubt it! ;-) But I'll give it a fair shot first.
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 8,834
Recognized Expert Expert
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.
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).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Joey Martin |
last post by:
I have a text file being create with the FileSystemObject. It's being
built based on results from a SQL query. All this is working fine, but
so that I have a carriage return VBCRLF at the end of...
|
by: puzzlecracker |
last post by:
I want to read lines and skip blank lines:
would this work considering the lines can contain tabs, spaces, etc.?
file.in:
------
line1
line2
|
by: Stan |
last post by:
hi,
Could any one give me an advice of providing a regular expression that
will return all non-blank lines in a file in linux.
Maybe it is just a command!
Thanks
Stan
|
by: Ryan S |
last post by:
I am trying to read an XML document generated by a web server using the
XMLTextReader class, but the document generated appears to have some blank
lines at the top that are causing problems.
If...
|
by: rudranee |
last post by:
hi there,
can anyone tell me how to lines from a file which are odd numbered i.e.
1st,3rd,5th...lines.
i tried incrementing file pointer by 2 (fp=fp+2)
but it does'nt work
Can someone give me...
|
by: EricR |
last post by:
I am trying to bcp import a text file into a SQL Server 2000 database.
The text file is coming out of a java application where order
information is written to the text file. Each record is on it's...
|
by: td0g03 |
last post by:
Hello, I am trying to write a code to copy a file which I can do as you can see below.
void copy ()
{
// Local Definitions
int c;
int closeStatus;
FILE* spProverbs ;
FILE*...
|
by: BibhuAshish |
last post by:
Hi,
I wanted to delete a line from xml file which i did it. But after deletion of that line there is a blank space. Again if i am adding another line by using java that blank line remains as...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |