Home
Top.Mail.Ru Yandeks.Metrika
Forum: "Bases";
Current archive: 2002.04.01;
Download: [xml.tar.bz2];

Down

Gentlemen, help with reading data from Excel Find similar branches


Санька   (2002-03-05 13:28) [0]

The problem is as follows:
The customer gives the Excel file, and I transfer all the data to the local
DB for further processing. It's a shame that this file is not very well composed in cells (instead of automatically transferring)
a lot of spaces m / y words and pressed Inputs .... When copying a column into a table, all these extra characters look ugly, and not very corrected. If it helps - here is the code. Maybe someone will tell.
procedure TForm1.BitBtn1Click (Sender: TObject);
var i, k: integer;
n: string;
AppExcel, Value: Variant;
begin
i: = 6; // from the sixth line in the book of exel
AppExcel: = CreateOleObject ("Excel.Application");
AppExcel.Workbooks.Open ("c: \ 111.xls", False);
AppExcel.Visible: = True;
while i <= 34 do
begin
n: = AppExcel.Cells.item [i, 4] .Value;
taSS.edit;
taSS.insert;
taSs.fieldbyname ("Name"). AsString: = n;

taSS.post;
taSS.Next;
i: = i + 1;
end;
end;

Or tell me how best to organize all this ...
If possible, do not send to sites D-Excel was already ...
Well, I'm sick of it, probably. Thank you in advance!!!



MetallAdm   (2002-03-05 16:38) [1]

Is it possible to remove extra spaces, etc. ??

n: = AppExcel.Cells.item [i, 4] .AsString; --- Sdesya, too, how do you do string If it is string
taSS.edit;
taSS.insert;
DelSpaces (N); --- like that :))

taSs.fieldbyname ("Name"). AsString: = n;


Procedure DelSpaces (Var xx: String);
Var
lt, i: Integer;
ll, l: Integer;
s: string;
b, a: Byte;
begin
s: = xx;
ll: = 1;
Lt: = Length (S);
l: = 0;
a: = 0;
for i: = 1 to lt do
begin
if String (s [i]) = "" Then inc (l);
if String (s [i]) = "*" Then a: = 1;
end;
If l = lt Then
begin
s: = "*";
a: = 1;
ll: = 1;
end;
if a = 0 Then
begin
b: = 0;
For i: = lt downto 2 do
begin
if String (s [i]) <> "" then
if b = 0 then
begin
ll: = i;
b: = 1;
end;
end;
If ll> 2 Then Setlength (s, ll) else s: = "*";
end;
xx: = s;
end;

Yes at least entoy procedure
once, also wrote well don’t know how correct it is
long was

Yes, I did not begin to correct it. She marks empty lines with an asterisk for clarity :)

I think you will remove :)



Санька   (2002-03-05 17:29) [2]

Thank you very much - I’ll try. If that appeals to you again
(about the starlets :)).
And as for simplification of the task, in principle, no one arises
thoughts. Being someone has run into a problem ...
By the way, in my case, with this procedure, the computer “thinks” for a very long time (until it enumerates all the cells, until it inserts into the lines in the table)
It will be very interesting to know your opinion, specialists.



MetallAdm   (2002-03-05 17:47) [3]

By the way enta procedure removes
only from the end are extra spaces

but I think it’s not difficult to do such a thing
which finds single words in a string
and then push them in a row through the space of a mona in one line




Санька   (2002-03-06 09:05) [4]

Das ....
I don’t think that I can do it quickly: something like pascal
clean (in terms of string processing) did not have much to deal with. I am more and more on the DB. Well, Lana - I'll try .. Sanks.
And other thoughts do not have chtoli .... Au!



gek   (2002-03-06 09:40) [5]

It seems to me if the customer will give a file in csv format, then
it will be easier to take away spaces, etc.



MetallAdm   (2002-03-06 09:51) [6]

Look something like I once did on Turbo Squeak :))

U loads the text file and the glory shoves into the array
i.e. selects individual words from an array
Well, if it is not clear, I ask myself

Procedure LoadFile (Var txt: POuts; Fl: String);
Var
i, j, k: Word;
s: Word;
buff: Array [1..lhtWord] of Byte;
bRead, bWrite: Word;
Sk, NumL: Word;
Strs: String;
begin
Assign (Fls, Fl);
Reset (Fls, 1);
Size: = FileSize (Fls);
If Size <65000 then
begin
BlockRead (Fls, lBuff ^, Size, bRead);
i: = 1;
While (i <= Size) and (lBuff ^ [i] = "") or (lBuff ^ [i] = # 13) or (lBuff ^ [i] = # 10) do
begin
Inc (i);
end;
AddW: = 1;
k: = 0;
For s: = i to Size do
begin
j: = 0;
If (lbuff ^ [s] <> "") and (lBuff ^ [s] <> # 13) and (lBuff ^ [s] <> # 10) and (lBuff ^ [s] <> ".") And (lBuff ^ [s] <> ",") Then
begin
{Write (lbuff ^ [s]);}
Inc (k);
Strs [k]: = lbuff ^ [s];
Strs [0]: = Chr (k);
txt ^ [addW]: = Strs;
end
else
begin
j: = s;
k: = 0;
If (lBuff ^ [j] = ".") Or (lBuff ^ [j] = ",") Then
begin
{Strs [k]: = lbuff ^ [s];
Strs [0]: = Chr (k);}
If (lBuff ^ [j + 1] <> "") or (lBuff ^ [j + 1] <> # 13) or (lBuff ^ [j + 1] <> # 10) Then
begin
Inc (AddW);
s: = j;
end;
end
else
begin
While (lBuff ^ [j] = "") or (lBuff ^ [j] = # 13) or (lBuff ^ [j] = # 10) do
begin
Inc (j);
{If (lBuff ^ [j] = # 13) or (lBuff ^ [j] = # 10) Then Write ("|") else Write ("+");}
end;
Inc (AddW);
s: = j-1;
end;
end;
end;
end;
end;


Heh Long procedure :))



Shirson   (2002-03-06 12:50) [7]

Hmm ...
XL has two such functions:
1. Clean (Text) Removes all nonprintable characters from the text (line feed refers to them).
2. Trim (Text) Removes all spaces from the text, with the exception of single spaces, between words.

Total by =Trim(Clean(A1)) Long Procedures can be avoided :)



Санька   (2002-03-06 14:10) [8]

> Shirson
Clarify, please, do these functions directly in Excel, or do they call Mona from Delphi?



Shirson   (2002-03-06 14:35) [9]

> Sanka

You can directly in XL. You can also from Delphi - to get in XL any cell with the formula =Trim(Clean(A1)), only substitute all the cells that need to be processed instead of A1, and from this read the data in Delphi and throw it into the database ... er .. it's just not clear why the Delphi step is needed here :) You can throw data into the database directly from XL :) )))



Санька   (2002-03-06 14:45) [10]

> Shirson
I'll try, thank you ...
As for direct transfer to the database: it’s necessary to do it with the help of Excel, but my users do not guess ... Then, if the data is repeated, it will be difficult for me to track this. And so - I copy to the intermediate database, and then I do SQL and select what I want for the main ... In general, the project is pretty dumb :) The main program has been working for a long time and everything has been debugged, but out of boredom I start to freak out and come up with new simplifications for users.
Well, I’ll go on to suffer further. Thanks to all!!! Helped!
Soon I will probably turn again. ;)



Санька   (2002-03-06 15:13) [11]

> Shirson
By the way, I tried this:

n: = AppExcel.Cells.item [i, 4] .Value;
Trim (n); - it works, removes spaces
Clean (n); - and this df is unknown to delphi

Maybe something will replace this feature. In a help on work with lines rummaged - nothing :(. ???????



TIP   (2002-03-06 15:31) [12]

And for Delphi there is an excellent Qstrings library
moreover, work with strings in assembler.



Shirson   (2002-03-06 15:35) [13]

In general, in Delphi (at least in D6), the trim function removes spaces at the beginning of a line, at the end of a line, and all control characters from the line itself. (how did you rummage through the help? :))
Try to trim the line only with trim - what happens?



Shirson   (2002-03-06 15:40) [14]

> Sanka
"As for the direct transfer to the database: it’s all right to do it with the help of excel - and my juvers do not guess ... Then, if the data is repeated, it will be difficult for me to track this."

The owner is the master :) I would not have fenced in the garden, but did everything in XL. And the users would need to press one pimp without thinking about the mechanism of the system. And then there’s a check that you want to do and twist the UI, etc. But if you want to use the delphi, you need to :)



Санька   (2002-03-06 16:21) [15]

