Interfacing with Excel

[ConvertFromOldNGs]

by JADE News Administrator >> Wed, 28 Oct 1998 22:15:07 GMT

Hi
I have a requirement to be able to load up Excel within my JADE application and pass on the command line the name of a CSV (ASCII text) file. It then should be able to fire up Excel and take the CSV file and split the fields up into separate columns in a new worksheet.

With my preliminary investigations I can easily call up Excel within JADE (using the shell32 external library function described in other postings) and pass a CSV file-name on the command line ---- and it loads up the CSV file into a new worksheet --- but puts all the content into column:1.

I need to get it to automatically perform what the import wizard does and split the fields up into columns?

I guess I need to look into ODBC etc but is there an easier solution ?

Ciao .................
Alan J.Thomson
ph...Auckland
http://home.clear.net.nz/pages/athomson

[ConvertFromOldNGs]

by JADE News Administrator >> Wed, 28 Oct 1998 22:32:45 GMT

If you just want to load up spreadsheet row and column values from a comma delimmited file, just comma separate column values and end each row with a CRLF (0d0a hex). Last time I tried this with excel (a couple of minutes ago) it worked.

[ConvertFromOldNGs]

by JADE News Administrator >> Wed, 28 Oct 1998 22:33:36 GMT

Alan's problem was that he named the file containing comma separated values with a suffix of .txt instead of .csv. Excel doesn't assume that ...txt files contain comma separated values.

BT

[ConvertFromOldNGs]

by JADE News Administrator >> Wed, 28 Oct 1998 22:49:32 GMT

See the report example in the Advanced Course Schema. Using Tab delimited files and starting excel using CreateExternalProcess method.

Cheers ... Arjan
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Arjan van Hasselt
Cardinal Technologies
CHRISTCHURCH
New Zealand
avanhasselt@cardinal.co.nz
Have a nice day
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[ConvertFromOldNGs]

by Hayden >> Tue, 17 Nov 1998 14:47:25 GMT

On a slight tangent....
Is this the easiest way to read a csv file into Jade ??

I have a requirement to read and write csv files, but I would prefer
to do this without bringing Excel into the picture. Can this be done
in Jade? Up until now I have read and written Tab delimited files,
since these are easy to control.

My latest plan is actually to try and do something similar to that of Excel - read in a CSV file, and dynamically create the correct number
of columns in a table to display the info, before importing it into
the live system.

Should I even bother attempting this? Has it already been done? Can anyone give me any tips to smooth my path?

Thanks

Hayden McInnes
Software Developer
Focus Business Solutions
hayden@focussoft.co.nz

[ConvertFromOldNGs]

by Skull >> Wed, 18 Nov 1998 4:57:46 GMT

Hi Hayden,

On the surface it sounds like your requirement is to import a comma delimitted file into JADE, which is basically the inverse to that discussed previously.

I have a requirement to read and write csv files, but I would prefer to do this without bringing Excel into the picture. Can this be done
in Jade? Up until now I have read and written Tab delimited files,
since these are easy to control.

Well, it sounds like all you need to do is change tab characters to a commas and your away laughing

My latest plan is actually to try and do something similar to that of Excel - read in a CSV file, and dynamically create the correct number
of columns in a table to display the info, before importing it into
the live system.

If the .csv file has been created in the same format as that created by Excel, then all you need to do is use the JADE file class readLine method to fetch each 'row' (since a row is delimited by CrLf) and then parse the string for comma seperated values - you can dynamically size the Jade table to suit the data as you load it.

[ConvertFromOldNGs]

by Hayden >> Wed, 18 Nov 1998 5:28:36 GMT

Hey Skull.

On the surface it sounds like your requirement is to import a comma delimitted file into JADE, which is basically the inverse to that discussed previously.

Sorry, I must have mis-read that thread.

I have a requirement to read and write csv files, but I would prefer to do this without bringing Excel into the picture. Can this be done
in Jade? Up until now I have read and written Tab delimited files,
since these are easy to control.

