Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

baffling text file SQL problem

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Malaysia Hindus, Sikhs In Malaysia Appalled With University Textbook Ridiculing Their Faiths Breaking News Jun 14, 2016
    Hard Talk Taking One Liners Out Of Context From Gurbani To Allow Intellectual Independence? Hard Talk Apr 23, 2016
    India Protests in India Over the Desecration of a Holy Text Leave Two Dead Breaking News Oct 16, 2015
    Learn Punjabi Convert Punjabi text Language, Arts & Culture Oct 11, 2013
    Sikh Coalition Harmeet Kaur Took the Lead ! First to Respond to Textbook Challenge ! Sikh Organisations May 25, 2013

  3. Guest

    Guest
    Expand Collapse
    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    >
    >
    >
    >
    >
    >
     
  4. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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


    <david@epsomdotcomdotau> wrote in message
    news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >> 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
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >
    >
     
  5. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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

    <david@epsomdotcomdotau> wrote in message
    news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >> 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
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >
    >
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no private e-mails, please)


    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eqe1PBVjGHA.4508@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > <david@epsomdotcomdotau> wrote in message
    > news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    >> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >>> 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
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>

    >>
    >>

    >
     
  7. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:OAyx9bVjGHA.3408@TK2MSFTNGP05.phx.gbl...
    > 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
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eqe1PBVjGHA.4508@TK2MSFTNGP05.phx.gbl...
    >> 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
    >>
    >> <david@epsomdotcomdotau> wrote in message
    >> news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    >>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >>>> 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
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  8. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eqe1PBVjGHA.4508@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > <david@epsomdotcomdotau> wrote in message
    > news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    >> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >>> 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
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>

    >>
    >>

    >
     
  9. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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" <david@epsomdotcomdotau> wrote in message
    news:ujvYKsejGHA.4512@TK2MSFTNGP02.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eqe1PBVjGHA.4508@TK2MSFTNGP05.phx.gbl...
    >> 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
    >>
    >> <david@epsomdotcomdotau> wrote in message
    >> news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    >>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >>>> 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
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  10. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:uD%231pphjGHA.1264@TK2MSFTNGP05.phx.gbl...
    > 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" <david@epsomdotcomdotau> wrote in message
    > news:ujvYKsejGHA.4512@TK2MSFTNGP02.phx.gbl...
    >> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:eqe1PBVjGHA.4508@TK2MSFTNGP05.phx.gbl...
    >>> 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
    >>>
    >>> <david@epsomdotcomdotau> wrote in message
    >>> news:%23JFPa8GjGHA.1508@TK2MSFTNGP04.phx.gbl...
    >>>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>> news:%237KFS4OiGHA.3900@TK2MSFTNGP05.phx.gbl...
    >>>>> 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
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     

Share This Page