How do I consolidate several tables in one long table? « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

How do I consolidate several tables in one long table?

(31 posts) (3 voices)

Started 1 year ago by moneymonitors
Latest reply from asa

Tags:

12 Next »

moneymonitors

Member
Joined: Sep '11
Posts: 15

How do I merge/consolidate several tables (bank statements) of slightly different format into a single 'allTranactions' list of just specific columns?

Sheet called BankA:-

Transaction Date Transaction Type Sort Code Account Number Transaction Description Debit Amount Credit Amount Balance
03/08/2011 DEP 11/07/1920 21928 CHAPS 3764.09 17537.06
03/08/2011 FPI 11/07/1920 21928 986843 11000 13772.97
02/08/2011 BGC 11/07/1920 21928 QUIDCO 3.25 2772.97
01/08/2011 11/07/1920 21928 Reward (Net) 5 2769.72
01/08/2011 DD 11/07/1920 21928 BRGAS-ELECTRICITY 8500434035 41.5 2764.72

Sheet called BankB:-

Date Date entered Reference Description Amount
29/07/2011 01/08/2011 2109000 ITUNES-GBP 0.79
30/07/2011 01/08/2011 2177007 SAINSBURY'S S/MKT 70.02
29/07/2011 01/08/2011 2153900 VAN DYKE 5.19
26/07/2011 26/07/2011 2011111 DIRECT DEBIT THANK YOU -288.09

Sheet 'allTranactions'

Date Money to Catergory Note MoneyOUT MoneyIN Overall Balance

in the 'allTranactions' the dates map, the the Descriptions map to the 'Money to' and the Amount from BankA and BankB map to MoneyIN and MoneyOUT depending on - or +.

I found a post http://chandoo.org/forums/topic/merge-matched-data-from-two-spreadsheets that talked about =INDEX(Sheet2!$A$1:$B$3,MATCH(A1,Sheet2!$A$1:$A$3,0),2)

Which sound like it is the sort of thing I am after.

Any pointer and help would be appreciated, I am going out this evening but will check in later tonight.

Kind regards
Giles

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Depending on your needs, there are various ways to merge tables that are laid out differently.

One simple way is to manually modify the layout to be the same, then copy and paste the data from one sheet to the other (or both to a new sheet).

The topic you linked to appears to be dealing with merging associated rows from two tables of data, which doesn't seem to apply to your case.

A couple of higher level solutions come to mind, if this is a repeated task: Create a QueryTable with a union query of the two tables you mention, or write a macro to automate the merge operation. I can offer more details if interested.

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Oh, one more suggestion. You could consider keeping all the transaction tables in one table to begin with. In that case, you would probably want an added column to indicate which bank/bank account the transaction was associated with.

Then, you could filter that table on bank account when only interested in one particular account.

This would be essentially the same effort as the "simple way" I outlined. It would probably perform better than the QueryTable, transparently be consistent with the individual accounts' data, and the process of making the columns match for the multiple accounts when you get new data/a new statement could still be automated if desired using a macro.

Posted 1 year ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,030

Moneymonitors

Firstly, Welcome to the Chandoo.org Forums

As Asa said there are a number of ways
In addition to Asa idea, you can export them all to Access, it will sort out the fields automatically and the reimport

Similarly you can use an SQL query to retrieve on certain fields from each sheet and merge them.

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa & Hui

