Carriage returns inside an exported CSV file data field.

B

Badroy

I am exporting orders from a Access database connected to a website.
The problem is when I export to a CSV sometimes it generates carriage
returns inside the data field. This ruins the import process into
another database which can be automated. I have to manually open the
text file and strip any carriage returns costing me time.

Is there a way in access to strip these?

"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075 <----
unwanted carriage return here
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234
,Blankman,,SomeTown, SD, 00298
 
D

Dirk Goldgar

Badroy said:
I am exporting orders from a Access database connected to a website.
The problem is when I export to a CSV sometimes it generates carriage
returns inside the data field. This ruins the import process into
another database which can be automated. I have to manually open the
text file and strip any carriage returns costing me time.

Is there a way in access to strip these?

"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075 <----
unwanted carriage return here
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234
,Blankman,,SomeTown, SD, 00298
Click to expand...

I don't know for sure, but I suspect that the fields actually have
carriage returns that were entered by users. If you can't remove them
from the input before they get into the database, you could export a
query that strips them out, using the Replace function. If you're
actually working in Access, you could write a function like this:

'----- start of code -----
Public Function NoCR(SourceField As Variant) As Variant

If Not IsNull(SourceField) Then
NoCR = Replace(SourceField, vbCrLf, "", , , vbBinaryCompare)
End If

End Function
'----- end of code -----

Then your query could wrap each field -- or each field that is subject
to this problem -- in a call to the function; e.g.,

SELECT
FirstName,
LastName,
Address,
City,
State,
NoCR(Zip)
FROM
YourTable;

If you export that query instead of the table itself, the carriage
returns should be stripped from the Zip field.
 
B

Badroy

I am fairly new to access so I am guessing that functions can be
defined in modules. Since I get this error in multiple data fields
(mostly text related) would I do this in the query?

SELECT
NoCR(FirstName),
NoCR(LastName),
NoCR(Address),
NoCR(City),
State,
NoCR(Zip)
FROM
YourTable;
 
D

Dirk Goldgar

Badroy said:
I am fairly new to access so I am guessing that functions can be
defined in modules.
Click to expand...

Right. I'd suggest storing the NoCR() function in a standard module,
probably one that you create (or have already created) for the purpose
of holding various utility procedures. One point to remember, if you're
not familiar with modules and VBA, is that the name of the module must
not be the same as that of any of the functions it contains. Give the
module its own, unique name.
Since I get this error in multiple data fields
(mostly text related) would I do this in the query?

SELECT
NoCR(FirstName),
NoCR(LastName),
NoCR(Address),
NoCR(City),
State,
NoCR(Zip)
FROM
YourTable;
Click to expand...

Yes, though I see I failed to suggest an alias for the new calculated
field. If you don't want these fields to show up in your queries as
"Expr0001", "Expr0002", and so on, you'd best provide names for them;
e.g.,

SELECT
NoCR(YourTable.FirstName) As FirstName,
NoCR(YourTable.LastName) As LastName,
NoCR(YourTable.Address) As Address,
NoCR(YourTable.City) As City ,
State,
NoCR(YourTable.Zip) As Zip
FROM
YourTable;

Note that I added the "YourTable." qualifiers so that I could reuse the
same name without confusing the SQL engine with an apparently circular
reference.
 
Top