Searching through a text file in excel using databse commands????

[Mightyginger]

Hi,

I have a text file with a lot of dates in it - they are holidays. In excel I need to write some vba code whereby I can type a date into a cell and it then searches through the textfile and checks to see if that date exists - if it does then it's a holiday.

The textfile looks like this.

01/01/03
02/01/03
03/01/03
(e.g day/month/year)
but can be made up ina different format if necessary.


My first though was just to open the text file, read it into an array and then check through the array to see if it existed. Someone has suggested that it might be easier instead to set the textfile as a db and then search through it directly and treat it just like a db. I've not used any of these commands before and was wondering (this is a bit cheeky I know) if someone could give me a rough outline of how the code might do to once the textfile is set as the db to then query a variable against the entries to see if the variable exists in the file.


Thanks again guys,


Neil.

 

[Nikita6003]

Hiya,

here's something I knocked up recently to test using a delimited file as a data source. It uses ADO rather than DAO as this is the standard data access method our tools use.

The sub TEST is the main routine; it calls the sub OpenConnection, which opens a "database" connection to the text file. As this was a test I hardcoded all filenames/paths etc, so the code assumes you have a delimited text file with field names as the first line in C:\Temp called tblNames.txt

Hope this is enuff to get you started; if not, let us know

Private m_cnTextFile As ADODB.Connection

Public Sub OpenConnection(p_sPath As String)
    Dim l_sConnection As String
    
    Set m_cnTextFile = New ADODB.Connection
    l_sConnection = "Provider=MSDASQL.1;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & p_sPath & ";" & _
                    "Initial Catalog=tblNames.txt;Extended Properties='text;HDR=NO;FMT=Delimited'"
    
    With m_cnTextFile
        .ConnectionString = l_sConnection
        .ConnectionTimeout = 0
        .Open
    End With
    
End Sub


Sub TEST()
    Dim l_rsTest As New ADODB.Recordset
    Dim l_lRow As Long
    Dim l_iCounter As Integer
    
    Call OpenConnection("C:\")
    l_rsTest.Open "Select * from tblNames.txt", m_cnTextFile, adOpenStatic, adLockReadOnly, adCmdText
    l_lRow = 1
    
    For l_iCounter = 0 To l_rsTest.Fields.Count - 1
         ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, l_iCounter + 1) = l_rsTest.Fields(l_iCounter).Name
    Next l_iCounter
    l_lRow = l_lRow + 1
    
    Do Until l_rsTest.EOF
        ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, 1) = l_rsTest.Fields(0).Value
        ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, 2) = l_rsTest.Fields(1).DataFormat
        ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, 3) = l_rsTest.Fields(2).Value
        ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, 4) = l_rsTest.Fields(3).Value
        ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, 5) = l_rsTest.Fields(4).Value
        l_lRow = l_lRow + 1
        l_rsTest.MoveNext
    Loop
    
    l_rsTest.Filter = "Name = 'Nikki'"
    MsgBox "The filter results in " & l_rsTest.RecordCount & " records."
    
End Sub

BTW - it was knocked up in Excel so should be useable for your purposes

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

cheers - you're a star. Thanks.

 

[Mightyginger]

I've pinched this code from Wrox's book Excel 2002 Prog Ref.

The only problem is that it comes up with the error "User-defined type not define" on the line "Dim rsData As ADODB.Recordset"

Any ideas why this is? Do I need to install some database updates? Running office 2000.

Also, as a newbie whats the difference between using MSASQL.1 and Jet4.0?

Public Sub QueryTextFile()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

'Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\" & _
"Extended Properties=Text;"

'Create the SQL statement.
szSQL = "SELECT * FROM dates.txt WHERE Date='01/01/03';"
'Dave here I'd aim to change the dates.txt to a variable and also the data as a variable - just trying to get it to work at the moment!!!!!
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

'Check to make sure received data.
If Not rsData.EOF Then
'Dump returned data onto Sheet 1
Sheet1.Range("A1&quot.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

'Tidy up.
rsData.Close
Set rsData = Nothing


End Sub


Thanks again guys.


Neil.

 

[Nikita6003]

Neil,

you need to set a reference to ADO before you can start using it ... in the Excel VBA Editor, choose TOOLS > REFERENCES & search for Microsoft ActiveX Data Objects Library 2.x
The .x will vary according to which version of Office you've got installed.

If you can't find this reference you'll need to install the MDAC files (aka Microsoft Data Access Components to the best of my knowledge of acronyms - although I *could* come up with a different solution ... ;-))
It's available for free download from the MS support site

As for the difference between Jet and MSDASQL - these are just different data interpreters so have different ways of talking to the underlying data. MSDASQL = the standard ODBC provider; Jet = a provider specifically written to deal with connections to an Access database. Never used it for text files before ...

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