Thanks for the replies & the welcome, I have been watching several youtube videos from excelisfun and so far come up with taking each statement (made them in to tables) and sorting it into the format I want the 'master' statement to look like using
=IF(Table2[[#This Row],[Transaction Date]]<>"",Table2[[#This Row],[Transaction Date]],"0")
But I was wondering why I was getting #VALUE! if I extended the formula down, but realized that it was because I was extending it below the bottom of the table that the formula is using!

I had thought of adding a column for the bank account - thanks for reminding me :-)

I shall look into the QueryTable & look up a union query.

I thought about Access but was hoping I could do it all simply in excel.

Kind regards
Moneymonitors

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa & Hui

I have just been looking at QueryTables and they appear to be a worksheet data object that has been create from a query of external data.

1. I was just planning of getting the bank statements in csv format and importing the data into a worksheet, a single worksheet per bank/card account. [Are you suggesting that the bank statement can be got with a web query of the banks website?]

2. I was planning of importing the individual bank statements and then combining them (hopefully automatically) into one big master statement. The categories spending, then use a pivot table to create a budget report.

"keeping all the transaction tables in one table to begin with" this is what I'd like in the first place but the bank statements from different banks have different columns.

So please point me to where a good place to start learning about QueryTables is and also about using access with excel.

Many thanks
Moneymonitors

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185
Hi moneymonitors!

I'm new to some of the stuff I recommended, as I have just started using Excel again for almost the first time in over 10 years, so if I miss something in my advice hopefully others will comment on it. But I have been experimenting with the features I talked about and have extensive prior database and VBA experience, so I have a foundation.

I'm just learning about Tables and QueryTables myself. I don't quite follow the formula that you mentioned in your comment. Does your formula refer to a column with the heading "This Row" (which I would think would have the # placed more like [#[This Row]]? Or does it refer to the row # in the table without any special column having the row # in it?

You're right about QueryTables -- but a trick that is not so obvious is that you can refer to tables or data in your worksheets in your self-same Excel file using queries or SQL statements (a QueryTable has a SQL statement indicating where to get it's data). The data does not really have to be "external".

I wasn't talking about using web queries, although it might be possible. The security on bank websites could pose a problem. Also, I don't know if web queries can pull data from a CSV file -- they can pull data from a regular table on a web page, but the tabulated data for viewing in the browser might not contain the whole range of transactions you want. Basically I wasn't suggesting this :) I would wait on trying to automatically get data from your bank websites.

OK, here are some steps that I think will give you a merged All Transactions worksheet for all your accounts, using a QueryTable. These steps should work in Excel 2010 (the version I am working with) but could require some modification in other versions of Excel.

1. Create a worksheet for each account, as you said, and import the data (CSV, etc.) -- make sure there are column headings.. each account can have difference headings and types of data for this method. Rename each worksheet to reflect the name of the account. For example "Bank A" and "Bank B".
2. In a text editor, you will need to write a SQL statement to merge all your bank's transactions:
a. Start by typing in a list of all the column headings each CSV file has. You can skip some of them if you know you don't need them in the QueryTable.
b. Decide what name you want each column to have in the Query table. For example, in your Bank A data you have a "Transaction Date" column, and in your bank B data you have a plain "Date" column. Decide which column heading you want to use. In your text document, type the new column name several spaces to the right of the original one in your list, like:
```
Transaction Date    Date
```
c. When done, you should have a list of column headings for each worksheet, with the original and new heading for each column.
d. Start with the following as a template for your SQL statement (this based on your initial post's sample data and two bank worksheets called "Bank A" and "Bank B"):
```
SELECT
  'Bank A'                         AS "Account",
  ~Transaction Date~               AS "Date",
  NULL                             AS "Date Entered",
  NULL                             AS "Reference",
  ~Transaction Type~               AS "Type",
  ~Sort Code~                      AS "Sort Code",
  ~Account Number~                 AS "Account Number",
  ~Transaction Description~        AS "Description",
  ~Credit Amount~-~Debit Amount~   AS "Amount",
  ~Balance~                        AS "Balance"
FROM ~'Bank A$'~

UNION ALL

SELECT
  'Bank B'                         AS "Account",
  ~Date~                           AS "Date",
  ~Date entered~                   AS "Date Entered",
  ~Reference~                      AS "Reference",
  NULL                             AS "Type",
  NULL                             AS "Sort Code",
  NULL                             AS "Account Number",
  ~Description~                    AS "Description",
  ~Amount~                         AS "Amount",
  NULL                             AS "Balance"
FROM ~'Bank B$'~
```
e. Change all the ~ characters in the template to backquotes (the backwards single quote character, usually on the same key as the tilde). This forum doesn't seem to allow typing of backquotes. The backquotes are enclosing each column name on the left-hand part of the SQL statement as well as the table/worksheet name.
f. Extend the template to chave a section for each bank account using the column data you collected in steps 2a to 2c -- simply add "UNION ALL" and another "SELECT...FROM" group for each. Make sure you have all the same column headings on the right-hand side for each section. Those headings will be the headings in your merged QueryTable.
g. Note that the worksheet name in the FROM clause of each section has the backquotes around it as well as single quotes inside the backquotes, and there is a $ appended to the end of the real worksheet name!
h. On the left-hand side, you can include certain types of formulas to calculate a value for the QueryTable, if the value you want doesn't exist as-is in that bank's data. Notice the example formula for Bank A's "Amount" column in the template.
i. On the left-hand side, put NULL for any columns going in your QueryTable that do not exist in that bank's data and that you don't intend to calculate.
j. On the left-hand side, you can include constant text strings, as I did to create values for the "Account" column indicating which Bank account. Include the text in single quotes (not backquotes), as I did in the template.
k. There's lots more you can do with SQL but it's a large topic. Please note that following the procedure outlined here you are using the ODBC driver (open database connectivity) to run your SQL statement. There are several flavors of SQL, and this flavor is the ODBC flavor.
3. When you have finished composing your SQL statement (you might want to save it for future reference), go back to Excel.
4. Create an "All Transactions" worksheet. Call it whatever you like.
5. Save your workbook before continuing.
6. In the blank "All Transactions" worksheet, go to the Data tab on the ribbon.
7. Click "Get External Data" on the ribbon.
8. Click "From other sources".
9. Click "From Microsoft Query".
10. Uncheck "Use the Query Wizard to create/edit queries"
11. Click "Excel Files"
12. Click "OK"
13. Browse to your current Excel file in the file list and select it
14. Check "Read Only"
15. Click "OK"
16. Close the Add Tables window
17. Click the "SQL" button
18. Paste your SQL statement in
19. Click "OK" to warning that query can't be represented graphically
20. Your data will appear, but your query was messed up due to a Microsoft bug -- http://support.microsoft.com/kb/298955 . Don't worry if what you see isn't quite right.
21. Close the window
22. At the "Import Data" pop up, insure "Table" is selected
23. Click "Properties" (still in Import Data)
24. Type a descriptive name in the "Connection name" box, like "All Accounts Query"
25. Make sure "background refresh" is selected (it should be)
26. Don't bother with a refresh interval or any other options; Excel seems to keep your QueryTable synchronized with changes immediately when the query is run against the current workbook.
27. Click the "Definition" tab
28. Click in the "Command text" box
29. Delete everything there, and re-paste your SQL statement there. This will unmangle it and make it work right :)
30. Click "OK"
31. Now back at the "Import Data" popup, make sure "Where do you want to put the data" is correct. If not, click in there and select the location for the top left corner of the table (i.e. A1) with your mouse.
32. Click "OK" to save the QueryTable.
33. If you want another column that has the row # in it from the query, allowing you to sort the QueryTable back to it's default order by sorting by that column (assuming you've re-sorted the QueryTable), you can create that column by:
a. Right click in table
b. Click "Table"
c. Click "External Data Properties"
d. Put a check mark next to "Include row numbers"
e. Click "OK"
34. Format any columns that need to be formatted differently.
35. Voila! You can peruse, sort and filter this All Accounts QueryTable as you please.

If you try this I will try to help with any questions or problems you have.

P.S. In my template/demo SQL statement, I calculated a single "Amount" column from the Credits and Debits for Bank A because Bank B doesn't have separate Credit/Debit columns, and for your merged table to be most useful, you want the same type of data (i.e. transaction amount) from every bank account to end up in the same column(s). Another solution might be to leave separate Credit and Debit columns, and assume that negative Amounts are always debit transactions and Positive amounts are always Credits (accountants have their reasons for not always doing that, but...) and have formulas for all banks that don't include separate Credit/Debit columns to split off the numbers from the Amount column based on sign (<0 or >0).

Asa
[edit: I had some stray text mixed into the SQL template...]
Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa

Many thanks, I am almost there :-)

1. Yes the formula refers to a column with the heading "This Row"

2. I get all the way down to 19 and I get...
Can not add table '~'Bank A$'~'
This is with the SQL line
FROM ~'Bank A$'~

3. if I edit it if to reply bock that backtick which I think is same as your
FROM ~Bank A$~
I can see a table/window appear with the columns in the 'Query from Excel Files' along with the 'cant rep graphically' message, but then get the message
Syntax error in query. Incomplete query clause

I have been trying a couple of other syntax's but no joy as yet but this looks very promising. Many thanks for your help.

Any suggestions greatly appreciated.

Kind regards
Giles

PS i see a note a the bottom of the backquotes (`)(the backwards single quote character) is used to put code in the reply so I am going to use the ~ for backtick. I'm in the UK so using a UK keyboard :-)

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

I don't understand your statement #3. What does "reply bock" mean?

In #2 when you say "this is with the SQL line", where you have tildes there, were they actually backticks?

Can you upload you exact SQL statement to shortText.com and provide a link so I can look at it?

Also, what version of Excel?

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi asa

Sorry for the gobbledy-gok, #3. should read...
3. If I remove the quote and just have the backtick around the worksheet name

Correct where the ~ (tildes) are is actually backticks

I have update to the SQL statement to shortText.com
http://shortText.com/vVpfbo

Fantastic little site :-)

Is the usual file extension for sql, just.sql?

Thanks
MoneyMonitors

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Hi :)

Remove UNION ALL from the end of the query. Those words should only go between each SELECT...FROM ("select statement") and are actually the glue that pulls several queries (each select statement) together.

It's possible that you should remove those single quotes I placed inside the backticks and surrounding the worksheet names.... I listed them because in my test workbook here they were required. I looked again just now, and I see they are required for some worksheets and not for others... OH, they seem to be required for worksheet names with spaces in them, and seems you CAN'T include them if there are no spaces! That's Excel's doing and has nothing to do with the SQL language. The backticks are technically optional everywhere too, but they need to enclose database/table/field names that include spaces or reserved words or symbols. It's more bullet-proof to just use them.

The table (worksheet) names as they appear to the ODBC driver can be determined for sure by noting how they appear in the Add Table window in Microsoft Query:
Get External Data / Other / Microsoft Query / Excel Files / your file
When the Add Table window appears, if there are no tables listed, click "options" and select all the options. The default options leave Excel worksheet tables hidden from the list.

If their are no quotes there, don't use them. Use the names as they appear, with just the backticks added on either side.

I actually don't know positively about file extensions. SQL queries are often stored in databases themselves, and don't have file extensions in that case. I think you are right that it is the most standard file extension used though. Only important if you have a query tool that associates with that file extension, though. Most of the work I did involved saving my queries either in the databases or as part of the source code to a program that manipulated or retrieved data from the databases.

Short text is so retro... reminds me a little of the BBS days and the "text file" repositories they had. Also, it fills a once over-looked need!

Asa

Posted 1 year ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,030
I'm going to plagarise this from the Linkedin Excelhero group

I like it as it very clear and concise in what it is doing

Thanx Ricardo Torres de Acha

= = = = = = = =
This is a non VBA option:

I usually make a Query table in a 4th Worksheet... merging each of the other three with UNION ALL.

Using the MS Query Wizard (http://support.microsoft.com/kb/211802) will get you started with the first worksheet and the connection string...

Then modify the SQL string to merge the other two... something like this works for me
```
SELECT "Reservations$".Field1,"Reservations$".Field2,"Reservations$".Field3
FROM "Reservations$" "Reservations$"

UNION ALL

SELECT "Requests$".Field1, "Requests$".Field2, "Requests$".Field3
FROM "Requests$" "Requests$"

UNION ALL

SELECT "Clients$".Field1, "Clients$".Field2, "Clients$".Field3
FROM "Clients$" "Clients$"
```
Replace all the " with a ` (the ~ key)

Every time you need to update your data... just right click the query and hit Refresh
Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Thanks Hui.

The complication I brought into it was the alias names for the fields (well, and my general verbosity!) and the unfortunate problem that MS query modifies your SQL after you paste it in. With a union query it insisted on removing all the aliases, which totally botched it, resulting in the extra steps of re-pasting the SQL in the QueryTable properties. It looked to be related to the MS kb article I linked, but the issue might be different.

The aliases are needed to merge tables with different column names.

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Hui,

I need a little clarification on the very clear and concise, as I'm slow and new to this stuff:-)

In the excel book there are 3 worksheets?
Reservations
Requests
Clients

And each as 3 fields, this would be like B1, B2 and B3.

So if field1 in on each worksheet had an unique ID# then it would combine all the info is a row?

1. How does that help in mapping columns? Or should I say what am I missing relating to columns?

2. How do you actually apply this to an excel work book? I this the SQL statement that I paste in the window when I click on the 'SQL' button in Get "External Data" ribbon -> Click "From other sources" -> Click "From Microsoft Query"?

Thanks
MoneyMonitors

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi asa

I remove the quotes around the worksheet name :-) and the UNION ALL at the end of the SQL statement.

http://shorttext.com/TQhHahc

When I look at the worksheet/tables in the 'Add tables' -> 'Options' (select all options) there are no quotes - so have removed them.

Now I am getting 'Too few parameter. Expected 4.'
Do I need to define on the 'AllTransaction' worksheet the columns that I want to be created? or is this indicating that I have messed up the column mapping in some way?

Can you explain what Hui is getting at with the example from excelhero as your reply went some what over my head.

Many thanks, once again
MoneyMonitors

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Hi Giles,
I just have a minute now, so I'll give you some quick pointers for the moment.

"Too few parameters" it thinks your query has something called parameters. Suffice it to say this query shouldn't have parameters and what it actually means is that 4 of your field (column) names don't match what it found in the table.
In Microsoft Query, use the Add Table window and just add all your worksheets to a new query. there will be box with a list of all the field names it sees. Check those against your SQL statement.

No, nothing to setup on AllTransaction.

I'll comment on your last one later.

Asa

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa

Thanks for the input, I thought it might be something like that.

I have found a simple way of rearranging the columns with a MS Query Wizard, use this to create a query and save it as a *.dqy file.
http://office.microsoft.com/en-us/excel-help/about-using-microsoft-query-to-retrieve-external-data-HP005274751.aspx?redir=0
Getting the query to reference itself (the workbook you want the data to end up in) is a brilliant twist, thanks so much for pointing this out to me :-)
But this doesn't answer the 'gluing' of tables together.

