[AccessD] Problem Exporting to a CSV file with a Column Name that 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
>