What a superstar!!! Yup, got that sorted (and that explains something else I couldn't get to work).

Okay, so quick question.

All I want to do is have a variable that I pass into the function (it's going to be a date) and I want to see if it exists in textfile. I'm surethis must be quite a simple query but I'm struggling to get it working.

So using this code,

Public Sub QueryTextFile()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

'Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\;" & _
"Extended Properties=Text;"

'Create the SQL statement.
szSQL = "SELECT * FROM dates.txt WHERE Date='01/01/03';"

'Okay this is the bit of code I'm stuck with - how do I use this command to query to see if an entry exists?
'Also, what about if I don'twant to have Dates at the top of the file and instead just want to have th etextfile filled with dates? How would I query it then because here it looks in the field "Date"

' No real idea what the rest of this does.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

'Check to make sure received data.
If Not rsData.EOF Then
'Dump returned data onto Sheet 1
Sheet1.Range("A1&quot.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

'Tidy up.
rsData.Close
Set rsData = Nothing


End Sub

 

[Mightyginger]

Just to be a bit clearer - everything works great up to a point. So thanks for that.

If use the command szSQL = "SELECT * FROM dates.txt;" then it successfully dumps all the data from the file into excel. However, my problem now is trying to query the contents of the file.

The file is a text file laid out like this.

Date
01/01/03,02/01/03,04/12/03

I'm trying to do
szSQL = "SELECT * FROM dates.txt WHERE Date='01/01/03';"
and I'm getting a type mismatch error. How should I be querying this?

Also, what about if my file looked like this
Date
01/01/03
02/01/03
04/12/03
how would this change the query?

I understand the rest of the code now - thank you.

 

[Nikita6003]

Hiya ginge,

coupla remarks:
1. you're querying on a date but using single quotes. That's causing the type mismatch: use hashes rather than quotes. The data provider is parsing data types when it tries to execute the select statement and it's picked up on the fact you've got dates rather than text in your data file
2. The SECOND example of your text file layout would appear to me what you should be using. The FIRST one would result in ONE record with an x number of fields. Trying to query on date would therefore *always* result in one record being returned.
3. You should use the hash rather than the sgl quote whichever layout you use.
4. Your select statement in itself would not have to change (apart from the hashes): use

SELECT * FROM Dates.txt WHERE Date = #01/01/03#

Just treat the text file as if it was a table you're querying - can't go wrong much if you do ... ;-)

HTH!


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

Thanks for your help - just the syntax that kills me. Off to get an ADO book later to read up but I have one quick question if you wouldn't mind - how do I pass varialbles in?

Say I Dim thedate As Date then set thedate=#01/01/2003# then want to do
szSQL = "SELECT * FROM Dates.txt WHERE Date = '&thedate&';"
what's the correct syntax for passing in "thedate"?

Also what about if I want to changte dates.txt to a variable as well? I'm assuming this will be very similar to thedate question.

Thanks again for your help,



Neil.

 

[Nikita6003]

Well, you're on the right track.
To change the date to a variable use:

Dim l_datDate as Date

'Use the mmm date format to make sure xl parses your date correctly!
'It'll change to d/m/yy format once you enter this in the VBA editor
l_datDate = #1-Jan-2003#

szSQL = "SELECT * FROM Dates.txt WHERE Date = #" & datDate & "#;"

To change the name of the text file to a variable as well use

Dim l_datDate as Date
Dim l_sDatabaseFileName as string

l_datDate = #1-Jan-2003#
l_sDatabaseFileName = "C:\Temp\dates.txt"

szSQL = "SELECT * FROM " & l_sDatabaseFileName & " WHERE Date = #" & datDate & "#;"

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

Yup, works a treat. Thank you very much matey - owe you quite a few drinks I feel ;o)

Off to get a ADO book so I can extend my knowledge a bit more.


Cheers,



Neil.

 

[Mightyginger]

There is one slight problem.

My text file looks like this

Date
01/01/03
02/01/03
04/12/03

Now when I query it with 01/01/03 it finds it BUT when I query with 02/01/03 or anything further down it doesn't. Do I need to modify my statement somehow? I thought it would search through the entire field.

Sorry to pester you again,



Neil.

 

[Nikita6003]

Hi Neil,

no worries - I quite like being bothered ;-)

I *think* it's a date issue. When I run the ciode using #2/1/03# in the WHERE clause it'll find the correct date without any problems. As your query *does* run on 1/1/03 but not on 2/1/03 I have the feeling that xl and the text file are getting the month/day order mixed up. xl is probably picking up 2/1/03 and treating it as 1-FEB-2003 rather than 2-JAN-2003

To check how xl interprets the date, open the text file in XL and format the date column as dd-mmm-yyyy. Do the dates come out as you expect?

Let me know how you get on ... just call me MightyCurious ;-)




Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

Yes, it is a date issue.