But to the SQL statement...
Checking the field (column) names that don't match what it found in the table.
"Date Entered" -> "Date entered"
And the was white space at the start of a other column name!

Now I get the message...
Incorrect column expression: 'FROM'.

I have cut the SQL down to just try to glue 2 of the tables together.

http://shorttext.com/wsG12r (grrr can't get the format correct to show code in courier)

Thanks
Giles

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Again

Just realized that I have a comma in the wrong place (at the end of the line just before the FORM that was causing the latest error.

And Fantastic - I now see a lovely concatenated table, I will have to go through the rest of the points 19+ at lunch time.

Must get on with some real paid for engineering work :-)

Kind regards
moneyMonitors

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa

I have got up to #32 where I try to click 'OK' on the 'import data' window.

I get
[Microsoft][ODBC Excel Driver] Circular reference caused bu alias 'Date' in querydefinitions's SELECT list.

I have tried to change it to lower case and added a : to change the column name but does seem to help. Any ideas?

shorttext:-
http://shortText.com/1J37a1

Almost there :-)
Many thanks
Giles

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185
Hi :)
Try changing
```
~Date~    AS "Date",
```
in the third select statement to just
```
~Date~,
```
(backticks instead of tildes of course)
You may have to do this same thing with "Amount" there. If this works then it would be my fault for suggesting the AS clause (also called an alias) when the name is not being changed. I was trying to keep my example consistent :)