:))))))) Yes, I thought that a long time ago I left dummies ... :)
Work with strings, say, and trim is an assembler chip,%)
shorter than %% (((.
Well, s, with strings of holes with strings ... I just can’t find anything but joining, searching for characters, etc., etc., but about removing spaces and “inputs” and “tabs” and a hedgehog with them - I see nothing but trims.
If you tell the unfortunate programmer - thanks.
If not, I’ll go to learn English and buy D for dummers :))
And, thank you all again, thank you, I went to get drunk !!!




dmitryK   (2002-03-06 18:13) [16]


> And as for the simplification of the task, in principle, no one arises
> thoughts. Being someone has run into a problem ...
> By the way, in my case, with this procedure, the computer "thinks"
> a very long time (while iterates over all the cells, until in the rows in
> the tablet will insert)
> It will be very interesting to know your opinion, specialists.


No wonder he thinks so long. The code is slow.
In a nutshell, how to optimize. This is the source code:


> procedure TForm1.BitBtn1Click (Sender: TObject);
> var i, k: integer;
> n: string;
> AppExcel, Value: Variant;
> begin
> i: = 6; // from the sixth line in the book of exel
> AppExcel: = CreateOleObject ("Excel.Application");
> AppExcel.Workbooks.Open ("c: \ 111.xls", False);
> AppExcel.Visible: = True;
> while i <= 34 do
> begin
> n: = AppExcel.Cells.item [i, 4] .Value;
> taSS.edit;
> taSS.insert;
> taSs.fieldbyname ("Name"). AsString: = n;
>
> taSS.post;
> taSS.Next;
> i: = i + 1;
> end;
> end;


AppExcel.Visible: = True; - What is this for?? what would work slower?
This piece of code indicates the need to visualize the process of working with XL, which naturally slows down the work.

n: = AppExcel.Cells.item [i, 4] .Value; - The code from the textbook is absolutely correct, but absolutely inhibitory. I don’t know exactly how it works (at the OS level), but in time it’s absolutely no matter how much data you download from the page of the book — one cell or 1000. To do this, create a dynamic array of the required size and load Range there.
And only then analyze this array, which in itself is much faster.

But this piece of code is not entirely clear to me
> taSS.edit;
> taSS.insert;
> taSs.fieldbyname ("Name"). AsString: = n;
>
> taSS.post;
> taSS.Next;

trying to replace existing lines ?? although it was like talking about adding new ones ?? But in any case, it is often faster to generate the whole SQL query and execute it. Since, according to the code, less than 30 rows are expected in one go, it is possible to send the whole package of changes (30 SQL queries for modification / addition) in one go, which will also speed up the task execution time.

and last
i: = i + 1; - it is better to replace such a fragment with Inc (i);

As for tips on optimizing the speed of access to data in XL, I highly recommend looking at the Kingdom of Delphi website and finding the articles “On the Waves of Integration”. Everything that I briefly mentioned here is described in great detail there. Read it.

As for removing extra spaces, this problem is not worth a damn. I don’t know what about D6, but before TRIM, I only cut leading and trailing spaces. If now he still pluses this removes intermediate control characters, then everything is generally simple, well, if not, then you have to spend half an hour to delete them yourself.



dmitryK   (2002-03-06 18:16) [17]


> TIP (06.03.02 15: 31)
> But for Delphi there is an excellent Qstrings library
> moreover, work with strings in assembler.


By the way, a very useful tip, I highly recommend using it.



MetallAdm   (2002-03-06 18:57) [18]

heh yes I think your question about optimization has already risen :)

> Shirson by the way did not know did not know :)
still can’t wean from dos times I can’t
when any programs had to be done from scratch :))



Pages: 1 whole branch

Forum: "Bases";
Current archive: 2002.04.01;
Download: [xml.tar.bz2];

Top





Memory: 0.63 MB
Time: 0.031 c
1-175
Aero
2002-03-19 16:54
2002.04.01
How to transfer value from one program to another?


1-96
earrings
2002-03-21 12:36
2002.04.01
Hello to all. Where to get the Update pack for Delphi 5


14-317
Vudz
2002-02-17 14:43
2002.04.01
How do you like this:


4-347
unikum
2002-01-31 19:41
2002.04.01
Subdirectories


1-209
Evgenyk
2002-03-10 16:35
2002.04.01
How to programmatically obtain data on the file version?





afrikaans albanian Arabic armenian azerbaijani basque belarusian bulgarian catalan Chinese (Simplified) Chinese (Traditional) croatian Czech danish Dutch English estonian filipino finnish French
galician georgian German greek haitian Creole hebrew Hindi hungarian icelandic Indonesian Irish italian Japanese Korean latvian lithuanian macedonian malay maltese norwegian
persian polish portuguese Romanian russian serbian slovak Slovenian Spanish swahili Swedish ภาษาไทย turkish Ukrainian urdu Tiếng Việt welsh yiddish bengali bosnian
cebuano Esperanto gujarati hausa hmong igbo javanese kannada Khmer lao latin maori marathi mongolian nepali punjabi somali tamil telugu yoruba
zulu
English French German Italian Portuguese Russian Spanish