VBA seems to be treating the dates in American format for some reason. BUT when I'm manipulating the date i.e. adding 2 days on to the inital date of 1/1/2003 it adds it on here 3/1/2003 using the dateserial command but then later think of this as 1st Mar 03. I don't think I've explained this very well but I've included all the code below and having the locals window open and stepping through you'll see what happens to the date. Annoyingly even though in the locals window it has the variable L_datData as #03/01#2003# it looks in the textfile for 01/03/03. How annoying.

Sub GingerBumpDates()
'Function GingerBumpDates(valdate As Date, num As Integer)
Dim valdate As Date
Dim num As Integer
Dim code As Variant
Dim year_num As Integer
Dim month_num As Integer
Dim day_num As Integer
Dim i As Integer
Dim Bobber As Variant

valdate = #1/1/2003#
num = 2
code = "eur"

year_num = Year(valdate)
month_num = Month(valdate)
day_num = Day(valdate)
valdate = DateSerial(year_num, month_num, day_num + num)

i = 0
Do Until week_num > 1 And week_num < 7 And flag = False
valdate = GingerWeekday(valdate)
flag = QueryTextFile(valdate)
week_num = Weekday(valdate)
year_num = Year(valdate)
month_num = Month(valdate)
day_num = Day(valdate)
valdate = DateSerial(year_num, month_num, day_num + i)
i = i + 1
Loop


End Sub
Function GingerWeekday(inputdate As Date)
Dim checkyear As Integer
Dim checkmonth As Integer
Dim checkday As Integer
Dim checkweekday As Integer
Dim i As Integer
Dim tempdate As Date

checkyear = Year(inputdate)
checkmonth = Month(inputdate)
checkday = Day(inputdate)
i = 0

Do Until checkweekday > 1 And checkweekday < 7
tempdate = DateSerial(checkyear, checkmonth, checkday + i)
checkweekday = Weekday(tempdate)
i = i + 1
Loop
GingerWeekday = tempdate

End Function


Function QueryTextFile(l_datDate As Date)

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
'Dim l_datDate As Date
Dim l_sDatabaseFileName As String

'l_datDate = #1/1/2003#
l_sDatabaseFileName = &quot;dates.txt&quot;

'Create the connection string.
szConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=H:\;&quot; & _
&quot;Extended Properties=Text;&quot;

'Create the SQL statement.
'szSQL = &quot;SELECT * FROM Dates.txt WHERE Date = #02/01/03#;&quot;
szSQL = &quot;SELECT * FROM &quot; & l_sDatabaseFileName & &quot; WHERE Date = #&quot; & l_datDate & &quot;#;&quot;

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

'Check to make sure received data.
If Not rsData.EOF Then
'Dump returned data onto Sheet 1
'QueryTextFile = True
Sheet1.Range(&quot;A1&quot.CopyFromRecordset rsData
Else
QueryTextFile = False
'MsgBox &quot;No records returned.&quot;, vbCritical
End If

'Tidy up.
rsData.Close
Set rsData = Nothing


End Function

 

[Nikita6003]

Neil,

I know & commiserate.

these date things have to do with the Regional, locale & language settings & can be hell to get right. Also, if your code has to run on more than one PC with different date/language settings on each you'll need to find a solution away from changing PC settings.

Is there any way that you can have the text file created using a different date format? I'd suggest using dd-mmm-yyyy or (to be 100% safe) yyyymmdd. This way, you and xl *both* know what dates you're pulling in and trying to query.




Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

Good thinking Batman. But just getting silly now!!!

What I've done is I've got it to write to cell(1,1), or R1C1 if you're that way inclined, the value of the date it's manipulating all the way through (even in the sub routines it's using). All the time it's got 3-Jan-03 and then it goes to check the f'ing text file which has both 03-Jan-03 and 3-Jan-03 and doesn't find either. But when I put 1-Mar-03 it finds a match in the file. How very, very annoying.



Neil.

 

[Mightyginger]

Actually, I think VBA stores the date as US and then excel displays it blindly as UK. Then when you use the Day, Month and Year commands I guess it looks at the country code and then picks the apropriate part from the date. So the day and month are the wrong way round if this makes any sense.

Then when it checks the file it tries to find the US format.

 

[Mightyginger]

Simple solution - have all dates in US date format. Done.


Thanks for your help.



Neil.

 

[Nikita6003]

yw

glad it's solved


Cheers
Nikki
Look, mommy, I'm flying!

 

[Mightyginger]

Hi Nikki,

Justextending what I've done and wondering if you've got yet another 2 minutes to help.

Firstly, thank you. All my code now works and it's all had a bit of an overhaul but it's all correct thankfully.

Problems/Extensions.
Instead of this l_sDatabaseFileName = &quot;dates3.txt&quot; I'd like to have &quot;gbp.txt&quot; where gbp is a variable so can I put something like &variable&&quot;.txt&quot; or whats the best way of doing this? Also how to I change
szConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=H:\;&quot; & _
&quot;Extended Properties=Text;&quot;
to having the Data Source= to a variable?

Basically the same problem, just lacking knowledge of the syntax. Thanks again,



Neil.