edit: indicated the comma at the end of the line above
Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185
As to your question about Hui's excelhero example, I think he was just providing a short and sweet instruction. I'm better at the long explanations, and the concise one is a good complement/supplement to my long commentary

The example from excelhero is appropriate for concatenating/merging tables with identical columns, but doesn't fully address the scenario where your tables each have different column names or where you need to calculate comparable values for some tables (as with Credit Amount/Debit Amount).

For short and sweet, I would further simplify the SQL from excelhero to
```
SELECT Field1,Field2,Field3
FROM "Reservations$"

UNION ALL

SELECT Field1,Field2,Field3
FROM "Requests$"

UNION ALL

SELECT Field1,Field2,Field3
FROM "Clients$"
```
(replacing backticks for quotation marks)

I removed some superfluous info -- the example select statements were specifying the table name for each field when it's not required and unambiguous (there is only one table in each FROM clause). I removed the table alias (where the table name was listed twice in the FROM clause), as it's unneeded.

Comparing this to the example SQL I gave you, the differences are:
1. As you can see, the fields can all be specified on one line. In fact, you can change the spaces and line breaks according to your preference in most spots of an SQL statement.
2. This one doesn't use backticks around field names. That's fine unless you have spaces, symbols, or certain SQL reserved words in your field name.
3. This one (originally) specified table names for each field in the form tablename.fieldname. This is required if there is more than one field with the same name in one specific select statement, which can happen if you join multiple tables together in the from clause (for finding related data on a row-by-row basis similar to the VLOOKUP worksheet function). With one table per FROM clause it's unneeded.
4. The FROM clause specified a "table alias" in the form FROM tablename tablealias. If you specify a table alias then when you refer to the table in your query you use the alias name instead. Some people always list an alias as in this example even though the alias is the same as the table name. It's required if you have more than one table with the same name in your FROM clause (or even the same table more than once in your FROM clause), for certain SQL constructs involving "subqueries", or if you just prefer to refer to the table by a short name (one letter table aliases are common)
5. There are no "expressions" (akin to Excel formulas) in this example. I had 3 basic expressions in mine, all used instead of field names: literal text string ('Bank A' AS "Account"), Arithmetic (~Credit Amount~-~Debit Amount~ AS "Amount"), and the special literal value NULL.

