Need a batch script to copy data from A.csv to B.xls
I'm a newbie to batch scripting and need help.
I want to copy the contents of a csv file A.csv to the first worksheet named "GenData" in a existing XLS file B.xls.
B.xls contains some macros in it's second worksheet for graph generation from the data copied to it's first worksheet.
Both the files A.csv and B.xls are in the same folder.
Also, I want to repeat this for about 40 folders containing these two files.
C:DesktopAbbyFolder1A.csv
C:DesktopAbbyFolder40A.csv
PS: I do not want to convert the A.csv to B.xls as B.xls has other worksheets which I wish to retain.I want to copy the contents of A.csv to the existing B.xls file's first worksheet.
I've tried a few scripts but the existing file B.xls is being over-written and the graph generation in the second worksheet is not working.
microsoft-excel batch csv xls
asked Jul 2 '15 at 4:46
AbbyAbby
625
add a comment |
I'm a newbie to batch scripting and need help.
I want to copy the contents of a csv file A.csv to the first worksheet named "GenData" in a existing XLS file B.xls.
B.xls contains some macros in it's second worksheet for graph generation from the data copied to it's first worksheet.
Both the files A.csv and B.xls are in the same folder.
Also, I want to repeat this for about 40 folders containing these two files.
C:DesktopAbbyFolder1A.csv
C:DesktopAbbyFolder40A.csv
PS: I do not want to convert the A.csv to B.xls as B.xls has other worksheets which I wish to retain.I want to copy the contents of A.csv to the existing B.xls file's first worksheet.
I've tried a few scripts but the existing file B.xls is being over-written and the graph generation in the second worksheet is not working.
microsoft-excel batch csv xls
asked Jul 2 '15 at 4:46
AbbyAbby
625
1
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16
add a comment |
I'm a newbie to batch scripting and need help.
I want to copy the contents of a csv file A.csv to the first worksheet named "GenData" in a existing XLS file B.xls.
B.xls contains some macros in it's second worksheet for graph generation from the data copied to it's first worksheet.
Both the files A.csv and B.xls are in the same folder.
Also, I want to repeat this for about 40 folders containing these two files.
C:DesktopAbbyFolder1A.csv
C:DesktopAbbyFolder40A.csv
PS: I do not want to convert the A.csv to B.xls as B.xls has other worksheets which I wish to retain.I want to copy the contents of A.csv to the existing B.xls file's first worksheet.
I've tried a few scripts but the existing file B.xls is being over-written and the graph generation in the second worksheet is not working.
microsoft-excel batch csv xls
asked Jul 2 '15 at 4:46
AbbyAbby
625
I'm a newbie to batch scripting and need help.
I want to copy the contents of a csv file A.csv to the first worksheet named "GenData" in a existing XLS file B.xls.
B.xls contains some macros in it's second worksheet for graph generation from the data copied to it's first worksheet.
Both the files A.csv and B.xls are in the same folder.
Also, I want to repeat this for about 40 folders containing these two files.
C:DesktopAbbyFolder1A.csv
C:DesktopAbbyFolder40A.csv
PS: I do not want to convert the A.csv to B.xls as B.xls has other worksheets which I wish to retain.I want to copy the contents of A.csv to the existing B.xls file's first worksheet.
I've tried a few scripts but the existing file B.xls is being over-written and the graph generation in the second worksheet is not working.
microsoft-excel batch csv xls
microsoft-excel batch csv xls
asked Jul 2 '15 at 4:46
AbbyAbby
625
asked Jul 2 '15 at 4:46
AbbyAbby
625
edited Jul 2 '15 at 5:20
Abby
asked Jul 2 '15 at 4:46
AbbyAbby
625
asked Jul 2 '15 at 4:46
AbbyAbby
625
asked Jul 2 '15 at 4:46
AbbyAbby
625
625
1
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16
add a comment |
1
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16
1
1
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16
add a comment |
3 Answers
3
active
oldest
votes
You can use PowerShell to read CSV and save to Excel ...
Check out this guys post ... Converting CSV file or files into an Excel workbook
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
add a comment |
I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.
Here's a nice blog post showing Power Query shredding a folder of csv files:
http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
add a comment |
Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?
It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.
OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fsuperuser.com%2fquestions%2f935309%2fneed-a-batch-script-to-copy-data-from-a-csv-to-b-xls%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use PowerShell to read CSV and save to Excel ...
Check out this guys post ... Converting CSV file or files into an Excel workbook
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
add a comment |
You can use PowerShell to read CSV and save to Excel ...
Check out this guys post ... Converting CSV file or files into an Excel workbook
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
add a comment |
You can use PowerShell to read CSV and save to Excel ...
Check out this guys post ... Converting CSV file or files into an Excel workbook
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
You can use PowerShell to read CSV and save to Excel ...
Check out this guys post ... Converting CSV file or files into an Excel workbook
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
answered Jul 2 '15 at 6:18
SteveCSteveC
1,09092345
1,09092345
add a comment |
add a comment |
I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.
Here's a nice blog post showing Power Query shredding a folder of csv files:
http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
add a comment |
I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.
Here's a nice blog post showing Power Query shredding a folder of csv files:
http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
add a comment |
I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.
Here's a nice blog post showing Power Query shredding a folder of csv files:
http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.
Here's a nice blog post showing Power Query shredding a folder of csv files:
http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
answered Jul 4 '15 at 3:08
Mike HoneyMike Honey
1,7661511
1,7661511
add a comment |
add a comment |
Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?
It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.
OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
add a comment |
Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?
It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.
OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
add a comment |
Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?
It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.
OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?
It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.
OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
answered Jul 7 '15 at 8:13
Some_GuySome_Guy
3751526
3751526
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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.
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%2fsuperuser.com%2fquestions%2f935309%2fneed-a-batch-script-to-copy-data-from-a-csv-to-b-xls%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
1
Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line stackoverflow.com/questions/1858195/… That way involves scripting.. not batch scripting but scripting.
– barlop
Jul 2 '15 at 5:00
you could also get a programmer to write you a little command line program to do it stackoverflow.com/questions/26648641/… then put the program on sourceforge so it's available to all
– barlop
Jul 2 '15 at 5:01
Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain.
– Abby
Jul 2 '15 at 5:03
For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA.
– Máté Juhász
Jul 2 '15 at 6:34
there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS
– barlop
Jul 2 '15 at 7:16