I have several marketing files that I need to download on a weekly basis. Let’s say 15 different files. Each file contains the same field names: field 1, field 2, field 3, field 4, field 5, field 6, field 7, field 8, field 9. The data contained in the fields are different and specific to each file. Each file can contain 10 to 1000s records. What I need to do, is add 2 additional fields to each record in each file. These fields; field A and field B will contain the same info for every record in each specific file. Each file will contain different info to add. File 1 Need to add Field A and Field B to every record. File 2 Need to add Field C and Field D to every record. File 3 Need to add Field E and Field F to every record. I can edit each file in Excel, add the columns, name the columns, add the 2 fields to the first row and then copy and paste to all the other rows. Is there a way to specify the file, field 1 and field 2 in order to add the info more efficiently and quickly? Thank you!
You can either load the excel files directly into Python, e.g. using the pandas package, or you can use a package that hits the data where it lives i.e. a package that directly modifies the Excel files.
Python and Pandas
import pandas as pd
data = pd.read_Excel('/path/to/your/file)
Data is basically a table that will look like the Excel itself. You can add fields (usually referred to as columns) like so:
data['new field'] = new_data
new_data will contain all values for all rows in the table, e.g. as a list:
new_data = [12, 24, 13, 66, 72, 45] # assuming there are 6 rows (a.k.a. samples)
You will have to alter the code for each of your files. You will also need to think about how to get your new data into that list (or a similar construction).
Caveat: this way might have problems if you have some very specific formatting or special Excel features in use (more than just conditional formatting - think dropdown lists and hidden/folded rows and columns).
There are a few good packages that will allow you to speak directly with the excel file and make use of things such as sheets/tabs and references to cells e.g.
A1 is the top left cell in Excel.
Have a look at the XlsWriter package or the python-excel package. Both let you access the fill and add/remove data as you please. I have used both for simple tasks and they both got the job done quite nicely.
Follow those links to see some nice examples of how to manipulate Excel programmatically.
It is worth mentioning that the first option above, using something like Pandas, will offer a lot more functionality and would likely not present a steeper learning curve (in my opinion!).
merging csv files is possible:
make sure that you have all the csv files in the same folder.
open that folder in a cmd window.
use the following command: copy *.csv combined.csv
this command will copy all the csv file into a single file called combined.csv
If you are looking for more advanced solutions, you can search for free packages and tools to do these things as proposed in previous answer.