This simpler example serves the same purpose my example did -- it concatenates tables together.

One other useful tidbit about the process is that Excel named ranges can be used as tables, too. If you define them they will appear in Microsoft Query along with your worksheets (without the $ on the end).

And finally a fairly useless tidbit: in my tests some useful operations don't work using the Excel ODBC driver, particularly it seems expressions don't have access to any functions other than the set/aggregate functions of COUNT(), AVG(), SUM(), MIN(), and MAX(). Other standard SQL functions allow testing if a condition is true, trimming leading/trailing spaces from strings, etc. I will play with this more.. but I think there is a pretty simple solution for when those are needed--using a different standard Microsoft driver called OLEDB. Downside is you can't use Microsoft Query to develop your query (at least fully), as Microsoft Query is ODBC-only. Due to this limitation it might not be possible in Microsoft Query for you to calculate Debits and Credits in your data given only an Amount column, as I think that would require testing for negatives and positives. The reason behind this is that ODBC expects the underlying database to handle these functions, and Excel isn't exactly a database, and simply doesn't handle these things. I think the OLEDB driver is designed to handle some of these tasks for data sources like Excel and text files.
Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi asa

Resolved the
[Microsoft][ODBC Excel Driver] Circular reference caused by alias 'Date' in query definitions's SELECT list.

