0

I have been using Python to perform a SQL query and retrieve data from the previous date every day since June and load it into a Postgres database which is local. Each date has about 2 million rows and 75 columns or so, so it is fairly large and it takes a while to pull.

Can someone give me an example bcp code which will generate a CSV (comma) file? How much data would I be able to pull at once or can I loop through each calendar date in the SELECT statement? I am hoping this will be a faster way to kind of replicate the tables I am interested in.

My Python code essentially boils down to SELECT * FROM table_name WHERE report_date = {}. The report date is typically yesterday.

Here is my final working result if anyone else every stumbles upon this. The subprocess.run executes the string which contains the BCP query. In the code below, there are a few variables such as the table_name, database name, and query date since I am looping through different tables and databases on different dates.

path = 'C:/raw/'
filename = '{}_{}.csv'.format(database, select_database(database)[table_int])
query = '"SELECT * FROM {}.dbo.{} WHERE row_date = \'{}\'"'.format(database, select_database(database)[table_int], query_date)
command = 'BCP {} queryout \"{}\" -t, -c -S "server_name" -T'.format(query, os.path.join(path, filename))
table_name = 'raw_{}_{}'.format(select_database(database)[table_int], database)
logger.info(command)
subprocess.run(command)
1

You can either use a query, as needed to BCP out each month, or BCP out the entire table.

bcp "SELECT * FROM table_name WHERE report_date = {}" queryout <FilePath And Filename> -c -t, -T -S "<servername>"

or

bcp <database>.<schema>.<tablename> out <filepath and filename> -c -t, -T -S "<Servername>"

There are multiple switches available for BCP, in this case the following ones are used:

  • -c - uses a character datatype using char as the storage type and tab as the seperator
  • -t, - replaces the tab separator with a comma
  • -T - uses trusted (Windows) authentication

There are many more switches available (MSDN has a full reference) so you might need to adjust things to get what you need. If all else fails you could create format files to ensure that you get the data in the exact method you need it.

| improve this answer | |
  • This seems to be working. There are no headers but I don't think that is an issue (I think that COPY FROM in postgres just appends the data anyway and I am not adding or moving columns in different orders anyway). Is there a way to schedule this task to run and download the previous day's data? I'd like for the report_date = {} to be dynamic so I can throw in some loops etc – trench Oct 11 '16 at 15:20
  • You could do that using Powershell to send along the BCP command. That's the simplest way that I know of. Within the PS you would have date as a variable set dynamically. – Nic Oct 11 '16 at 16:09
  • I ended up finding a way in Python. Thanks for providing me with what I needed to finish everything. – trench Oct 11 '16 at 18:39
1

There are plenty of examples on the internet - search for "bcp comma delimited" Also, refer to the BCP syntax here.

This should get you started:

bcp <YourDatabase>.<YourSchema>.<YourTable> out C:\OutData.dat -o C:\OutData.log -S . -T -c -a 65535' -t, -b100000
  • BCP - the command
  • the fully qualified object you're exporting from Sql Server (this could also be a view)
  • out - the path and file name of the exported data
  • -o - the output log information about number of rows extracted
  • -S - the sql server instance name (default here)
  • -T - use a trusted connection
  • -c - export the data in character format
  • -a - packet size of 65535
  • -t - your column delimiter (comma in this case)
  • -b - batch size (for performance)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.