{"id":16968,"date":"2020-03-26T03:57:38","date_gmt":"2020-03-26T07:57:38","guid":{"rendered":"https:\/\/bangla.salearningschool.com\/recent-posts\/python-read-amex-anerican-express-canada-mastercard-pdf-statement-transaction-data-into-csv-file\/"},"modified":"2020-03-28T13:00:55","modified_gmt":"2020-03-28T17:00:55","slug":"python-read-amex-anerican-express-canada-mastercard-pdf-statement-transaction-data-into-csv-file","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=16968","title":{"rendered":"Python: Read Amex (American Express) Canada: Mastercard PDF Statement Transaction Data into CSV file"},"content":{"rendered":"\n

Python: Read Amex (American Express) Canada: PDF Statement Transaction Data into CSV file<\/a><\/h3>\n

By Sayed Ahmed<\/p>\n

In general, you will get CSV files from your Amex card; However, sometimes you need to download in every 3 or 6 months. Otherwise, you might only get\/request pdf files for transactions (depends on the card).<\/p>\n

Sure, you can use tools such as Adobe Acrobat (Paid) to convert PDF statements to csv\/xls files. This is a simple approach that you might be able to use when you do not have access to any such tools.<\/p>\n

Steps:<\/strong><\/p>\n

Download the statements.<\/p>\n

Copy the text (only the transactions) into a text file<\/p>\n

The use the code provided here or similar to read the text file and create a CSV file<\/p>\n

Note:<\/p>\n

You can use PDF export software to open pdf file and copy\/paste. Acrobat did not give me a good format.<\/p>\n

Copy and paste using PDF Export (to open pdf), resulted 5 lines for each transaction. if any transaction (credit adjustment) comes to be 4 lines or 6 lines (foreign transaction), make them 5 lines.<\/p>\n

Related<\/strong>:<\/p>\n

Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file<\/strong>
By Sayed Ahmed<\/em>medium.com<\/a><\/div>\n
Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time<\/strong>
By Sayed Ahmed<\/em>medium.com<\/a><\/div>\n
Python: Generate Expense Report from RBC MasterCard Transaction Data<\/strong>
For the context read the following two first<\/em>medium.com<\/a><\/div>\n
\u00a0<\/div>\n
# code will generate both individual csv output files; also a merged file that combines all data into one csv file<\/pre>\n
#!\/usr\/bin\/env python
# coding: utf-8<\/pre>\n
# In[1]:<\/pre>\n
import os;
import glob;
import pandas as pd;
os.chdir(\u2018.\/\u2019);<\/pre>\n
# In[2]:<\/pre>\n
extension = \u2018csv\u2019;
all_filenames = [i for i in glob.glob(\u2018*.{}\u2019.format(extension))];<\/pre>\n
# In[3]:<\/pre>\n
sorted(list(all_filenames))<\/pre>\n
# In[4]:<\/pre>\n
os.getcwd();<\/pre>\n
# In[5]:<\/pre>\n
# test : Check the data for each csv files to be combined.
# do the data align well with each other
row_total_count = 0
for i in range(0, len(all_filenames)):
 j = i + 1
 for f in all_filenames[i:j]:
 file = f
 print(file)
 df_s = pd.read_csv(f)
 print(df_s.shape, f)
 #print(f)
 row_total_count += df_s.shape[0]
 print(df_s.head(1))<\/pre>\n
#row_total_count
#df_s.head()<\/pre>\n
# In[6]:<\/pre>\n
# keep track of total rows in all files so that you can compare the shape with the final combined data file
row_total_count = 0
for f in all_filenames:
 file = f
 print(file)
 df_s = pd.read_csv(f, header=None)
 print(df_s.shape, f)
 #print(f)
 row_total_count += df_s.shape[0]
 #print(df_s.head())<\/pre>\n
row_total_count
#df_s.head()<\/pre>\n
# In[7]:<\/pre>\n
#combine all files in the list, axis =0 i.e. one after another
combined_csv = pd.concat([pd.read_csv(f, header=None) for f in all_filenames], axis=0)
# combined_csv.sort_values(\u201cModel Code\u201d, inplace = True) 
# dropping ALL duplicte values 
#combined_csv.drop_duplicates(subset =\u201dModel Code\u201d, keep = False, inplace = True)<\/pre>\n
#export to csv
combined_csv.to_csv( \u201camex_gold_card_data_combined\u201d, index=False, encoding=\u2019utf-8-sig\u2019)<\/pre>\n
# In[8]:<\/pre>\n
combined_csv.shape<\/pre>\n
# In[9]:<\/pre>\n
row_total_count == combined_csv.shape[0]<\/pre>\n
# In[11]:<\/pre>\n
df = pd.read_csv(\u2018amex_gold_card_data_combined\u2019)
df.head(3)<\/pre>\n
# In[12]:<\/pre>\n
df.shape<\/pre>\n
# In[ ]:<\/pre>\n
# In[ ]:<\/pre>\n
Note: Older short-notes from this site are posted on Medium: <\/em><\/strong>https:\/\/medium.com\/@SayedAhmedCanada<\/a><\/p>\n
***\u00a0. *** ***\u00a0. ***\u00a0. ***\u00a0. ***<\/p>\n
Sayed Ahmed<\/em><\/strong><\/p>\n
BSc. Eng. in Comp. Sc. & Eng. (BUET)<\/em><\/strong><\/p>\n
MSc. in Comp. Sc. (U of Manitoba, Canada)<\/em><\/strong><\/p>\n
MSc. in Data Science and Analytics (Ryerson University, Canada)<\/em><\/strong><\/p>\n
Linkedin<\/em><\/strong>: <\/em>https:\/\/ca.linkedin.com\/in\/sayedjustetc<\/em><\/a><\/p>\n
Blog<\/em><\/strong>: <\/em>http:\/\/Bangla.SaLearningSchool.com<\/em><\/a>, <\/em>http:\/\/SitesTree.com<\/em><\/a><\/p>\n
Training Courses: <\/em><\/strong>http:\/\/Training.SitesTree.com<\/em><\/a><\/p>\n
Facebook Groups\/Forums to discuss (Q & A):<\/em><\/strong><\/p>\n
https:\/\/www.facebook.com\/banglasalearningschool<\/a><\/p>\n
Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others. <\/em>http:\/\/sitestree.com\/training\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[182],"tags":[],"class_list":["post-16968","post","type-post","status-publish","format-standard","hentry","category---blog","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":16964,"url":"http:\/\/bangla.sitestree.com\/?p=16964","url_meta":{"origin":16968,"position":0},"title":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time","author":"Sayed","date":"March 21, 2020","format":false,"excerpt":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time By Sayed Ahmed Merge multiple transaction files into one. This is an extension to the article: Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file By Sayed Ahmedmedium.com The Code for Merging\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":16962,"url":"http:\/\/bangla.sitestree.com\/?p=16962","url_meta":{"origin":16968,"position":1},"title":"Python: Read RBC Canada: Mastercard Statement Data into CSV file","author":"Sayed","date":"March 19, 2020","format":false,"excerpt":"Sure, you can use tools such as Adobe Acrobat to convert PDF statements to csv\/xls files. This is a simple approach that you might be able to use when you do not have access to any such tools. How can this help? If you were trying to calculate based on\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":17001,"url":"http:\/\/bangla.sitestree.com\/?p=17001","url_meta":{"origin":16968,"position":2},"title":"Python: Ecommerce: Part\u200a\u2014\u200a1: Merge Multiple Supplier Data Files into One File","author":"Sayed","date":"April 18, 2020","format":false,"excerpt":"Python: Ecommerce: Part\u200a\u2014\u200a1: Merge Multiple Supplier Data Files into One File Section: Merge multiple Supplier Data Files All code in one block #!\/usr\/bin\/env python # coding: utf-8 # # Section: Merge multiple Supplier Data Files # # In[1]: # if there is a need to merge multiple files \u2014 use\u2026","rel":"","context":"In "Build Ecommerce Software"","block_context":{"text":"Build Ecommerce Software","link":"http:\/\/bangla.sitestree.com\/?cat=1912"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":17002,"url":"http:\/\/bangla.sitestree.com\/?p=17002","url_meta":{"origin":16968,"position":3},"title":"Python: Ecommerce: Part \u2014 1: Merge Multiple Supplier Data Files into One File","author":"Sayed","date":"April 19, 2020","format":false,"excerpt":"Section: Merge multiple Supplier Data Files All code in one block #!\/usr\/bin\/env python # coding: utf-8# # Section: Merge multiple Supplier Data Files ## In[1]:# if there is a need to merge multiple files \u2014 use this block import os; import glob; import pandas as pd;# supplier data files\/feeds are\u2026","rel":"","context":"In "Build Ecommerce Software"","block_context":{"text":"Build Ecommerce Software","link":"http:\/\/bangla.sitestree.com\/?cat=1912"},"img":{"alt_text":"8112223 Canada Inc. (Justetc)","src":"https:\/\/miro.medium.com\/fit\/c\/80\/80\/0*P_esmjKoJnHlNjFX","width":350,"height":200},"classes":[]},{"id":16965,"url":"http:\/\/bangla.sitestree.com\/?p=16965","url_meta":{"origin":16968,"position":4},"title":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time","author":"Sayed","date":"March 21, 2020","format":false,"excerpt":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":16966,"url":"http:\/\/bangla.sitestree.com\/?p=16966","url_meta":{"origin":16968,"position":5},"title":"Python: Generate Expense Report from RBC MasterCard Transaction Data","author":"Sayed","date":"March 21, 2020","format":false,"excerpt":"Python: Generate Expense Report from RBC MasterCard Transaction Data For the context read the following two first Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file By Sayed Ahmed medium.com Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time By Sayed\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/16968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16968"}],"version-history":[{"count":1,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/16968\/revisions"}],"predecessor-version":[{"id":16971,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/16968\/revisions\/16971"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16968"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}