Thank you, I see what was happening now :-)
Fab - I have update my sql statement to:-
http://shorttext.com/SqwMIo

The only thing I didn't get working was 2h) where you said "2h.On the left-hand side, you can include certain types of formulas to calculate a value for the QueryTable, if the value you want doesn't exist as-is in that bank's data. Notice the example formula for Bank A's "Amount" column in the template."

~Credit Amount~-~Debit Amount~ AS "Amount",

I tried putting white space around the minus sign (-) but this didn't work, but the formula I would really like is the IF in excel...
=IF(logic_test,[value_if_true],[value_if_false])
...to split out money in or out of an account

Something like...
IF(Transaction Amount>0,Transaction Amount,0) AS "MoneyOUT",
IF(Transaction Amount<0,0,-(Transaction Amount)) AS "MoneyIN"

Where do I find a list of "certain types of formulas" that can be used in QueryTables?

This if I have a credit & debit columns:-
Debit Amount AS "MoneyOUT",
Credit Amount AS "MoneyIN"

searching for "formulas in SQL statment" I came across...
http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/
But I think this is generating sql to excute in a database terminal?

Thanks
MoneyMonitors

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi asa, thanks for going over the excelhero code, very useful thx :)
I like you verbose way of explaining things as I can follow it and it is the way my mind works too.

And I very much like the 'fairly useless tidbit' as you are talking about the last thing I am trying to do "SQL functions allow testing if a condition is true", and you spotted the issue of testing for a negative number. Which could be tested with < or > if the SQL driver supports it?

What do you think?

Oh, and I need to actually get the simple minus sign working :-)

Many thanks
MoneyMonitors

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

I'm glad you got a working SQL statement.

Hmm, I was able to use arithmetic adding/subtracting with no problems here. With your current SQL statement, can you verify that the moneyin/moneyout is showing up as numeric and not as text in the resting QueryTable? Also, what kind of error did you get? you could try enclosing the expression in parentheseis (~field1~ - ~field2~) AS "fieldalias", although I did not need to do so in a test here.

There are oddities about how the Excel ODBC Driver determines what's a number, what's a date, and what's text.. it basically just guesses based on what the first few lines of data look like. I'm not really experienced with using Excel as a data source, so I'm learning as I go here too. It's possible it got the wrong type (this could go back to your CSV import actually, make sure you specified the types for the columns during import if that seems like the issue).

I'll post some links with SQL references in a bit.

As the that useless tidbit, yeah, I was guessing you were going to want to go this next step :).. and I think we'll have to using a slightly different method and bypass the ODBC driver to get the IF() type functionality. The alternative would be to calculate that in your spreadsheet first. But I like the simplicity of just doing the straight unmodified data import and having this merge table with some calculated values automatically update, with no macros or anything. We can go the macro route if need be, but I think we can get this working.

The minus sign really should be working...
Asa

edit:fixed parenthesis in paragraph 1

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

