Exporting to text a table that has line breaks

[AlanKW]

I have a table that has some fields who's sole purpose is user-information only but formatted for 3 lines of text. Simple example in a warehouse model:

[tt]Cust# Notes
123 Always include extra set of Batteries
Always ship ground
never include catalog[/tt]

We now have to share this table with a different office (There are bigger plans to incorporate everything into one system) So i need to be able to export to a deliminated file (they recommend a |, but I think a comma with text qualifiers will suffice) All the basic solutions are choking on the line breaks and returning multiple lines of data per Customer. based on the line breaks.

Has anyone come across this and/or have thoughts?

 

[smedvid]

I assume the Notes field is a memo field. I would go with the bar symbol as a delimiter. Commas appear commonly through memo fields and you can not readily control what users are entering without placing a code block wrapper to prevent such characters from being entered.

In terms of exporting, try to export to an Excel (xls) file. If this is a one time deal. Otherwise, you may want to write code to:
1. Create a record set
2. Loop through record set line by line.
3. Strip out the rougue characters, CRLF, etc.
4. Construct a string variable to hold data.
5. Output variable by line to a text file.

lcExportFileName1 = "\\ca\comm\EPI0171.CSV"
...
Function CreateExportFile(lcFName As String, lcQName As String) As Boolean
Dim ThisDB As Database
Dim lcCoCode As String
Dim lcTempFile As String
Dim lcString As String
Dim rs As Recordset
Set ThisDB = CurrentDb
Set rs = ThisDB.OpenRecordset(lcQName)
rs.MoveLast
rs.MoveFirst
If rs.RecordCount = 0 Then
CreateExportFile = False
rs.Close
Exit Function
Else
lcTempFile = Dir(lcFName)
If lcTempFile <> &quot;&quot; Then
Kill lcFName 'Delete existing copy!
End If
Open lcFName For Output As #1
Do While Not rs.EOF
lcCoCode = rs(&quot;CO_Code&quot
If lcQName = &quot;qryADP_PayRoll_Final_1&quot; Then
lcString = lcCoCode & &quot;,&quot; & rs(&quot;BATCH_ID&quot & &quot;,&quot; & rs(&quot;FILE_#&quot & &quot;,&quot; & rs(&quot;EARNINGS_4_CODE&quot & &quot;,&quot; & rs(&quot;EARNINGS_4_AMOUNT&quot & &quot;,&quot; & rs(&quot;EARNINGS_4_CODE2&quot & &quot;,&quot; & rs(&quot;EARNINGS_4_AMOUNT2&quot
Else
lcString = lcCoCode & &quot;,&quot; & rs(&quot;BATCH_ID&quot & &quot;,&quot; & rs(&quot;FILE_#&quot & &quot;,&quot; & rs(&quot;PAY_#&quot & &quot;,&quot; & rs(&quot;TAX_FREQUENCY&quot & &quot;,&quot; & rs(&quot;EARNINGS_5_CODE&quot & &quot;,&quot; & rs(&quot;EARNINGS_5_AMOUNT&quot
End If
Print #1, lcString
rs.MoveNext
Loop
Close #1
lcTempFile = Dir(lcFName)
If IsNull(lcTempFile) = False Then
CreateExportFile = True
Else
CreateExportFile = False
End If
rs.Close
End If
Exit Function
End Function

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[AlanKW]

I appreciate the real-world example. My experience with [tt]rs.[/tt] commands is minimal, but I think I can adapt.

I presume that IcFName is the filename.

I will poke at this the next few days (@#$ emergencies)

thanks again.

 

[smedvid]

Correct, the function accepts (2) parameters. A file name that will be created (i.e. the output text file) and a query name which is used to create the content of the text file.

U would call the function as follows...

lcOutputName = &quot;TestOutput.txt&quot;
lcQueryName = &quot;qryTestQuery&quot;
If CreateExportFile(lcOutputName, lcQueryName)=True
msgbox(&quot;Ok&quot
Else
msgbox(&quot;NOT Ok&quot
Endif

htwh,

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[AlanKW]

I got it!
I suited your code and added in a function from Terry Kreft. I took it out of a function as I only needed limited onClick use for it.

[tt]
On Error GoTo err

Dim ThisDB As Database
Dim lcFName As String
Dim lcString As String
Dim strLF As String
Dim strCR As String
Dim rs As Recordset
Dim strDelim As String
Dim strSep As String
Dim strNotes as string
Dim Notes As String

lcFName = &quot;C:\DataDump\MyFile.txt&quot;
strLF = Chr(13) & Chr(10)
strDelim = &quot;|&quot;
strSep = &quot; :&quot;

Set ThisDB = CurrentDb
Set rs = ThisDB.OpenRecordset(&quot;Qry_AllCustomers&quot
rs.MoveLast
rs.MoveFirst
Open lcFName For Output As #1
'Text Header
Print #1, &quot;CustID&quot; & strDelim & &quot;CustName&quot; & strDelim & &quot;CustCompany&quot; & “CustNotes”

Do While Not rs.EOF
If IsNull(rs(&quot;CustNotes&quot) = False Then strNotes = SearchAndReplace(rs(&quot;CustNotes&quot, strLF, strSep)

lcString = rs(&quot;CustID &quot & strDelim & rs(&quot;CustName&quot & strDelim & rs(&quot;CustCompany &quot & strDelim & strNotes
Print #1, lcString
rs.MoveNext
Loop
Close #1
rs.Close
Exit Sub

err:
MsgBox err.Number & &quot; &quot; & err.Description & &quot; &quot; & err.Source
Close #1
rs.Close

End Sub [/tt]

AND the Search and Replace function I can't find the original post where I found it, but its come in handy a few times now.

[tt]Function SearchAndReplace(ByVal Source As String, ByVal Find As String, _
ByVal Replace As String, Optional WholeWord As Boolean = False) As String
'*******************************************
'Name: SearchAndReplace (Function)
'Purpose: Finds each occurence of one string inside _
another and replaces it with a third string
'Author: Terry Kreft
'Date: March 01, 1999, 11:06:07 AM
'Called by: Any
'Calls: None
'Inputs: Source - String to search and replace within
' Find - String to be replaced
' Replace - String to be inserted
' Wholeword (Optional) - If False (Default) _
replaces all occurences, if True replaces _
only whole words
'Output: Parsed string
'*******************************************

Dim intinstr As Integer
Dim intLenFind As Integer
Dim strTemp As String
If WholeWord = True Then
Find = &quot; &quot; & Trim(Find) & &quot; &quot;
Replace = &quot; &quot; & Trim(Replace) & &quot; &quot;
End If
intinstr = 1
intLenFind = Len(Find)
Do
intinstr = InStr(Source, Find)
If intinstr > 0 Then
strTemp = strTemp & Left(Source, intinstr - 1) & Replace
intinstr = intinstr + intLenFind
Source = Mid(Source, intinstr)
End If
Loop While intinstr > 0
If Len(Source) > 0 Then strTemp = strTemp & Source
SearchAndReplace = strTemp
End Function[/tt]

Thank you SmedVid!