Well, it sounds like all you need to do is change tab characters to a commas and your away laughing

Yes and No. The beauty about cvs is that you can have commas inside strings. This is where the complexity comes in.... I don't know if I want to test every char read in - but will I have to?

It seems to be a pain that Jade has not included some of these "basic features" in it's release. It's mainly just time consuming to have to re-create the wheel for yourself....

My latest plan is actually to try and do something similar to that of Excel - read in a CSV file, and dynamically create the correct number
of columns in a table to display the info, before importing it into
the live system.

If the .csv file has been created in the same format as that created by Excel, then all you need to do is use the JADE file class readLine method to fetch each 'row' (since a row is delimited by CrLf) and then parse the string for comma seperated values - you can dynamically size the Jade table to suit the data as you load it.

Yeah, this part seems fairly straight forward.

Thanks.
Hayden.

[ConvertFromOldNGs]

by Craig Shearer >> Thu, 5 Nov 1998 23:35:19 GMT

Everybody seems to be assuming that CSV format is easy, but it does contain some hidden complexity (double quotes and commas within fields, etc.)

I have written some code to both write strings to a CSV file (ie. taking into account whether they contain double quote or comma characters), and to load data into JADE from a CSV formatted file.

Reading the CSV file is the hardest part, and before implementing it myself, I did a search of the web for algorithms to do it, but to no avail.

Anyway, I'm prepared to make to code available - contact me if you're interested.

Craig.
cds@wang.co.nz

[ConvertFromOldNGs]

by Hayden >> Sun, 22 Nov 1998 11:45:43 GMT

Craig,

Anyway, I'm prepared to make to code available - contact me if you're interested.

Thanks for the offer, but I ended up getting my noggin around it.

But you've given me a thought. Below is my code. Have a look at it, compare it to yours, anyone can comment, or pick holes with it, but
feel free to do want you want, and if you make it better, post it.
Sort of a GNU license thing....

BTW, have you noticed that Excel only puts quotes around it's strings
in CSV if there is a comma in the string? A "variation" that this
logic works with.

Enjoy the code. Lemme know what you think.
Thanks.
Hayden.


i, j, pos :Integer;
len :Integer;
line :String;
sa :StringArray;
f :File;
begin


// locate and open the file to read, etc

// Read CSV routine
j := 0;
while not f.endOfFile do
j := j + 1;
line := f.readLine;
pos := 1;
i := 1;

while pos < line.length do
// Locate next comma
len := line.pos(",",pos);
len := len - pos;
if line.pos(",",pos) = 0 and pos < line.length then
len := line.length - pos + 1;
endif;
// Put string into array
sa := line[pos:len];
// If comma inside quotes, concatenate with rest of
// string
while sa[1] = '"' and sa[sa.length] <> '"' do
pos := pos + len + 1;
len := line.pos(",",pos);
len := len - pos;
sa := sa & "," & line[pos:len];
endwhile;
// Remove quotes from the string
if sa[1] = '"' and sa[sa.length] = '"' then
sa := sa[i][2:sa[i].length-2];
endif;
// Increment position to just after the last comma
pos := pos + len + 1;
i := i + 1;
endwhile;

// sa contains full line, and can now be used for display, etc

// Progress bar indicator
showProgress(j, tot);
endwhile;

[ConvertFromOldNGs]

by Paul Mathews >> Wed, 24 Feb 1999 6:20:59 GMT

Alan J.Thomson mentioned using shell32.

(using the shell32 external library function described in other postings)

I haven't been able to see any other postings on this, maybe going
blind.

But what is it's advantage starting excel using CreateExternalProcess method (Arjan van Hasselt).

TIA

Paul Mathews
pem@cmsystemsgroup.com.au
Phone: [612] (99717384) Fax[612] (99711679)
(Dee Why,Sydney,Australia)

Please visit our homepage cmsystemsgroup.com.au.