Parsing Text file and import into Access Table.

[TerryM]

First off let me say that i'm pretty much a VBA Beginner.

My issue is I have to go out onto the internet and download a text file.
The Text file has multiple different columns and over 7000 rows. Out of
there 7000+ rows I only need about 150 of the rows. The rows are broken down
by contract tag such as "BB" or "CC" and so on then Month then Year then
Todays Settlement, etc. Out of all these rows I need the one's with the
contract tag "EJ" and "EM", this would be rather easy to just import them
into Excel and do a Paste link to a different location and then have Access
import the named range. However the amount of contract tags change on a
daily basis. One day "EJ" could start on row 3000 and the next it could be
2900 or 3019, etc.

So what I need to figure out is how to parse just the rows and the 4 colomns
that I need out and import just that data into a Access table.

Here is a link to the text file in question:
http://www.nymerc.com/futures/innf.txt

 

[Allen Browne]

1. Edit the text file to remove the headings.

2. Create a temporary table to hold the 8 columns.

3. Use the import wizard to import it into the temporary table. During that
process, click the Advanced button to create an Import Specification for the
fixed-width columns.

4. Create an query, with criteria on the first column of:
IN ("EJ", "EM")
Change it to an Append query (Append on query menu.)
Append it to your real table.

If you know what you are doing and you have to run this every day, you could
automate steps 2 - 4 with TransferText followed by the append query.

 

[Douglas J. Steele]

If you could strip off the first 5 rows, you could link to it (through File
| Get External Data | Link Tables, assuming you're using Access 2003 or
earler) and then write a simple query to only return those records of
interest to you.

Another option would be to read the file in row-by-row, using the Line Input
# statement. For each row of interest to you, write to the database:

Dim intFile As String
Dim strBuffer As String
Dim strFile As String

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
Open strFile For Input As #intFile

Do While EOF(intFile) = False
Line Input #intFile, strBuffer
Select Case Left(strBuffer, 2)
Case "EJ", "EM"
' This is a row of interest to you...
' Break the content of strBuffer into individual fields
' using the Mid function.

Case Else
' Ignore
End Select
Loop

Close #intFile

 

[TerryM]

Allen,

I have tried to use the import wizard to import the text table, However I
keep on getting "internal internet failure" error. I have been unable to
figure out how to rectify this issue. I have also attempted the trasnfertext
code and have had the same error. I have researched the internet in an
attempt to solve this issue, with no luck.

Thank you for your response.

 

[TerryM]

Mr. Steele,

First off I would like to thank you for your assistance so far, as I stated
before i'm kinda of a beginner to a lot of this. The
http://www.nymerc.com/futures/innf.txt
link is where I need the code to go out and capture the txt file not from
my harddrive. In addition how or where do I tell the code to put it into the
database table?

 

[Allen Browne]

Allen,

I have tried to use the import wizard to import the text table, However I
keep on getting "internal internet failure" error.

Save the text file locally?

 

[Douglas J. Steele]

You're far better off copying the file to your hard drive.

To copy, use the URLDownloadToFile API call. Here's its declaration:

Declare Function URLDownloadToFile _
Lib "urlmon" _
Alias "URLDownloadToFileA" ( _
ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long _
) As Long

and here's a little "wrapper function"

Private Function DownloadFile( _
URL As String, _
LocalFilename As String _
) As Boolean

On Error GoTo Err_DownloadFile

Dim lngRetVal As Long

lngRetVal = _
URLDownloadToFile(0, URL, LocalFilename, 0, 0)

DownloadFile = (lngRetVal = 0)

End Function

I'm afraid that you haven't given me enough details to be able to provide
code to put the data in a table.

Here's a typical row in the file:

EJ 09 08 32.05 31.81 0 .00 .00

Now, 32.05 is "Today's Settle", 31.81 is "Previous Settle", the 0 is
"Estmated Volume", the first .00 is "Daily High" and the second .00 is
"Daily Low". Is "EJ 09 08" the Contract, or should that be split into
three component parts?

What's the name of the table to which you want to store them, and what are
the fields in that table?

 

[TerryM]

Mr. Steele,

The Database is called ForeCasting.db, the table is called Futures, fields
are Contract "EJ", Month "09", Year "08", Settle "32.05. However I do need a
date field so I will know what date the data was imported into the database.
The other fields are not needed.

 

[TerryM]

I have saved the text file locally and it works this way for the import as
you described. However I still need to have this done automatically. I'm
doing web searchs to try to figure out how to do this. In addition I'm
trying to automate steps 2-4 like you said.

 

[Douglas J. Steele]

First of all, rename the fields Month and Year. Those are reserved words in
Access, and should never be used for your own purposes. For a comprehensive
list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

Let's assume you rename them to DetailMonth and DetailYear. (I'm assuming
you're making them numeric fields: integers or even bytes)

Once you've got the text file onto your hard drive, the code you'll need is:

Dim dbCurr As DAO.Database
Dim intFile As String
Dim lngMonth As Long
Dim lngYear As Long
Dim curSettle As Currency
Dim strBuffer As String
Dim strContract As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
Open strFile For Input As #intFile

Do While EOF(intFile) = False
Line Input #intFile, strBuffer
strContract = Left(strBuffer, 2)
Select Case strContract
Case "EJ", "EM"
' This is a row of interest to you...
lngMonth = Mid(strBuffer, 7, 2)
lngYear = Mid(strBuffer, 10, 2)
curSettle = Mid(strBuffer, 25, 10)
strSQL = "INSERT INTO Futures " & _
"(Contract, DetailMonth, " & _
"DetailYear, Settle) " & _
"VALUES(""" & strContract & """, " & _
lngMonth & ", " & _
lngYear & ", " & curSettle & ")"
dbCurr.Execute strSQL, dbFailOnError
Case Else
' Ignore
End Select
Loop

Close #intFile

Set dbCurr = Nothing

 

[Douglas J. Steele]

Oops, forgot to address your request for a date field to know what date the
data was imported.

Just add a Date field to your table (call it something like DateCreated),
and set its default value to =Now() (complete with equal sign and
parentheses)

 

[lan huynh]

Hi Doug,

let say i have a table in MS Acccess with two columns: URL_ID, Complete_URL.
for example:
URL_ID = 1
Complete_URL = 'http://www.mssqltips.com/tip.asp?tip=1226'

how do i write a query that parse 'http://www.mssqltips.com' into a new column?

Thanks for any help!
Lan

 

[Douglas J. Steele]

So you want everything before the 3rd slash.

Assume that all the URLs will start with either http:// or https://

Dim lngThirdSlash As Long

If Left(Complete_URL, 7) = "http://" Then
lngThirdSlash = InStr(7, Complete_URL, "/")
If lngThirdSlash > 0 Then
Partial_URL = Left(Complete_URL, lngThirdslash - 1)
Else
Partial_URL = Complete_URL
End If
Else If Left(Complete_URL, 8) = "https://" Then
lngThirdSlash = InStr(8, Complete_URL, "/")
If lngThirdSlash > 0 Then
Partial_URL = Left(Complete_URL, lngThirdslash - 1)
Else
Partial_URL = Complete_URL
End If
End If

Hopefully you're not planning on storing that partial URL in your table.