Is there a way to; easily, merge data into a CSV (or Excel) file without having to edit each file individually?2019 Community Moderator ElectionSpatial clustering based on response to inputs and building a reduced modelWhat is the most used format to save data with type informationHow to deal with large data setsBalance data using different criteriaImporting Excel format data into R/R Studio and using glmnet package?Is the Apriori algorithm suitable for database tuples?
How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?
Type 1 Error & Type 2 Error's pregnancy test analogy: is it legit?
Methods for deciding between [odd number] players
Is it possible to make sharp wind that can cut stuff from afar?
How can bays and straits be determined in a procedurally generated map?
Can I make popcorn with any corn?
Can a German sentence have two subjects?
How does strength of boric acid solution increase in presence of salicylic acid?
Animated Series: Alien black spider robot crashes on Earth
Is it possible to do 50 km distance without any previous training?
Why was the small council so happy for Tyrion to become the Master of Coin?
Japan - Plan around max visa duration
Is it possible to rebuild the bike frame (to make it lighter) by welding aluminum tubes
How to write a macro that is braces sensitive?
Pronouncing Dictionary.com's W.O.D "vade mecum" in English
DOS, create pipe for stdin/stdout of command.com(or 4dos.com) in C or Batch?
Why are weather verbs 曇る and 晴れる treated differently in this sentence?
Email Account under attack (really) - anything I can do?
How to test if a transaction is standard without spending real money?
What would happen to a modern skyscraper if it rains micro blackholes?
Dividing by a power
N.B. ligature in Latex
How do I create uniquely male characters?
Is the month field really deprecated?
Is there a way to; easily, merge data into a CSV (or Excel) file without having to edit each file individually?
2019 Community Moderator ElectionSpatial clustering based on response to inputs and building a reduced modelWhat is the most used format to save data with type informationHow to deal with large data setsBalance data using different criteriaImporting Excel format data into R/R Studio and using glmnet package?Is the Apriori algorithm suitable for database tuples?
$begingroup$
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!
data
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
$endgroup$
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
$begingroup$
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!
data
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
$endgroup$
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59
add a comment |
$begingroup$
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!
data
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
$endgroup$
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!
data
data
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
asked Oct 8 '18 at 19:54
Maverick924Maverick924
1
1
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59
add a comment |
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59
add a comment |
2 Answers
2
active
oldest
votes
$begingroup$
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).
Excel interface
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!).
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
$endgroup$
add a comment |
$begingroup$
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.
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
$endgroup$
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
);
);
, "mathjax-editing");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "557"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f39380%2fis-there-a-way-to-easily-merge-data-into-a-csv-or-excel-file-without-having%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
$begingroup$
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).
Excel interface
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!).
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
$endgroup$
add a comment |
$begingroup$
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).
Excel interface
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!).
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
$endgroup$
add a comment |
$begingroup$
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).
Excel interface
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!).
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
$endgroup$
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).
Excel interface
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!).
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
answered Oct 8 '18 at 20:11
n1k31t4n1k31t4
6,4912421
6,4912421
add a comment |
add a comment |
$begingroup$
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.
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
$endgroup$
add a comment |
$begingroup$
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.
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
$endgroup$
add a comment |
$begingroup$
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.
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
$endgroup$
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.
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
answered Oct 8 '18 at 21:03
Bashar HaddadBashar Haddad
1,2621313
1,2621313
add a comment |
add a comment |
Thanks for contributing an answer to Data Science Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f39380%2fis-there-a-way-to-easily-merge-data-into-a-csv-or-excel-file-without-having%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
$begingroup$
Look into using the pandas python library
$endgroup$
– RossDeVito
Oct 8 '18 at 19:59