Writing to text file with line feeds / carriage returns (AC2007) (1 Viewer)

[AOB]

Hi guys,

I have a function which takes a recordset and writes the contents to a text file. One of the fields in the recordset is a free format text field, the contents of which originate from a text box on a form. Occasionally, a user can hit the 'Enter' key to add a new line (carriage return) to the note within the text box.

The problem is, when the output text file is subsequently imported to Excel, the carriage returns within that text field serve to move the next piece of data to the next row (even though the CR is within the double-quote text-qualifier in the text file)

I want line breaks within the text field to be retained within the cell.

One option would very simply be to replace the carriage returns with simple spaces in the write function. I could do that quite easily. However, I would prefer to retain whatever the user has entered into the text box, including line breaks, if at all possible?

So if the user broke his free format 'note' into multiple lines (for whatever reason), it would similarly appear as multiple lines in the same cell (and in the same row / record) in the Excel worksheet.

Any suggestions as to the easiest way to do this? I had hoped wrapping the text fields in double-quotes would serve to solve the problem but it doesn't...

I could replace the carriage return with some obscure character during the write, then switch it back during the Excel import, but that seems a little clunky (plus Murphy's Law dictates that no matter how obscure a character I choose, somebody, at some point, will genuinely use it, and it will get switched out. Which means I'd have to use a non-printing character? In which case, which one??...)

Or, could I simply replace the carriage return with a line feed? (Or is it the other way around? I always get carriage returns and line feeds mixed up!)

Pretty sure there's a simple way around this but I always get confused as to precisely which ASCII character the 'Enter' key produces when keying into a text box.

Thanks in advance!

AOB

 

[spikepl]

Google says Excel wants Chr(10) so try replace vbcrlf by Chr(10)

 

[vbaInet]

There must be a reason why you're looping through a recordset in order to save to a text file? Why not use the TransferText method which would work out much quicker? Or an export spec?

With your current method try:
1. Export to Excel (i.e. CopyFromRecordset)
2. Save As txt

Again, it should work out quicker than looping through a recordset.

 

[AOB]

Thanks guys

spikepl - cheers for clarifying - I've added a line to replace vbCrLf with vbLf (which I believe is also Chr(10), correct me if I'm wrong?...) when writing the text fields. Still waiting to test it, had some separate issues to deal with yesterday, hence the delay in responding...

vbaInet - appreciate the advice and take it on board. The reason I use a recordset is that the process of writing to the text file varies depending on certain conditions. So it's easier for me to dynamically construct a SQL statement and read the data into a recordset, then write from the recordset to the text file. I don't actually loop through the recordset; there is only ever one record in there to be written at a time. So it's still pretty quick. There is also the fact that multiple users will write to the same text file (I believe you helped me with a concurrency question a few weeks back relating to this?) so it's more stable for me to use the scripting object with some appropriate error handling to write to the target file from the recordset. But, as always, advice taken on board and much appreciated!

Thanks guys, as ever

AOB

 

[vbaInet]

That's fine, as long as you're not looping through a recordset. And yes I remember that problem. Obviously you've developed something?

You can still construct SQL and export the constructed SQL using the TransferText method and it will handle the line breaks accordingly. Otherwise, the other method mentioned (i.e. CopyFromRecordset and SaveAs) is also viable.

 

[AOB]

Okay guys I finally managed to test this and I'm still getting the same (or similar) problem

When I write the contents of the recordset to the text file, the line feed is still creating a new line in the text file (rather than just marking it as a line feed character within a text-qualified field)

Meaning when I subsequently import the text file into Excel, the record is split across multiple rows, and the text field which contains the line break is not contained in a single cell.

For clarity, here is the code used to write the contents of the recordset to the text file :

Dim rst as Recordset     [COLOR=darkgreen]' Recordset object containing the record to be written[/COLOR]
Dim objFile As Object    [COLOR=darkgreen]' FSO file object created via .OpenTextFile[/COLOR]
Dim strLine As String    [COLOR=darkgreen]' String variable to construct the line prior to writing[/COLOR]
...
With rst
 
  .MoveFirst
 
  Do Until .EOF
 
    For i = 0 To .Fields.Count - 1
 
      Select Case .Fields(i).Name
 
        Case "Comment1", "Comment2"   [COLOR=darkgreen]' The two comment (text) fields which need to [/COLOR]
