Need a tool to convert .csv to .xls

[Vanguard]

New user here. While we use Excel here at work, it is only in the capacity
of providing input data and reviewing test results of our testing.

At work, they use a 3rd party tool that accepts a .csv file and converts it
to an .xls file. This is input data for data driven scripts used by
WinRunner (a testing tool). We don't want to be loading Excel and simply
reading the .csv file and then save it as an .xls file because we have the
conversion scheduled to run in the morning when the input data is supposed
to arrive. We also don't want to be using Excel itself since we would have
to lose a license for a copy to install it on the server.

Preferrably the tool should be open source (i.e., free) but stable.
WinRunner doesn't accept .xls files with more than one worksheet in them
(i.e., you need to have just one spreadsheet in the file). This is the
format that the users are supposed to use when they give us the data.

To summarize, the requirements are:
- Converts .csv file to an .xls file.
- Only need to support an .xls file with one worksheet.
- Runs on a NT-based Windows platform (Windows 2000/2003/XP).
- Has command-line parameter to specify the input and output files. No GUI
gets used (if it has one, it must still run from a command line).

 

[Earl Kiosterud]

Vanguard,

Is there no workstation with Excel already licensed where you could use
Excel to convert the csv to xls? The command line could start Excel,
specify an Excel workbook containing a macro that auto-runs and opens your
csv file, and saves it as xls.

 

[Vanguard]

Is there no workstation with Excel already licensed where you could use
Excel to convert the csv to xls? The command line could start Excel,
specify an Excel workbook containing a macro that auto-runs and opens your
csv file, and saves it as xls.

I am not an Excel user. I know just enough to get past the muddle and
dumbfounded level. Could you tell me what would be the command-line syntax
which would have Excel startup, load the .csv file, save it as an .xls file,
and then exit Excel? It is possible that we have more licenses of Excel (or
MS Office) around that could be used as you mention. There can be no manual
intervention in this process because it is a scheduled and unattended event.
We can already do it manually but need a utility or method to perform the
conversion from the command line so it can be executed within a batch file
when there is no one watching a monitor (and there won't even be a monitor
to show the GUI or console window) and no one to walk Excel through the save
after the .csv file got loaded.

 

[Jerry W. Lewis]

Start Excel, then follow the menu choices
Data|Import External Data|Import Data
Specify the .csv file, and follow the prompts of the Import Wizard.

Jerry

 

[Vanguard]

That won't work. Remember that I said that there must be no user
intervention. The scheduled script performs the file conversion. Scheduled
tasks require no GUI (or a command-line interface) and require no user
intervention (since no one will be around to be using the keyboard or mouse
to control the application, and the script might not even have a desktop to
present its GUI).

A manual convert is even easier than you mention below (since the point is
to generate an .xls file from a .csv file). Just load Excel, open the .csv
file, and use File -> Save As to save as an .xls file. But that is all
manual and we need a script to handle it where it runs without user
intervention or even a window.

I saw the AMB Amber Conversion Utility ($20,
http://www.thebeatlesforever.com/processtext/abccsv.html) which said it had
a command-line interface (for the registered version) which seemed like it
would do the job and was a cheap solution. As it turned out, the boss
decided to stick with Mindlead's JobMaster ($400). So the problem is solved
for now by sticking with what they were using.

 

[DungFlinger]

That won't work. Remember that I said that there must be no user
intervention. The scheduled script performs the file conversion. Scheduled
tasks require no GUI (or a command-line interface) and require no user
intervention (since no one will be around to be using the keyboard or mouse
to control the application, and the script might not even have a desktop to
present its GUI).

A manual convert is even easier than you mention below (since the point is
to generate an .xls file from a .csv file). Just load Excel, open the .csv
file, and use File -> Save As to save as an .xls file. But that is all
manual and we need a script to handle it where it runs without user
intervention or even a window.

I saw the AMB Amber Conversion Utility ($20,
http://www.thebeatlesforever.com/processtext/abccsv.html) which said it had
a command-line interface (for the registered version) which seemed like it
would do the job and was a cheap solution. As it turned out, the boss
decided to stick with Mindlead's JobMaster ($400). So the problem is solved
for now by sticking with what they were using.

I'm not real sure how to do this but it sounded interesting. did some
google'n and found this:
http://www.softinterface.com/Convert-File-Programs/Convert-Excel-File-Programs-Target.HTM
This may be what your looking for, although it's not free. There is a
free trial.. keyword @ google: excel command line import .csv

hth

df

 

[Jerry W. Lewis]

That won't work. Remember that I said that there must be no user
intervention. The scheduled script performs the file conversion. Scheduled
tasks require no GUI (or a command-line interface) and require no user
intervention (since no one will be around to be using the keyboard or mouse
to control the application, and the script might not even have a desktop to
present its GUI).

You could record a macro (Tools|Macros|Record New Macro) while you
either import as described or open as you countered; then schedule
re-execution of that macro.

A manual convert is even easier than you mention below (since the point is
to generate an .xls file from a .csv file). Just load Excel, open the .csv
file, and use File -> Save As to save as an .xls file. But that is all
manual and we need a script to handle it where it runs without user
intervention or even a window.

Opening the .csv only works if you are you are happy with the default
interpretation (for instance if there are numeric ID #'s that contain
more than 15 digits, then you will need the Import Wizard to force that
column to be interpreted as text).

Jerry

 

[Vanguard]

I'm not real sure how to do this but it sounded interesting. did some
google'n and found this:
http://www.softinterface.com/Convert-File-Programs/Convert-Excel-File-Programs-Target.HTM
This may be what your looking for, although it's not free. There is a
free trial.. keyword @ google: excel command line import .csv

I looked at their site and went through the ordering process to see it might
cost $900, or more, for a single license. That's more expensive than the
$400 for Jobmaster. The ABC Amber CSV Converter was only $20 and said it
could do the conversion.

 

[Vanguard]

You could record a macro (Tools|Macros|Record New Macro) while you either
import as described or open as you countered; then schedule re-execution
of that macro.

As I mentioned, the boss decided to just renew the license to JobMaster. So
the problem doesn't exist anymore but I'd still like to know a workable
solution should the decision change later.

There was a problem getting a license for Excel on the remote server host.
So we needed a utility that didn't require Excel to be present to do the
conversions. For anytime that we might be doing this locally, what would be
command line syntax to call Excel to have it load, run a macro, and then
exit Excel?

 

[Jerry W. Lewis]

You could run the macro from the Workbook_Open event of a particular
workbook. Then you would just need to issue a command like

"C:\Program Files\Microsoft Office\Office10\Excel" "\My
Documents\myFile.xls"

(adjust paths as appropriate for your machine). The macro could then
close Excel after doing all the work.

Jerry

 

[Vanguard]

Thanks for the info. I'll play around with this a bit later.

 

[DungFlinger]

I looked at their site and went through the ordering process to see it might
cost $900, or more, for a single license. That's more expensive than the
$400 for Jobmaster. The ABC Amber CSV Converter was only $20 and said it
could do the conversion.

whoops sorry bout that.... I couldn't find the price.