Import *.qif file into Access

[Alberto Comino]

Does anyone know how can I import a qif file (old Quicken format for bank
statements, which is the one still used from my online bank) into an MS
Access table?

I have some knowledge of VBA, so I believe I could write the code to get it
done but I imagine something already exits. (Althought Goggleing it gave no
results).

Thanks in advance.

Alberto

 

[Arvin Meyer [MVP]]

Export the file using Quicken to Excel, then import it into Access.

 

[Alberto Comino]

Love workarounds, but doesn't answer my question.

 

[Arvin Meyer [MVP]]

The short answer is no, Access cannot directly import Quicken files. There
is a direct connection possible using QODBC a $200 product which will allow
connecting to a QuickBooks file. I have no idea whether it works with
Quicken though. You might check with them:

http://www.qodbc.com/
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Disclaimer: Any code or opinions are offered here as is. Some of that
code has been well tested for number of years. Some of it is untested
"aircode" typed directly into the post. Some may be code from other
authors. Some of the products recommended have been purchased and
used by the author. Others have been furnished by their manufacturers.
Still others have not been personally tested, but have been
recommended by others whom this author respects.

You can thank the FTC of the USA for making this disclaimer necessary.

 

[Alberto Comino]

Arvin, thank you for elaborating the answer.

The problem is that I don't have Quicken and don't plan to buy it after
testing a demo. The thing is that *.qif (or for the same *.ofc) are the only
extractions possible from my bank. I know it's completely outdated -have
already complained to the bank- but that is the way it is. I have a macro in
Excel that imports the files, but I intend to build it in Access to make the
process smoother and especially safer.

Anyway, I'll put aside some time to write all the parsing in Access VBA but
I really thought someone else had gone through all this before.

Alberto

 

[Clif McIrvin]

Anyway, I'll put aside some time to write all the parsing in Access
VBA but I really thought someone else had gone through all this
before.

Alberto

It's entirely possible that someone has ... have you tried posting your
question in other Access forums? You have reached a substantial audience
here, but while AccessMonster (for instance) copies these NNTP posts, I
don't believe UtterAccess does, and cdma is a completely different
newsgroup. I have no idea how many different forums are out there ... I
have visited several from time to time but this one is where I lurk.

HTH

 

[David W. Fenton]

The short answer is no, Access cannot directly import Quicken
files. There is a direct connection possible using QODBC a $200
product which will allow connecting to a QuickBooks file. I have
no idea whether it works with Quicken though.

Intuit provides its own QuickBooks developers toolkit that allows
working directly with the QB files. I don't believe you need to pay
to get that capability. I don't know if it provides the capability
to read the QIF files directly.

It's important to note that QIF is an export/import format, and not
the actual Quicken data file. It's a text file, but with multiple
data sets, each with different row types. It's tedious but not all
that complex to open the file and walk through it and figure out
where the boundaries are. I did it once about 10 years ago, but
haven't had need for it since.

 

[judybirkenhead]

Does anyone know how can I import a qif file (old Quicken format for bank
statements, which is the one still used from my online bank) into an MS
Access table?

I have some knowledge of VBA, so I believe I could write the code to get it
done but I imagine something already exits. (Althought Goggleing it gave no
results).

Thanks in advance.

Alberto

I just needed this myself and could not find it anywhere.

here tis - you will need to change the path for saving your file

Sub ExportToQIF()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilePath As String
Dim strQIF As String
Dim strDate As String
Dim strDescription As String
Dim strAmount As String
Dim strTableName As String
Dim intFileNum As Integer

' Prompt the user for the table name
strTableName = InputBox("Enter the name of the table to export:", "Table Name")

' Check if the user entered a table name
If strTableName = "" Then
MsgBox "No table name entered. Export cancelled.", vbExclamation
Exit Sub
End If

' Set the file path for the QIF file
strFilePath = "C:\Users\judyb\OneDrive\Documents\Private\file.qif"

' Open the recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Date, Description, Amount FROM [" & strTableName & "]")

' Initialize the QIF string
strQIF = "!Type:Bank" & vbCrLf

' Loop through the records and build the QIF string
Do While Not rs.EOF
strDate = Format(rs("Date"), "mm/dd/yyyy")
strDescription = rs("Description")
strAmount = Format(rs("Amount"), "0.00")

strQIF = strQIF & "D" & strDate & vbCrLf
strQIF = strQIF & "T" & strAmount & vbCrLf
strQIF = strQIF & "P" & strDescription & vbCrLf
strQIF = strQIF & "^" & vbCrLf

rs.MoveNext
Loop

' Close the recordset
rs.Close
Set rs = Nothing
Set db = Nothing

' Write the QIF string to the file
On Error GoTo ErrorHandler
intFileNum = FreeFile
Open strFilePath For Output As #intFileNum
Print #intFileNum, strQIF
Close #intFileNum

MsgBox "Export to QIF completed successfully!", vbInformation
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
If intFileNum > 0 Then Close #intFileNum
End Sub