[COLOR=darkgreen]                                      be wrapped in double-quotes for text-qualification[/COLOR]
 
          strLine = strLine & Chr(34) & Replace(Nz(.Fields(i).Value, ""), vbCrLf, vbLf) & Chr(34) & "|"
 
        Case Else
 
          strLine = strLine & Nz(.Fields(i).Value, "") & "|"
 
      End Select
 
    Next i
 
    strLine = Left(strLine, Len(strLine) - 1)  [COLOR=darkgreen]' Remove the final pipe delimiter[/COLOR]
 
    objFile.WriteLine strLine                  [COLOR=darkgreen]' Write the string to the text file[/COLOR]
 
    .MoveNext                                  [COLOR=darkgreen]' Technically redundant as by design only one record[/COLOR]
 
  Loop
 
End With

If there are no line breaks in either of the comment fields, the text file writes fine, for example :

ABCD|1234|25-Jun-2015|"Comment1 with no line breaks"|"Comment2 with no line breaks"|12345.67

But if there are, that example would look like this :

ABCD|1234|25-Jun-2015|"Comment1 with a line break
here"|"Comment2 with a line break
here"|12345.67

How can I retain the presence of the line break without actually creating a new line in the text file, which messes up the formatting process when importing to Excel? Is there another ASCII character I can use (which wouldn't require further substitution when importing back into Excel?)

Thanks!

AOB

 

[namliam]

If you want to retain returns then dont export to a text file, the text file will break period

Export it directly to excel even if it is the 97 version

 

[AOB]

Thanks namliam

I kinda need to store this information as text as it's basically an audit log for all the activity that passes through the system. I only use Excel to tidy it up a bit for reporting purposes - I don't particularly want to have lots of Excel spreadsheets building up over time. I also don't want to have to create an Excel object and open a saved workbook every time somebody makes a change (which happens fairly frequently throughout the day) as this is a lot slower than simply spitting the raw text to a text file and then worrying about the formatting only when the log needs to be reportable (i.e. reviewed) - which won't be very often. Bear in mind, multiple users as well, so this log will be updated quite a bit during the day - I don't want to slow down the process by introducing a lag every time anybody adds or updates a record.

Which makes me think I'm back to what I was hoping to avoid - the introduction of a non-printing character in place of the carriage return / line feed, which I can then substitute back to being a line feed once the data has been imported into Excel.

Any suggestions for a suitable placeholder character? i.e. one that is unlikely to be required in general use and won't be confused for anything else?

 

[Minty]

We use ¶ chr(182) for a similar purpose

 

[AOB]

Nice. Thanks Minty, let me play around with that!

 

[namliam]

Why not log changes to a table instead of text files?

 

[AOB]

A very valid point, namliam, and one which I had considered. I do have some more rudimentary audit trails logged straight to tables as a matter of course.

However, this particular activity is quite extensive, and I need to record all attributes as of the time of action (not as they are 'now') - so I can't make use of table joins to normalise the data (as a value on one day may change due to a valid update in the underlying static at some future point. e.g. The name on an account may change, or some other characteristic - I need to see what it was at the time it was logged, not based on the current static setup)

Therefore, such a table would need quite a large number of fields, most of which will be text fields (not simply ID's to link to related tables) and there will be a lot of records posted to it. Right now I'm getting around 3,000 logged entries per month but this may rise. That's not very sustainable and I don't want to bloat my DB with these records which should be non-editable anyway (it's just an activity log) which might impact performance for the 'genuine' data which is reviewed and enriched each day.

Hence my preference is to take it out of the DB altogether and house it in text form. All I need is a record of it and the ability to review that record retrospectively.

Appreciate I could set up a second BE purely for logging purposes and house the records there, but then I have to add that second BE to my existing maintenance protocols and handle all of that.

It's a lot easier just to spit out a text file (I generate a new one each month) and takes up a lot less space on the network.

Does this make sense?

 

[namliam]

if formatting is such an issue, then no it doesnt make sence.
I dont see how you would need multiple columns, just one column with a memo field to hold what you would otherwize write into the tekst file.... with some additional columns to keep track of who, when and where.

 

[AOB]

Appreciate that namliam but it's still an additional table which will compromise the available space in the BE. I'm trying to keep the BE as lean as I possibly can, for longevity and scalability. The volume of importable data is due to increase ten-fold in the coming weeks.

I'm constantly trying to keep my BE on a strict diet so any savings I can make are most welcome.

This method, while admittedly clunky, works for the purpose it is intended, without using up any valuable space for the underlying records. It's not an essential part of the overall process; it's just a log. The only issue I have with the method is this minor irritant. If the worst comes to the worst, I can restrict the use of carriage returns in commentaries at point of entry. But if I can find a workaround, and allow users to continue to use them, then so much the better?

 

[AOB]

Well this is odd...

So I've changed my write procedure to replace carriage returns with Chr(182) / "¶"

But when I look at the text file having logged an entry with a carriage return, the replacement character is "Â" / Chr(194)

So when I then try to identify instances of Chr(182) in the columns in Excel - to replace them with line feeds within the cell - obviously, I can't find any?

You come across this at any stage Minty?

Any ideas why Chr(182) is being switched for Chr(194) as part of that write process?

(The obvious question, to me, would be whether or not the text file being opened via .OpenTextFile, was set to open as ASCII - I can confirm it is...)

 

[AOB]

Correction - the pilcrow ("¶") character is writing to the text file correctly

The issue appears to be the subsequent import to Excel

Investigating further...

 

[AOB]

Solved - the default .TextFilePlatform for the data connection in Excel is 850 / MS-DOS (PC-8)

By changing this to 1252 / Windows (ANSI), the pilcrow character is maintained

Dim appExcel As Object      [COLOR=darkgreen]' Excel Application object[/COLOR]
Dim objWorkbook As Object   [COLOR=darkgreen]' Excel Workbook object[/COLOR]
Dim objWorksheet As Object  [COLOR=darkgreen]' Excel Worksheet object[/COLOR]
Dim con As Variant          [COLOR=darkgreen]' Excel Connection object[/COLOR]
...
Set appExcel = CreateObject("Excel.Application")
 
With appExcel
 
  Set objWorkbook = .Workbooks.Add
 
  With objWorkbook
 
    Set objWorksheet = .Sheets.Add
 
    With objWorksheet.QueryTables.Add(Connection:="TEXT;" & strPath, & _
                                      Destination:=.Range("$A$1"))
 
      [COLOR=darkgreen]' Make a data connection and import the log to the specifications for that log[/COLOR]
 
      .Name = "LogConnection"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = 1                    [COLOR=darkgreen]' xlInsertDeleteCells[/COLOR]
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      [COLOR=red].TextFilePlatform = 1252[/COLOR]             [COLOR=darkgreen]' Windows (ANSI)[/COLOR]
      .TextFileStartRow = 1
      .TextFileParseType = 1               [COLOR=darkgreen]' xlDelimited[/COLOR]
      .TextFileTextQualifier = 1           [COLOR=darkgreen]' xlTextQualifierDoubleQuote[/COLOR]
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = False
      .TextFileSpaceDelimiter = False
      .TextFileOtherDelimiter = "|"
      .TextFileColumnDataTypes = arrDataTypesInteger   [COLOR=darkgreen]' defined prior[/COLOR]
      .TextFileTrailingMinusNumbers = True
      .Refresh BackgroundQuery:=False
 
    End With
 
    [COLOR=darkgreen]' Drop the data connection[/COLOR]
 
    For Each con In .Connections
      con.Delete
    Next con
 
  End With
 
End With

 

[Minty]

Erm - no - in fairness we use it internally within Access, not Excel, so it could be an Excel interpretation issue or an export weirdness.

Maybe try a more normal character "¬" that is is equally unlikely to be used in typing...

Edit - glad you sorted it! We've used that character quite a lot so was surprised you were having n issue