I realized that your problem with the subtraction was probably getting a blank column, with no values, right? If so that is because you can't do math with blank values, and I assume only the credit or the debit column has a value in it for each row. The solution would usually involve using one or another SQL function that doesn't seem to be supported here :(, or putting 0s in all those blank cells in Excel first.

I still want to look into OLEDB that may solve all these problems.. Will get back to you.

In the meantime, here are some SQL and (if you want to put more thought into it) ODBC references:

A tutorial of standard SQL syntax, not ODBC or Excel specific: http://www.firstsql.com/tutor.htm

It's my impression that via ODBC->Excel only the following types of queries are supported: SELECT, INSERT, CREATE TABLE and maybe DROP TABLE
Those SQL statements are covered at http://www.firstsql.com/tutor.htm

Limitations of the Excel ODBC driver (in an old 1999 document) are mentioned at http://support.microsoft.com/kb/q178717/

In fact you probably want to focus on SELECT for now, which is what your current task requires.

Expressions are covered at http://www.firstsql.com/tutor3.htm#exp
However, most of the functions do not work with ODBC->Excel, same for the "SQL Special Constructs". The datetime literals are specified a different way in ODBC that this lesson says, but at least the "Expression Operators" apply.

For expressions, also read http://www.firstsql.com/tutor2.htm at "Extended Comparisons" and "Logical Tests". I would expect the expression operators discussed there to work (BETWEEN, AND, OR, NOT, IN, LIKE, IS NULL), although with an IF() like function, those kinds of logical tests in the SELECT clause would only be useful for calculating columns of True or False values. They are discussed in that tutorial primarily in the context of the WHERE and HAVING clauses which filter results based on boolean tests like these. Oh, this reminds me that IF() could probably be emulated using subqueries.. not sure offhand exactly how. I want to get back to you on the OLEDB connection method instead, anyway, which I think is a little more full-featured than ODBC.

Highly technical ODBC and OLEDB documentation from Microsoft: http://msdn.microsoft.com/en-us/library/aa968814%28v=VS.85%29.aspx
Some of the most useful of the ODBC reference material there:
Escape sequences (used for date/time literals and function calls): http://msdn.microsoft.com/en-us/library/windows/desktop/ms715364%28v=VS.85%29.aspx
Functions are covered here, but my initial testing of a few indicates they unfortunately don't work with Excel.

Asa

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi asa

You are correct the cells in excel are 'blank' so I will try the easy workaround of adding zeros. The other issue of the IF() statement I could workaround by again a manual edit of just copying the 'credit amount' over into a new column as you tend to get far less credits than debits in my bank account (unfortunately).

Many thanks for the links and references of where to start for sql :-)
It is very much appreciated.

I need to spend some time pulling it all together this week, and document what I have learnt so I can ref back to it.

Thanks
MoneyMonitors

Posted 1 year ago #
moneymonitors

Member
Joined: Sep '11
Posts: 15

something similar to shorttext.com is pastebin.com that is more targeted at programmers I found today :-)

Kind regards
MoneyMonitors

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

Giles,
re. Pastebin -- nice!! Syntax highlighting and all. Sounds like the way to go :)

I will post back here when I have the instruction on OLEDB.

All the best,
Asa

Posted 1 year ago #

asa

Member
Joined: Sep '11
Posts: 185

Hi Giles, I'm back with the OLE DB instructions.

Yes indeed, you can do if/then constructs. It should be more full-featured in general.

OLE DB uses an underlying "provider" (database engine) to connect to your data file (in this case an Excel file). The two underlying database engines from Microsoft that support Excel files are the Jet Database Engine (the database engine used by MS Access in Office 95 through Office 2003) and the Microsoft Access Database Engine (replaces Jet in Office 2007 and 2010).

I think you would only use the Jet provider for compatibility with older systems. For one thing, it hasn't been updated and is designed for Excel 2003 and older files.

Here's a quick walkthrough in something like shorthand (for me) for adding a QueryTable linked to an Excel sheet, using the Microsoft Access Database Engine Provider. Tested in Excel 2010.

================================================================================
[Get External Data]
DATA CONNECTION WIZARD
 Other/Advanced
 [NEXT]

DATA LINK PROPERTIES
 Provider tab
  Microsoft Office 12.0 Access Database Engine OLE DB Provider
 [NEXT]

 Connection tab
 Data Source
  C:\Path\To\File\Filename.xlsx

 [Advanced tab]
  Access Permissions
   Read
   Share Deny None

 [All tab]
 Extended Properties
  Excel 12.0

 [Connection tab]
  [Test Connection]
   if success [OK]

DATA CONNECTION WIZARD
 [uncheck "Connect to a specific table"]
 [Finish]
  if prompted to replace data connection file [YES]

SELECT TABLE
 irrelevant, hit [OK]

IMPORT DATA
 [Properties]

CONNECTION PROPERTIES
 Connection Name
  MergeQuery_Or_Whatever_You_Want
   (QueryTable will be named "Table_ConnectionName")

 ::::::Optional::::::::::::::::::::::::::::::::::::::::
 ::: can skip to [ok] and edit query after to avoid :::
 ::: abort if sql statement error                   :::
 ......................................................
 [Definition tab]
  Command Type
   SQL
  Command Text
   SELECT * FROM [Sheet Name$]
 ::::::::::::::::::::::::::::::::::::::::::::::::::::::
 [OK]

THE CONNECTION IN THIS WORKBOOK...PROCEED? warning
 [YES]

IMPORT DATA
 [OK]

::::::Optional::::::
Right-click table, TABLE>External Data Properties>select options
Right-click table, TABLE>Edit Query>modify your SQL statement

Notes:

