[AccessD] Problem Exporting to a CSV file with a Column Namethat has a #

All,

Thanks for the advice and insights.

I am planning to experiment with the ideas posted.

I like the sounds of the "roll your own" export (I am a product of the
1960s :-)

I have never used Access to tweak a flat file, so that also is something
that sounds interesting, at least to have in the bag-of-tricks.  (If
someone has a simple example that they are willing to share, I would
appreciate it).

Thanks again,
Brad




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, April 20, 2011 4:54 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem Exporting to a CSV file with a Column
Namethat has a #

Hi Brad,

Listen to Gustav ;-)

Especially for import.  You willl find all sorts of CSV file formats.
The biggest problem is the 
inconsistent use of "quotes".  Sometime all dates and strings are
quoted, sometimes they are 
only quoted if they contain a comma, sometimes you will get numbers
quoted because they 
are formatted to include commas and others which are not because they
don't include 
commas, sometimes you will get embedded real quotes represented as
double quotes.  You 
can get all of these variations in a single file.  The built in Import
function can't handle many 
of them.

What I do is import the line, strip the quotes and convert it into Tab
delimited strings without 
extraneous quotes.  

Function ConvertLine(strIn As String) As String
    'Converts CSV(either quoted or non-quoted text) to TAB delimited
    Dim inquotes As Boolean
    Dim strC As String
    Dim x As Long

   'Change real delimiter commas to Tabs
   'ie those that aren't inside quotes
    For x = 1 To Len(strIn)
        strC = Mid$(strIn, x, 1)
        If strC = "," And Not inquotes Then
            Mid$(strIn, x, 1) = Chr$(9)
        End If
        If strC = """" Then
            inquotes = Not inquotes
        End If
    Next

    ' there may be real quotes escaped as "paired" quotes
   ' so change them temporarily to a non print character
   strln = Replace(strln,"""""",chr$(1))

    'get rid of all the remaining quotes
    strln = Replace(strIn, """", "")

    're-instate the escaped quotes
    ConvertLine = Replace(strln,Chr$(1),"""")
End Function

Then I split the record using  
...
Line Input #ff strInputLine
strData() = Split(Convertline(strInputLine),Chr$(9))
...

Once you have done that, you can convert the strings to other data types
as required.

-- 
Stuart

On 20 Apr 2011 at 9:59, Gustav Brock wrote:

> Hi Brad
> 
> Listen to Stuart. 
> Once you have this set up you are in total control and will never look
> back. The same goes for importing text files.
> 
> /gustav
> 
> 
> >>> stuart at lexacorp.com.pg 20-04-2011 05:40 >>>
> The best solution is not to use the built in Access export routine. 
> Roll your own and you can put anything you want in the file.
> 
> Aircode:
> 
> Function Export() as Long
> Dim rs as DAO.Recordset
> Dim ff as long
> ff = Freefile
> set rs = CurrentDB.Openrecordset("qryMyExport")
> ff = Freefile
> Open "myExportFile.csv" for Output as #ff
> Print #1,"First fieldname,Employee #,Third fieldname"
> While not rs.Eof
>    Print #1 rs(0) & "," & rs(1) & "," & rs(2)
>    rs.movenext
> Wend
> Close #ff
> End Function
> 
> -- 
> Stuart
> 
> On 19 Apr 2011 at 17:23, Brad Marks wrote:
> 
> > All, 
> > 
> > We are just starting to use an Access 2007 application to feed data
> > to another outside system via a CSV file. 
> > 
> > This outside system is very rigid and we need to use exact field
> > names on the CSV file that we are creating.
> > 
> > The catch is that one of the fields is call "Employee #".
> > 
> > We set up an Export Specification (called Export-Query1 for our
> > testing) and we are using the following command to do the export
> > 
> > DoCmd.RunSavedImportExport "Export-Query1"
> > 
> > When we run the application, everything works nicely, except the
> > field named "Employee #" is somehow being renamed "Employee ." in
> > the generated CSV file.
> > 
> > We have run a number of tests and it looks like we have stumbled
> > upon either a "bug" a "feature", or something that we just don't
> > understand.
> > 
> > Is there a way to force Access to not replace the "#" with a "."  ?
> > 
> > Thanks for your help,
> > 
> > Brad 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.