Excel to CSV conversion.

[spr]

Thanks for all the suggestions . Now, I'm trying to convert info read from an excel file to CSV through VC++. How do I go about reading the info and converting to CSV. The same procedures used for access files are not working.
Thanx in advance

 

[TomSark]

You could do this a few ways...

You could use DTS to convert it and keep it in SQL Server instead of VC++.

You could do this using an excel.application object and invoke the 'save as' method (I speak VB, not VC++)...

Tom

 

[Ruairi]

here's the code for saving an excel file as csv. I, like Tom speak VB, you should be able to translate this pretty easily. I'm using command line arguments to get the filenames and such, just ignore that part and use these properties and methods of the excel object. No reason to reinvent the wheel when excel can do the translation for you.
hth

Dim ObjXL As Excel.Application
Dim CmdArgs()

CmdArgs() = GetCommandLine 'Split the command line into 2 arguments the first is the
'excel file to convert, the second is the name to save the CSV file as. Store these 2
'filenames in the array CmdArgs

CSVDir = Mid(CmdArgs(2), 1, InStr(CmdArgs(2), "\"))

On Error Resume Next
Kill CmdArgs(2) 'if the CSV file already exists delete it so excel can save to that
'filename without prompting the user
Kill CSVDir & "convert"
On Error GoTo 0

Set ObjXL = CreateObject("excel.application") 'create a new instance of excel in
'memory to handle the conversion. this is completely seperate from any already running
'instances of excel, and the opening and conversion is done in the background.

ObjXL.Interactive = False    ' This line tells excel to ignore user input. this is
'not really nessecary because we created an 'invisible' instance of excel, but it is
'always a good idea when controlling excel progromatically
ObjXL.DisplayAlerts = False  'This line tells excel not to ask whether to overwrite
'files, change from excel format to csv, etc..

ObjXL.Workbooks.Open FileName:=CmdArgs(1) 'Open the file that is specified in the first
'command line argument

ObjXL.ActiveWorkbook.Saved = True ' "trick" excel into thinking the original excel file
'has been saved. again not really necessary since excel will not be prompting for user
'input, but always a good idea if you have no changes to save to the excel file

ObjXL.ActiveSheet.SaveAs FileName:=CmdArgs(2), FileFormat:=xlCSV, CreateBackup:=False
'Saves the excel file as a CSV file with the name specified in the second argument
'the FileFormat:=xlCSV is the important part that does the conversion

ObjXL.Workbooks.Close 'Close the file
                          
ObjXL.Quit 'Close the instance of excel in memory

ObjXL.Interactive = True   'Let Excel accept user input again
ObjXL.DisplayAlerts = True 'Let excel prompt before saving, closing, etc.

Set ObjXL = Nothing 'Release the refrence to excel, this allows the instance of excel
'to be cleared from memory

Ruairi

http://www.plccontroltechnologies.com

ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[swilliams]

Ruairi,

What is GetCommandLine???

Simon

 

[TomSark]

So specifically what isn't working?

Tom