baffling text file SQL problem

R

RB Smissaert

In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers, but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
 
G

Guest

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


RB Smissaert said:
In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers, but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
 
R

RB Smissaert

Thanks for the reply.
The schema.ini files are the same on both computers and it works fine on
mine.
Maybe because the schema.ini didn't specifically deal with both files the
guessing behaved differently on both computers.

I have fixed this now, although I don't really understand how.
This trouble started after I had changed a routine that writes a RecordSet
to a text file.
I used to do this with looping through an array after making that array with
GetRows, but found it was
somewhat faster to do GetString and write the whole string to text with the
appropriate delimiters.
As far as I could see the resulting text files where exactly the same, but
going back to the old routine solved
my problem.

I will see now if making a better schema.ini would make it possible to use
the slightly faster routine.

RBS


Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


RB Smissaert said:
In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers, but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
 
R

RB Smissaert

Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


RB Smissaert said:
In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers, but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
 
D

Douglas J. Steele

There's little, if any, benefit in worrying about it. Access will only use
as much space as it needs. This is easily proven. A record in Access cannot
exceed 2000 bytes, yet you can created a table with, say, 30 fields, each of
255 bytes. You won't run into an issue until the total usage of all of the
fields exceeds the limit.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


RB Smissaert said:
Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


RB Smissaert said:
In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers,
but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
Click to expand...
 
R

RB Smissaert

Actually this doesn't involve Access at all.
It's all done in Excel. Just thought there was a better chance of getting a
reply in this NG.

RBS

Douglas J. Steele said:
There's little, if any, benefit in worrying about it. Access will only use
as much space as it needs. This is easily proven. A record in Access
cannot exceed 2000 bytes, yet you can created a table with, say, 30
fields, each of 255 bytes. You won't run into an issue until the total
usage of all of the fields exceeds the limit.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


RB Smissaert said:
Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and
this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers,
but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and
PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

In a schema.ini there is a benefit to setting column
widths if you know that the text is fixed width.

In my Access tables, I sometimes use fixed width strings
as way of specifying the width of a column: for example,

if len(sfield) <> rs.fields(1).size then msgbox "import error"

I also use fixed width fields because my reports have
fixed width fields: If users put in strings that are
too wide, they won't be able to see the whole string in
the report.

(david)



RB Smissaert said:
Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


RB Smissaert said:
In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers,
but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
Click to expand...
 
R

RB Smissaert

But would there be a benefit if there are no Access tables involved at all.
I am running SQL on text files only here.
Joining 2 text files on a common field, with the output to
a third text file. For example:

Query4 = "SELECT " & _
"E.ENTRY_ID, " & _
"E.PATIENT_ID, " & _
"E.START_DATE, " & _
"E.READ_CODE " & _
"INTO ENTRY2.txt " & _
"IN '" & BPTablesFolder & "' " & _
"'Text;FMT=Delimited' " & _
"FROM " & _
"PATIENT.txt P INNER JOIN ENTRY.txt E ON " & _
"(P.PATIENT_ID = E.PATIENT_ID)"

RBS

david epsom dot com dot au said:
In a schema.ini there is a benefit to setting column
widths if you know that the text is fixed width.

In my Access tables, I sometimes use fixed width strings
as way of specifying the width of a column: for example,

if len(sfield) <> rs.fields(1).size then msgbox "import error"

I also use fixed width fields because my reports have
fixed width fields: If users put in strings that are
too wide, they won't be able to see the whole string in
the report.

(david)



RB Smissaert said:
Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


In Excel VBA and ADO I am running SQL on 2 text files to join them on a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and
this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers,
but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of this
user, but
run on my computer have the problem, whereas files ENTRY.txt and
PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

If you have fixed width text fields in a text file, the
benefit of specifying the field width is that you don't
have to use the delimiters to delimit the fields.

There is no agreed standard for CSV or delimited text, and
sometimes you get unusual text values that are misunderstood
by the delimited text import. I think that Fixed width import
is more reliable if possible.

(david)


RB Smissaert said:
But would there be a benefit if there are no Access tables involved at
all. I am running SQL on text files only here.
Joining 2 text files on a common field, with the output to
a third text file. For example:

