Results 1 to 12 of 12
  1. #1
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161

    Missing crlf read from text file.

    I have created a text file from excel using vba code :
    Open myFile For Output As #1 Print #1, Cells(i, 26)



    The cell contains several lines of text, with each line created using the usual alt return.

    I then read this into my access database using :
    intFile = FreeFile
    Open myFile For Input As #intFile

    Do Until EOF(1)
    Line Input #1, strTextLine
    Loop

    However, Access seems to be treating the data as a single line rather than several lines. It seems to be ignoring the crlf.

    Incidentally, when I created myFile manually using notepad Access read each line correctly.

    Looking at the manual and excel created text files side by side I can see no difference and both show the separate lines in notepad.

    Anyone got any idea what is going on ?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    just a wild guess since you don't have any other replies yet
    each line created using the usual alt return.
    Except that in Access, a line wrap is ctrl+return, not alt+return.
    Sorry I can't be more helpful on this one.

    EDIT: except possibly to say that the Ascii value should be 13 - at least on the Access side.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Thanks Micron,
    I suspect it is something like that. When I created the original file, I cut and pasted directly from the excel cell into notepad. This must have somehow replaced the alt+return with ctrl+return.
    I now have two identical looking notepad files, one which works and one which does not. If I manually add blank lines to the one that works, these blank lines show up when read by access. If I manually add blank lines to the other one the lines do not show up in access. Somehow notepad must know what the file is using for carriage return - if you get what I mean.
    Got to find a simple way to make this work as I have hundreds of excel cells with multiple lines of text that I want to convert to text files to be read by access.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code looks a little wonky.....

    This is my code for working with text files

    Code:
      '  open read text file
       RF = FreeFile
       Open FileToRead For Input As #RF
    
    
    
       '  open write text file
       WF = FreeFile
       FileToWrite = CurrentProject.Path & "\FundTransfer_" & Format(dPayment, "yyyy-mm-dd") & ".txt"
    
       Open FileToWrite For Output As #WF
       Print #WF, "END_PK, Division, EmpID, SSN, LName, FName, MI, Amount, Transfer_Date, Comment"
    
       Do Until EOF(RF)       ' <<-- NOT 1
          Line Input #RF, TextLine    ' Read line into variable.     ' <<-- NOT 1
          ' the magic happens here!!
       Loop
    
    
       Close #RF
       Close #WF

    You are using 1 instead of "intFile"
    If you use 1, you are hard coding the file handle number and not using the value from FreeFile.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe try something like this on the notepad text. Not sure if you'd have to adapt to open the np file and look at the text or if you can just look at a table field where you've pasted it. If I understand, when pasted in a field one will "work" and the other not. I used a form here, with line wrapped values of 11, 22 and 33.
    Code:
    Sub testForLineWrap()
    Dim strInput As String
    Dim i As Integer
    
    strInput = Forms![frmMyFormName].MyFieldName
    For i = 1 To Len(strInput) - 1
    Debug.Print Asc(Mid(strInput, i, 1))
    Next
    
    End Sub
    I get
    49
    49
    13
    10
    50
    50
    13
    10
    51

    13 is the ascii for carriage return and 10 is for newline
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Thanks Steve,
    Had not spotted the bit about using 1 instead of "intFile". I had copied the code from somewhere else on the internet and it seemed to work, so did not realise it was not correct. Just got to solve the CRLF issue now.

  7. #7
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Thanks Micron,

    Have done as you suggested and examined the data in the text files. Basically the one that works has crlf (13,10) at each line end and the one that does not just has lf (10), so Access treats the whole lot as just one line.

    This means several things :-
    1) When you put a new line in an excel cell using alt return, it just inserts line feed (10) into the cell.
    2) If you cut and paste from an excel cell, (10) is replaced by (10,13).
    3) Text files with (13,10) look exactly like text files with (10) in notepad.
    4) If you add linefeeds using notepad when the text file already has (13,10) the line feeds are added as (13,10)
    5) If you add linefeeds using notepad when the text file already has (10) the line feeds are added as (10)

    How many people know all that?

    To solve my problem, I think I will replace all (10) characters with (10,13) before I write the excel cell contents to the file so the text file is using the (10,13) method.
    Have not tried this yet, but I think it should work.

  8. #8
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Tried this and replacing (10) with (10,13) in excel created extra line feeds in the text file as shown by notepad.
    So, I just replace (10) with (13) and this works. Have now created my hundreds of text files from the excel cells and they are all read correctly by access showing the correct line breaks.
    Problem solved.

  9. #9
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Addendum
    Actually replacing (10) with (13,10) works as well. What does not work is (10,13).
    Not sure if it is preferable to have just (13) as the line feed or both (13) and (10), Does not seem to matter to notepad or to Access.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting that the order matters. Also, pretty sure that is the opposite of what a typewriter does, which is what this all stems from. My inclination would be to use both characters just in case it will matter somehow down the road - such as if Word is ever involved.
    Thanks for posting the results of your tests.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just had a thought. Wondering if your Excel data is single character in one column per cell as in
    A
    B
    C
    in which case a line feed would suffice. What would Excel insert if you had
    AAA
    BBB
    CCC
    Or is your data not single character per line?

  12. #12
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    No, the data is rows of text. A description of an object in a collection of mine.
    I think the reason Access does not mind is that it is looking for (13) to terminate each line read in by Line input.
    If there is a (10) after that on the line it just ignores it.
    If the row has (10,13), it reads the (10) and puts an extra new line in before it gets to the (13) to terminate the line input.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-26-2017, 02:09 PM
  2. Read excel/text file thro VBA
    By shabar in forum Programming
    Replies: 2
    Last Post: 01-29-2013, 04:41 AM
  3. Read text file with LF
    By SteveG in forum Programming
    Replies: 8
    Last Post: 12-24-2011, 09:57 AM
  4. Err 7-out of memory, read text file
    By dssrun in forum Programming
    Replies: 4
    Last Post: 12-04-2011, 01:06 AM
  5. CRLF automatically appended in string concat?
    By tnt in forum Programming
    Replies: 2
    Last Post: 10-04-2011, 09:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums