How to export entire bitcoin blockchain into database

[Sinsix]

Hello. I wonder if it's possible to export entire blockchain to this date or specific date into some kind of database for example excel. Adresses, transactions, balance etc. Thank you for your answer.

[1713859609]

1713859609

[1713859609]

1713859609

[1713859609]

1713859609

[jackg]

I'm pretty sure you can use the api from blockchain.com to get a copy of the blocks in csv (but it might not have native segwit transactions).

There was a user here who was doing data dumps recently you might want to do a Google search for it. It's worth noting you're not going to be able to open it with excel since it'll be 250gb+ in total after conversion to human readable formats... A few text editors won't have a problem with it though (especially the old ones that used to open files in segments)...

https://github.com/organofcorti/bitcoin-blockchain-data?files=1 as an example from a quick Google search, this is written in R (I didn't get to have a look through it but R is C based).

[DannyHamilton]

Hello. I wonder if it's possible to export entire blockchain to this date or specific date into some kind of database for example excel. Adresses, transactions, balance etc. Thank you for your answer.

The entire blockchain is already in a database.

Excel makes a better spreadsheet program than database program.

[BitMaxz]

Check this one and maybe this is the one you are looking for. You can get Bitcoin Blocks, Transactions, Inputs, Outputs, Addresses on the specific date.

Just extract them after you download the extension file after you extract is .tsv where you can open it with Microsoft Excel.

Here's the link: https://blockchair.com/dumps

You can also use their API to retrieve them
Blockchair API:: https://github.com/Blockchair/Blockchair.Support/blob/master/API.md

[DaCryptoRaccoon]

Here is my easy to follow guide on how to setup bitcoin abe in localhost and scrape the chain to a MySQL data base.
If you get stuck or need help just ask.

Code:

 SETUP CHAIN SCRAPE TO MYSQL DB

				  :Required:

Ubuntu, MySQL Python, MySQL Client, MySQL Server, Python, Python2, Blockchain,

Downloading the bitcoin blockchain two options. 

1. Download & Install bitcoin core wallet and sync with network


################################################################################

				Install Python MySQL


$ sudo apt-get install python-mysqldb

			    Install MySQL Client & Server

$ sudo apt-get install mysql-client mysql-server

To configure the MySQL instance with InnoDB engine support. 
If you installed with Debian/Ubuntu then InnoDB is enabled by default. 
To check for InnoDB support, issue "SHOW ENGINES" and look in the output
for "InnoDB" with "YES" next to it.  If "skip-innodb" appears in the server
configuration (my.cnf or my.ini) then remove it and restart the server.

################################################################################

				SETUP MYSQL

Log into MySQL as root (i.e: mysql -u root) and give commands.
		Don't forget to change the PASSWORD

    create database abe;
    CREATE USER 'abe'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD_HERE';
    grant all on abe.* to abe;

The above will

1.  Create a database called abe
2.  Create a user called abe for localhost with the password you enter above
3.  Will grant all permisions to abe for abe user


################################################################################

			DOWNLOAD AND INSTALL BITCOIN-ABE

This will allow you to check getrecievedbyaddress calls quickly without being rate limited by online API checkers.

$ git clone https://github.com/bitcoin-abe/bitcoin-abe.git
cd bitcoin-abe
python setup.py install

once installed open file abe.conf
(Included is a copy of the file with section uncommented all you need to do is edit the password and DB info if you changed it)
If You use the original config file from abe you will need to follow steps below and uncomment and modify lines.

################################################################################

				abe.conf GUIDE

uncomment lines 33 & 34 and update line 34 with your password / user and db info for MySQL

# MySQL example; see also README-MYSQL.txt:
dbtype MySQLdb
connect-args {"user":"abe","db":"abe","passwd":"YOUR_PASSWORD_HERE"}

Uncomment lines 57 & 58

# Specify port and/or host to serve HTTP instead of FastCGI:
port 2750
host localhost

This will open port 2750 on localhost to allow the wallet checker to connect to the API

Next scroll down to the section where datadir is listed, you must specifiy the path to the blockchain directory this path should contain the bitcoin.conf & blocks & chainstate folders.

Uncomment Lines 136 / 137 / 139 / 140
**Do not uncomment line 138 "loader"**  as we are not caling via RPC rather via the API and http request to gettecievedbyaddress + addy

datadir += [{
        "dirname": "/path/to/blockchain",
#        "loader": "rpc",    # See the comments for default-loader below.
        "chain": "Bitcoin"
      }]

################################################################################

				START THE IMPORT

You can now start to load the data to abe from the blockchain run the following command from the bitcoin-abe-master dir

$ python -m Abe.abe --config abe.conf --commit-bytes 100000 --no-serve --datadir /path/to/blockchain

You should see output like:

	block_tx 1 1
    	block_tx 2 2
	block_tx 3 3
    	block_tx 4 4
	block_tx 5 5
    	block_tx 6 6
	block_tx 7 7
    	block_tx 8 8

( THIS PROCESS WILL TAKE A VERY LONG TIME POSSIBLY 2-6 DAYS DEPENDING ON YOUR SYSTEM SPEC )

You can stop the process at any time and re-start the load will continue from the last block loaded after checking the chain.
The data that is loaded can also be used to search while data in importing but only up to the latest block number processed.

Next step can be done now if you want to scan small search space or wait for full import of data before continuing.


################################################################################

				CHECK MYSQL

Go back to MySQL and log in and type

mysql> use abe;

mysql> show tables;

Output will show.

+-----------------+
| Tables_in_abe   |
+-----------------+
| abe_lock        |
| block           |
| block_next      |
| block_seq       |
| block_tx        |
| block_txin      |
| chain           |
| chain_candidate |
| chain_seq       |
| chain_summary   |
| configvar       |
| datadir         |
| datadir_seq     |
| multisig_pubkey |
| orphan_block    |
| pubkey          |
| pubkey_seq      |
| tx              |
| tx_seq          |
| txin            |
| txin_detail     |
| txin_seq        |
| txout           |
| txout_approx    |
| txout_detail    |
| txout_seq       |
| unlinked_tx     |
| unlinked_txin   |
+-----------------+



################################################################################


			LAUNCH BITCOIN ABE (LOCALHOST)

If you have this working you can now launch ABE from the bitcoin-abe-master dir to view the api in localhost.

$ python -m Abe.abe --config abe.conf

Open browser and navagate to: localhost:2750 

You should now see the ABE block explorer running.


################################################################################

[Sinsix]

I would like to have a excel or csv database with adresses and transactions between them.

EDIT: Actually I have found a way how to do it but first of all I need to parse blk.dat from bitcoin core to JSON then JSON to csv but can't find proper JSON parser for win10

[DannyHamilton]

I would like to have a excel or csv database with adresses and transactions between them.

Bitcoins don't always go to addresses.
Bitcoin doesn't have a useful concept that can be called a SENDING addresses.
It is possible for bitcoins that were received in 3 different transaction outputs to all be spent together as inputs for a single transaction that has less than (or more than) 3 outputs.  How would you determine which bitcoins when where?

Your request sounds simple when you state it the way you did, but when you look at the details of how bitcoin works, it becomes clear that you are going to have to figure out how to deal with a lot of edge cases before you can create the database you are asking for.

[PrimeNumber7]

Excel spreadsheets can have a maximum of approximately 1,000,000 rows, and there are approximately 600k blocks, 465mm transactions and 1.2b outputs.  As such you won’t be able to put the entire blockchain in an excel spreadsheet.

If you have enough RAM, you can put data from the blockchain into a Pandas (or you could use Spark if you have access to a cluster) DataFrame and do analysis via the DF. Otherwise you can put it into a SQL database.