Query4 = "SELECT " & _
"E.ENTRY_ID, " & _
"E.PATIENT_ID, " & _
"E.START_DATE, " & _
"E.READ_CODE " & _
"INTO ENTRY2.txt " & _
"IN '" & BPTablesFolder & "' " & _
"'Text;FMT=Delimited' " & _
"FROM " & _
"PATIENT.txt P INNER JOIN ENTRY.txt E ON " & _
"(P.PATIENT_ID = E.PATIENT_ID)"

RBS

david epsom dot com dot au said:
In a schema.ini there is a benefit to setting column
widths if you know that the text is fixed width.

In my Access tables, I sometimes use fixed width strings
as way of specifying the width of a column: for example,

if len(sfield) <> rs.fields(1).size then msgbox "import error"

I also use fixed width fields because my reports have
fixed width fields: If users put in strings that are
too wide, they won't be able to see the whole string in
the report.

(david)



RB Smissaert said:
Would you know if there is any benefit is setting the width
of a text field if you know what the maximum number of characters is?

RBS

Check the schema.ini on the two computers. The copy
you show looks bad for two reasons:
1) It only shows one table: you have two tables
2) The table it shows is not either of the two tables
you are using.

If you don't have a valid schema.ini in the folder where
the files are, Jet will be guessing what the columns are.
It will guess that the last column is a number, then fail
on values which are not valid numbers, ie values which
contain two decimal points.

(david)


In Excel VBA and ADO I am running SQL on 2 text files to join them on
a
common field to make a third text file.
Connection is with the Jet.OLEDB driver and the connection string is
setup
like this:

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempTables\;" & _
"Extended Properties=Text;"

The 2 text files are joined on a common field called PATIENT_ID and
this
field holds only integer numbers.

This is the layout of the 2 text files:

File PATIENT.txt:

PATIENT_ID
8
9
16
etc.


File ENTRY.txt:

ENTRY_ID,PATIENT_ID,START_DATE,READ_CODE
2152,3,19890420,2464.
2332,7,19920309,246..
2403,7,19900319,2464.
3073,8,19970210,2464.
3074,8,19970210,246..
3076,8,19970210,246A.
3134,8,19911227,2465.
etc.


There is a schema.ini file with this:

[ENTRY2.txt]
ColNameHeader = True
CharacterSet = 1252
Format = CSVDelimited
Col1=ENTRY_ID Integer
Col2=PATIENT_ID Integer
Col3=START_DATE Integer
Col4=READ_CODE Text


And this is the SQL:

SELECT
P.PATIENT_ID,
E.ENTRY_ID,
E.START_DATE,
E.READ_CODE
INTO ENTRY2.txt IN
'C:\TempTables\'
'Text;FMT=Delimited'
FROM
PATIENT.txt P INNER JOIN ENTRY.txt E ON
(P.PATIENT_ID = E.PATIENT_ID)
WHERE NOT E.READ_CODE IS NULL


And this is how it is run:

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText


All this has been used for years and without any problem.
Now however there is one user where the rows of the file ENTRY.txt
with the READ_CODE 246.. are not passed on to the file ENTRY2.txt
even although there should be a join on PATIENT_ID.

Now I could maybe understand this if this was on different computers,
but
the
problem even shows when I run this SQL on the same (my) computer.
So the files ENTRY.txt and PATIENT.txt produced on the computer of
this
user, but
run on my computer have the problem, whereas files ENTRY.txt and
PATIENT.txt
produced
on my computer run fine and the rows with READ_CODE 246.. are passed
on
nicely.

I have examined the files very carefully, but I just can't see any
difference and there shouldn't be
as they are produced by the same software with the same SQL.

Any insight or advice in this very greatly appreciated.


RBS
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

querying text files with sql 1
Importing Large Text File Problem 7
run SQL queries on arrays? 12
Databse crashed if SQL strin runs from CurrentDb.Execute method 2
convert an ADO recordset to a text file that can be queried with SQL 0
Too few parameters error when exporting to text file 4

Top