The Office Access Database Engine 2010 (download) can be installed freely on older systems that don't have it.

DATA LINK PROPERTIES/Extended Properties specifies the version of the Excel file. Excel 8.0 is Excel 97-2003 format. Excel 12.0 is Excel 2007-2011.

At DATA LINK PROPERTIES/Provider you could select the Microsoft Jet 4.0 Provider on systems that don't have the Access Database Engine installed. Maximum version for the Extended Properties is Excel 8.0 (although it read an .xlsx file for me).

================================================================================

It's not any more involved than using ODBC outside of the fact that you can't use Microsoft Query to help you design your query.

I did find an Excel Add In for this task that I haven't tried yet, but looks interesting, called SQL Drill. Free/donation-ware. While I'm distracted from the particulars of your database task, I'll also mention another Add-In, for Excel and for Word, that I discovered in my research -- DBAddin -- which is free/open-source and adds all kinds of database functionality.

Back to your project:

Using OLE DB to connect to your Excel sheets, your flavor of SQL has changed. It is now, I believe, essentially the same flavor used by Microsoft Access and Microsoft SQL Server (so any documentation you read about that syntax should be pretty relevant).

Syntax highlights:
1. No more backticks. (Optionally) Enclose "database object" names, such as your table/sheet name and field names in brackets instead, like so:[table].[sheet$]
2. No special quoting for object names that have spaces.[Bank A$] will do fine. In ODBC it was ~'Bank A$'~ (backticks for tildes).
3. Alias names can go in brackets just like the original table/field names. Never put them in quotes (like we did with ODBC).
4. You can use either single or double quotes to delimit literal strings of text, but the single quote is ANSI standard SQL. If you need to have the delimiter character as part of your string, use two of them ('I''d love to.' becomes the text "I'd love to.")
5. Specify date and time literals using # as a delimiter. Dates should always be in m/d/y format, and times h:mm:ss. These are all acceptable: #5/16/2008# #5/16/08# #9:06:00# #21:06:30# #5/16/2008 9:06:00#
6. Expanded SQL syntax and capabilities.
7. Supported functions include:

Many/most Visual Basic for Applications functions

Iif(boolean,value-if-true,value-if-false)
  Switch(boolean,value-if-true[,boolean,value-if-true[,...]])
  Choose(index,value if 1[,value if 2[,...]])
  IsNull(value)            returns boolean  http://bytes.com/topic/access/insights/716197-what-null
  AVG(number)              returns aggregate of all/grouped records
  COUNT(field or * for records/rows)  returns aggregate number of non-nulls
  FIRST(value)             returns value for first row
  LAST(value)              returns value for last row
  MAX(number)              returns aggregate of all/grouped records
  MIN(number)              returns aggregate of all/grouped records
  SUM(number)              returns aggregate of all/grouped records
  Year(date)
  Month(date)
  Day(date)
  Hour(time/date-time)
  Minute(time/date-time)
  Second(time/date-time)
  Date()                   returns today's date
  Time()                   returns current time
  Now()                    returns current date and time
  Weekday(date)
  Format(date-time,format)
  DateAdd(interval,number,date)
  DateDiff(interval,date1,date2)
  DatePart(interval,date-time)
  DateSerial(year,month,day)
  UCase(text string)
  LCase(text string)
  Mid(text string,start position,length)
  Left(text string,length)
  Right(text string,length)
  Round(number,number of decimal places)
  InStr(text string to search,text string to find)    other syntaxes available too
  InstrRev(text string to search,text string to find)
  Int(number)
  Fix(number)
  Sgn(number)              returns 1=positive, 0=zero, -1=negative

8. Supported expression operators include:

expr IS [NOT] NULL                  in excel, null means an empty cell
  expr AND expr                       both
  expr OR expr                        either
  NOT (expr)                          expr is false
  expr XOR expr                       one and only one expr is true
  expr EQV expr                       both exprs true or both false
  expr IMP expr                       false if 1st expr true but not 2nd, else true
  expr [NOT] IN (expr1[,expr2[,...]]) among expr1, expr2...
  expression [NOT] IN (query)         among query results
  ANY  SOME  EXISTS                   these are three more subquery operators
  expr [NOT] LIKE expr                comparison - wildcards ok in 2nd expr
  =  <>  <  >  >=  <=                 comparison
  /  *  +  -                          arithmetic
  +                                   concatenate text strings
  &                                   convert to text (and null to '') and concatenate

Posted 1 year ago #

moneymonitors

Member
Joined: Sep '11
Posts: 15

Hi Asa

Thanks for the extra info, I will have a look through it this week :-)
Many thanks
Giles

Posted 1 year ago #