Board index » delphi » TXT delimited (;) filles problem

TXT delimited (;) filles problem


2007-05-14 02:57:17 PM
delphi223
I'm connecting to several txt files delimited with ";".
They are all the same format (columns, rows and data), but on one file there
is an error.
The data in one column is:
row 1: 83,07
row 2: 11,57
row 3: 6,22
row 4: 0
but delphi sees it as date:
row 1:
row 2: 30.12.1899 11:57:00
row 3: 30.12.1899 6:22:00
row 4: 30.12.1899
Why am I getting it as date? Is there any way to force it to see it as
number?
 
 

Re:TXT delimited (;) filles problem

At first, how do you connect to these files? Probably with ADO because you
write into this newsgroup, but more information is needed.
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Luka" <XXXX@XXXXX.COM>сообщи?сообщила ?новостя?следующе?
Quote
I'm connecting to several txt files delimited with ";".
They are all the same format (columns, rows and data), but on one file
there is an error.
The data in one column is:

row 1: 83,07
row 2: 11,57
row 3: 6,22
row 4: 0

but delphi sees it as date:

row 1:
row 2: 30.12.1899 11:57:00
row 3: 30.12.1899 6:22:00
row 4: 30.12.1899

Why am I getting it as date? Is there any way to force it to see it as
number?




 

Re:TXT delimited (;) filles problem

adoquery1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=g:\temp+';Extended Properties=''text;FMT=Delimited''';
it works fine with all but one txt file, and they are basicly the same, just
diferent data, same format
"Vassiliev V. V." <XXXX@XXXXX.COM>writes
Quote
At first, how do you connect to these files? Probably with ADO because you
write into this newsgroup, but more information is needed.

//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Luka" <XXXX@XXXXX.COM>сообщи?сообщила ?новостя?
следующе? news:XXXX@XXXXX.COM...
>I'm connecting to several txt files delimited with ";".
>They are all the same format (columns, rows and data), but on one file
>there is an error.
>The data in one column is:
>
>row 1: 83,07
>row 2: 11,57
>row 3: 6,22
>row 4: 0
>
>but delphi sees it as date:
>
>row 1:
>row 2: 30.12.1899 11:57:00
>row 3: 30.12.1899 6:22:00
>row 4: 30.12.1899
>
>Why am I getting it as date? Is there any way to force it to see it as
>number?
>
>
>
>


 

Re:TXT delimited (;) filles problem

Is this the only input file that has a "comma" in the number ???
 

Re:TXT delimited (;) filles problem

"Luka" <XXXX@XXXXX.COM>wrote in
Quote
adoquery1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=g:\temp+';Extended Properties=''text;FMT=Delimited''';
Is there a g:\temp\schema.ini file? If so, what does it contain?
Chris
Quote
it works fine with all but one txt file, and they are basicly the
same, just diferent data, same format



"Vassiliev V. V." <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>At first, how do you connect to these files? Probably with ADO
>because you write into this newsgroup, but more information is
>needed.
>
>//------------------------------------------
>Regards,
>Vassiliev V. V.
>www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>ADO.Net
>www.oledbdirect.com - The fastest way to access MS SQL Server,
>MS Jet (Access) and Interbase (through OLEDB)
>
>
>"Luka" <XXXX@XXXXX.COM>сообщи?сообщила ?новостя?
>следующе? news:XXXX@XXXXX.COM...
>>I'm connecting to several txt files delimited with ";".
>>They are all the same format (columns, rows and data), but on one
>>file there is an error.
>>The data in one column is:
>>
>>row 1: 83,07
>>row 2: 11,57
>>row 3: 6,22
>>row 4: 0
>>
>>but delphi sees it as date:
>>
>>row 1:
>>row 2: 30.12.1899 11:57:00
>>row 3: 30.12.1899 6:22:00
>>row 4: 30.12.1899
>>
>>Why am I getting it as date? Is there any way to force it to see it
>>as number?
>>
>>
>>
>>
>
>


 

Re:TXT delimited (;) filles problem

"Luka" <XXXX@XXXXX.COM>wrote
Quote
... it works fine with all but one txt file, and they are
basically the same, just different data, same format ...
Luka,
Then show us the content of the file around the point
of the problem in hexadecimal or equivalent. This
will help us help you.
Rgds, JohnH
 

Re:TXT delimited (;) filles problem

I found the answer to my problem, adding IMEX=1 to the connection string
tells the query to interprete all the data as text. And then I convert
string to number with strtofloat();
The connection string then looks like this:
adoquery1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=g:\temp+';Extended Properties=''text;FMT=Delimited; IMEX=1''';
To answer Del, all the files have numbers with comma;
To answer Chris, schema.ini file is needed if the text file is TAB
delimited, and when delimiter is comma, or some other symbol (like ;) it is
not needed.
Thanx everyone for replays, maybe this solution will help you all at some
point
Luka
"John Herbster" <herb-sci1_AT_sbcglobal.net>writes
Quote

"Luka" <XXXX@XXXXX.COM>wrote
>... it works fine with all but one txt file, and they are
>basically the same, just different data, same format ...

Luka,
Then show us the content of the file around the point
of the problem in hexadecimal or equivalent. This
will help us help you.
Rgds, JohnH




 

Re:TXT delimited (;) filles problem

"Luka" <XXXX@XXXXX.COM>wrote in
Quote
I found the answer to my problem, adding IMEX=1 to the connection
string tells the query to interprete all the data as text. And then I
convert string to number with strtofloat();

The connection string then looks like this:

adoquery1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=g:\temp+';Extended Properties=''text;FMT=Delimited; IMEX=1''';

To answer Del, all the files have numbers with comma;
To answer Chris, schema.ini file is needed if the text file is TAB
delimited, and when delimiter is comma, or some other symbol (like ;)
it is not needed.
Thanks for the IMEX information - I am glad you have solved the problem.
IFAICT this does not seem to be documented by MS with respect to text
import (however, support.microsoft.com/kb/194124/EN-US/ describes
IMEX for import from Excel).
BTW, in my experience, the effect of changing the delimiter in a file
that successfully imports without a schema.ini from comma to semicolon
was to require a schema.ini specifying for that file
format=delimiter(;)
YMMV.
Quote
Thanx everyone for replays, maybe this solution will help you all at
some point

Luka


"John Herbster" <herb-sci1_AT_sbcglobal.net>writes
news:XXXX@XXXXX.COM...
>
>"Luka" <XXXX@XXXXX.COM>wrote
>>... it works fine with all but one txt file, and they are
>>basically the same, just different data, same format ...
>
>Luka,
>Then show us the content of the file around the point
>of the problem in hexadecimal or equivalent. This
>will help us help you.
>Rgds, JohnH
>
>
>
>