exporting/importing QIF files


J

JIP

I've posted earlier on this but it seems easier now to start a new thread to
start from where I've reached.

I am in the process of moving from the UK version of Quicken 2000 deluxe to
2008 Home & Business. And at the same time moving to Vista 64 bit. And I am
almost there!!!

With help from this group, and especially from John Pollard, I've been able
to get printing working, to set 2008 up to use sterling as the default
currency, and learned how to export QIF files from the old version, then
import those into a temporary cash account, and then copy all those
transactions into the real account. Wonderful!

Except for the date field!! This is the final issue.

The imported data in 2008 is in the FORMAT I want ( i.e. DD/MM/YYYY - the
standard for us Brits) but YYYY shows as 19** instead of 20** (e.g 1908
instead of 2008).

I've tried using the Excel macro xl2qif. When I import a QIF file into it,
the dates show as they should (i.e. 2007 for instance, not 1907), but when I
try to save as a new QIF file I keep getting error messages "Invalid dates
in date column". I've made sure that the data range selected does not
include column headings when I've been doing this. I am using Excel 2007.

In case it's relevant I've noticed that in the 2000 version of Quicken,
dates in the transaction windows show as DD/MM/YY, whereas in the 2008
version they show as DD/MM/YYYY. I've searched around Quicken to find a date
display/format setting but couldn't find one - if I could just have the last
two digits of YYYY displayed there would be no further problems.

All suggestions welcome.
 
Ad

Advertisements

J

John Pollard

JIP said:
I've posted earlier on this but it seems easier now to start a
new
thread to start from where I've reached.

I am in the process of moving from the UK version of Quicken
2000
deluxe to 2008 Home & Business. And at the same time moving to
Vista
64 bit. And I am almost there!!!

With help from this group, and especially from John Pollard,
I've
been able to get printing working, to set 2008 up to use
sterling as
the default currency, and learned how to export QIF files from
the
old version, then import those into a temporary cash account,
and
then copy all those transactions into the real account.
Wonderful!

Except for the date field!! This is the final issue.

The imported data in 2008 is in the FORMAT I want ( i.e.
DD/MM/YYYY -
the standard for us Brits) but YYYY shows as 19** instead of
20**
(e.g 1908 instead of 2008).

I've tried using the Excel macro xl2qif. When I import a QIF
file
into it, the dates show as they should (i.e. 2007 for
instance, not
1907), but when I try to save as a new QIF file
I keep getting error
messages "Invalid dates in date column".
That message box also will give you the cell where the error is
found and cell value.

Are you sure you have correctly defined the format of the data
for outputting? That you have the correct column designated for
the date (and all other fields)?
 
J

John Pollard

JIP said:
In case it's relevant I've noticed that in the 2000 version of
Quicken, dates in the transaction windows show as DD/MM/YY,
whereas
in the 2008 version they show as DD/MM/YYYY. I've searched
around
Quicken to find a date display/format setting but couldn't
find one -
if I could just have the last two digits of YYYY displayed
there
would be no further problems.
I should have mentioned in my previous reply that:

1.) Quicken's date format is controlled by the System Date
format (Control Panel in XP).
2.) Quicken stores its dates with a 4 digit year (actually two
digit century + two digit year). If you could only see the year
and not the century, your Quicken dates would still be wrong ...
you just wouldn't be able to see the problem.
 
J

JIP

John Pollard said:
That message box also will give you the cell where the error is found and
cell value.

Are you sure you have correctly defined the format of the data for
outputting? That you have the correct column designated for the date (and
all other fields)?
Hmmm - well the cell reference that is cited is b4, with a value of 100, but
that isn't the date column.

When I import the QIF file into excel, the columns are in the order Date,
Amount, Cleared Status, Check Number, Payee, Category, Sub-category, Memo.

Once the file has been imported, I have then selected the whole data range
(not including column titles) and selected Save to QIF, then ticked the
fields to include (such as date, amount etc) then clicked on "Convert". That
is when I get the message "Invalid values in Date column B4: 100"

But column B is not "date"

Am I supposed to move the columns into the order in which they appear when I
am selecting which fields to include?

Or is there something else I'm missing?

Thanks again
 
Ad

Advertisements

J

John Pollard

JIP said:
Hmmm - well the cell reference that is cited is b4, with a
value of
100, but that isn't the date column.

When I import the QIF file into excel, the columns are in the
order Date, Amount, Cleared Status, Check Number, Payee,
Category,
Sub-category, Memo.
Once the file has been imported, I have then selected the
whole data
range (not including column titles) and selected Save to QIF,
then
ticked the fields to include (such as date, amount etc) then
clicked
on "Convert". That is when I get the message "Invalid values
in Date
column B4: 100"
But column B is not "date"

Am I supposed to move the columns into the order in which they
appear
when I am selecting which fields to include?
Sort of.

It is the order in which you "tick" the fields to output that
determines their "sequence" in the spreadsheet. The first field
you "tick" must be the leftmost field (column), the second field
you "tick" must be the next leftmost field (column), etc. If
the first field you put a check mark in was "Check Number",
"xltoqif" would assume that the leftmost field/column was "Check
Number" ... even if that column was really the date.

If you have ticked the fields in the wrong order, just click the
"Clear" button, and initiate the selection process again, with
particular attention to the order in which you make the
selection. When you have finished the field selection process,
look below at the "Column order in Input Data" box to verify
that you have selected correctly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export import Qif files 1
QIF--export/import 3
Exporting a QIF File 2
Export / Import QIF Question 3
No QIF Import - but only QIF Export available 3
Importing QIF files 13
import Qif file 0
Importing QIF files 0

Top