How to create a .XLS file instead of a .CSV file

[Karen Middleton]

I have a COBOL program that generates a .CSV file regularly but I have
a problem in the .csv file my users want the file to be in .XLS
instead of .CSV is there a simple way I can turn my .csv file into a
..XLS file please advise.

Can my COBOL program put some structure to turn the .CSV file into a
..XLS file.

Also, can somebody share a VB Script to turn a .CSV file into a .XLS
file.

Thanks
Karen

 

[Tom Ogilvy]

An easy way is to open it in Excel and then save it as a workbook.

You might just write a CSV file and name it with an XLS extension if the
main gripe is having to change the fileopen dialog to look for a CSV.

 

[keepITcool]

following vb script generates an excel file from a comma-delimited (CSV)
text file WITHOUT using excel. it relies on ADO to do the work and if
scripting is enabled you can just put it into a vbs file. (you can also
put the code into a vba procedure)

be careful with the syntax... SELECT INTO statement has several
"syntaxis" (see KB 295646), but this is the only way I got it to work
with both CSV and XLS in a "one-liner"

note the the source file name cannot contain spaces!


copy following into notepad
then save with the extention .vbs

cut here =>

Dim csvPath
Dim csvFile
Dim xlsFile

csvPath = "d:\ado test"
csvFile = "myExport.csv"
xlsFile = "d:\a new workbook.xls"

With CreateObject("Scripting.FileSystemObject")
If .FileExists(xlsFile) Then .DeleteFile (xlsFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";" & _
"Data Source='" & csvPath & "';"

.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO [Import] " & _
" IN '' [Excel 8.0;Database=" & xlsFile & "]" & _
" FROM " & csvFile
.Close

End With


<==cut here




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jamie Collins]

be careful with the syntax... SELECT INTO statement has several
"syntaxis" (see KB 295646), but this is the only way I got it to work
with both CSV and XLS in a "one-liner"

I prefer the alternative syntax e.g.

SELECT MyCol FROM [Excel
8.0;HDR=YES;Database=C:\Tempo\db.xls].MyTable

The sequence <<database>>.<<table>> is more logical, it avoids those

note the the source file name cannot contain spaces!

This is incorrect (perhaps you were referring to a specific syntax
rather than generally?). You merely surround the file name (table
name) with e.g. brackets:

SELECT MyCol FROM [Text;Database=C:\Tempo\;].[Gappy Gap#csv];

I think your code is connecting to the wrong object i.e. the ADO
connection should be to Excel. As you said, because you connected to
the source and the source is Text you had syntax problems (a
limitation or bug in the provider?) You can open a connection to a
non-existent workbook; remember, the provider creates the workbook,
worksheet and Excel table on the fly if required. Also, with the
provider you are using (OLE DB for Jet 4.0), FMT=Delimited in the
connection string has no effect; you must use a scheme.ini file to
specify format, however comma delimited is default. Thus:

.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & xlsFile & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

.Execute _
"SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & csvPath & ";].[" & csvFile &
"];"

Jamie.

--

 

[keepITcool]

Jamie...

Many roads lead to Rome... but you seem to use SatNav!
<g>

i'm learning (and struggling).. but i wouldn't mind a kickstart.

have you published all that knowledge of yours somewhere?
or can you recommend some definitive guide/book/site
with the down and dirty on all these ado settings?
(i currently use www.able-consulting.com/ADO_Conn.htm as 'bible'



of course the "trick" in this case was simple in hindsight:
you set the Source of the connectstring to the
(non existing) target file, which solved a lot of the syntax hassle.

Thanx... one of these days I'll get satnav too







keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

be careful with the syntax... SELECT INTO statement has several
"syntaxis" (see KB 295646), but this is the only way I got it to work
with both CSV and XLS in a "one-liner"

I prefer the alternative syntax e.g.

SELECT MyCol FROM [Excel
8.0;HDR=YES;Database=C:\Tempo\db.xls].MyTable

The sequence <<database>>.<<table>> is more logical, it avoids those

note the the source file name cannot contain spaces!

This is incorrect (perhaps you were referring to a specific syntax
rather than generally?). You merely surround the file name (table
name) with e.g. brackets:

SELECT MyCol FROM [Text;Database=C:\Tempo\;].[Gappy Gap#csv];

I think your code is connecting to the wrong object i.e. the ADO
connection should be to Excel. As you said, because you connected to
the source and the source is Text you had syntax problems (a
limitation or bug in the provider?) You can open a connection to a
non-existent workbook; remember, the provider creates the workbook,
worksheet and Excel table on the fly if required. Also, with the
provider you are using (OLE DB for Jet 4.0), FMT=Delimited in the
connection string has no effect; you must use a scheme.ini file to
specify format, however comma delimited is default. Thus:

.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & xlsFile & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

.Execute _
"SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & csvPath & ";].[" & csvFile &
"];"

Jamie.

--

 

[Jamie Collins]

of course the "trick" in this case was simple in hindsight:

you set the Source of the connectstring to the
(non existing) target file, which solved a lot of the syntax hassle.

This trick (i.e. opening a connection to a non-existent .xls) can also
be used when you need a Jet connection which doesn't involve Excel at
all e.g. to create a text file using SQL Server data:

SELECT fname, minit, lname
INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[Export#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=(local);DATABASE=pubs;UID=***;Pwd=***;].employee;

i'm learning (and struggling).. but i wouldn't mind a kickstart.
or can you recommend some definitive guide/book/site
with the down and dirty on all these ado settings?
(i currently use www.able-consulting.com/ADO_Conn.htm as 'bible'

Do you mean the Jet syntax where the connection string is in the sql
text? I agree there are few details out there. I don't even know what
this syntax 'is' - ODBCDirect? I've gleaned details only through hours
of experimentation (plus a little Excel app that archives my sql
queries) but there are still holes in my knowledge e.g. is it possible
to specify the path to the .mdw used to secure an .mdb? There is no
Jet newsgroup and most in the MS Access ngs cannot tell Jet from MS
Access anyhow.

I also agree able-consulting is a good source for general ADO:
invaluable connection string collection and interesting faq.

have you published all that knowledge of yours somewhere?

I'm gradually publishing all my knowledge in the Usenet archive <g>. I
guess I could scrape together a few html pages that could be a useful
reference source (for me if no one else) but I don't have the time to
maintain a domain. Others have kindly hosted some stuff I can link to
e.g.

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

You gotta hand it to people like Dick Kusleika who can write daily on
an interesting and relevant topic. My blog about Excel+sql would dry
up after the first week <g>.

Jamie.

--