3 ms·

You touched on something I've always wondered about: what's up with all those different transaction data formats? What's wrong with simple CSv files?

joshuaheard 11y ago
Quicken switched to its own proprietary format for which it charged banks a license fee.
sizzzzlerz 11y ago
Consumer lock-in. If its proprietary, so much the better.
paulryanrogers 11y ago
CSV is strictly a single table, so it cannot normalize the data. You can end up with a lot of them or a lot of columns and empty cells.
- jessaustin 11y ago
  I'm drawing a blank on the normalization that would be relevant for transactions. Do you mean something like "the sub-sub-account determines the sub-account which determines the account"?
  - orionblastar 11y ago
    https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization You find that database armatures usually store everything in one table. The professionals break things up into different tables using normalization. For example you usually have a table with names in it and an ID, and then you have a transaction table that has the ID in it plus the transaction with a foreign key going back to the table with names in it. Could be names of each account or checking or savings account with an ID assigned to them. Thing is you could be downloading more than one account and you don't want them mixed up into one CSV file. You want them separate in normalized tables. Now you could just have a CSV file with Account ID in it and then have a separate CSV file with the name of the account with the ID in it. But that just gets confusing. What if you are a business and have several accounts you want to download and process? You don't want them all mixed up together. Yeah there could be sub-accounts as well that have an ID as a foreign key and then another Foreign key to the main account or sub-sub account etc. When you get into accounting and you have sub accounts and sub-sub accounts to track spending you have to have a way to figure out how they work. Here is an example of tables with sub accounts: http://www.utteraccess.com/forum/lofiversion/index.php/t1855469.html http://www.utteraccess.com/forum/lofiversion/index.php/t1855...
orionblastar 11y ago
CSV files are good, but sometimes they contain blank data and spaces that mess up importing them. I used to work as a federal contractor importing text files into databases by downloading them via FTP or Web Sites on a federal Intranet. I would import them into Oracle, SQL Server, MS-Access, Clipper, etc and sometimes had to write a custom program to import the files. I would work in a Unix environment as well and had to run AWK and SED to remove blank spaces and other problems that would cause invalid use of null errors in a database that the column couldn't be null. Sometimes the CSV file didn't follow the database validation rules, like using text in a number field, etc. We also had fixed width files. Stuff from an IBM Mainframe that was in EPISODIC that had to be converted to ASCII. Then when it converted some of the characters in the file had to be edited because they were illegal control characters that had something to do with DASD JCL/JECL control codes. But a CSV file is basically one database table with a bunch of columns in it that you import, separate the data with commas.

joshuaheard 11y ago

Quicken switched to its own proprietary format for which it charged banks a license fee.

sizzzzlerz 11y ago

Consumer lock-in. If its proprietary, so much the better.

paulryanrogers 11y ago

CSV is strictly a single table, so it cannot normalize the data. You can end up with a lot of them or a lot of columns and empty cells.

jessaustin 11y ago
I'm drawing a blank on the normalization that would be relevant for transactions. Do you mean something like "the sub-sub-account determines the sub-account which determines the account"?
- orionblastar 11y ago
  https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization You find that database armatures usually store everything in one table. The professionals break things up into different tables using normalization. For example you usually have a table with names in it and an ID, and then you have a transaction table that has the ID in it plus the transaction with a foreign key going back to the table with names in it. Could be names of each account or checking or savings account with an ID assigned to them. Thing is you could be downloading more than one account and you don't want them mixed up into one CSV file. You want them separate in normalized tables. Now you could just have a CSV file with Account ID in it and then have a separate CSV file with the name of the account with the ID in it. But that just gets confusing. What if you are a business and have several accounts you want to download and process? You don't want them all mixed up together. Yeah there could be sub-accounts as well that have an ID as a foreign key and then another Foreign key to the main account or sub-sub account etc. When you get into accounting and you have sub accounts and sub-sub accounts to track spending you have to have a way to figure out how they work. Here is an example of tables with sub accounts: http://www.utteraccess.com/forum/lofiversion/index.php/t1855469.html http://www.utteraccess.com/forum/lofiversion/index.php/t1855...

orionblastar 11y ago

CSV files are good, but sometimes they contain blank data and spaces that mess up importing them. I used to work as a federal contractor importing text files into databases by downloading them via FTP or Web Sites on a federal Intranet. I would import them into Oracle, SQL Server, MS-Access, Clipper, etc and sometimes had to write a custom program to import the files. I would work in a Unix environment as well and had to run AWK and SED to remove blank spaces and other problems that would cause invalid use of null errors in a database that the column couldn't be null. Sometimes the CSV file didn't follow the database validation rules, like using text in a number field, etc. We also had fixed width files. Stuff from an IBM Mainframe that was in EPISODIC that had to be converted to ASCII. Then when it converted some of the characters in the file had to be edited because they were illegal control characters that had something to do with DASD JCL/JECL control codes. But a CSV file is basically one database table with a bunch of columns in it that you import, separate the data with commas.