UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Text File - Csv Without Quotes, Access 2016    
 
   
ws1o
post Oct 2 2017, 08:13 AM
Post#1



Posts: 67
Joined: 30-October 15
From: Central CT USA


Since Microsoft has apparently removed the ability to create and edit Import/Export specs, how can I use VBA code to export a query as a comma-delimited text file (without quotation marks) from a query?

Note...the query is created dynamically in VBA code, and its fields may vary from job to job inside this application.

Thanks for any suggestions!
Go to the top of the page
 
DanielPineault
post Oct 2 2017, 08:18 AM
Post#2


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



What about using: http://www.UtterAccess.com/wiki/ExportToCSV

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 08:30 AM
Post#3


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


If the number of fields in any given export instance varies, then having an Import/Export spec does not do any good anyway; they rely on having a fixed set of columns to define....

What have you tried so far? What were the results? What delimiter do you want to use as a replacement for the quote marks?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 09:02 AM
Post#4


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


I also suspect there's more to the story than what we've seen so far.

For example, it's true that exporting a fixed width text file calls for an import/export specification. Is that your problem here?

And, btw, creating an import/export spec is possible, if not exactly straightforward. The process involves starting with an IMPORT of a text file that matches what you want to export later, and saving the export/import spec thus created.

But, even if it is possible, as previously noted, having one wouldn't help if the number of fields in the exported text file varies each time.

So, how were you doing this previously?

--------------------
Go to the top of the page
 
ws1o
post Oct 2 2017, 09:21 AM
Post#5



Posts: 67
Joined: 30-October 15
From: Central CT USA


I just built this particular application using MS Access 2016 (Office 365). I was originally outputting the file as an Excel file, but the boss said we need to export it as a comma-delimited TXT file with no quotation marks. I thought maybe I could come up with an Export Spec that might work...even if I had to modify and resave it using VBA code, if the query fields change.

So far, the only thing I can think of is to use

CODE
DoCmd.TransferText acExportDelim, , (query name), (filename), True


And then use VBA code to create a Scripting.FileSystemObject and then open the file as a TextStream, then delete all the quotation marks and resave the text file.

I know; it seems very clumsy....

dazed.gif
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 09:28 AM
Post#6


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Well, perhaps that's the work-around you have to use to satisfy a request from the boss. thumbup.gif

Seriously, though. What's his objection to the quote marks? Does he intend to read the text file directly in Notepad?

--------------------
Go to the top of the page
 
ws1o
post Oct 2 2017, 09:49 AM
Post#7



Posts: 67
Joined: 30-October 15
From: Central CT USA


The file is sent off to a client that feeds it into some sort of software that requires no quotation marks, so it's out of our hands.
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 10:18 AM
Post#8


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Ah, that's unfortunate, but not completely unheard of. I once had a project similar to that, in which the import function on the downstream application had rigid, non-standard specifications. I had to export the file something like you're doing line by line, and NOT touch it again with either NotePad or Excel because they would both "fix" the non-standard layout needed and saved in our output file. PITA, but you have to do what you have to do.

In this case, I'd say your solution may not be "elegant", but it is effective.

--------------------
Go to the top of the page
 
cheekybuddha
post Oct 2 2017, 10:50 AM
Post#9


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Here's a simple function using a nifty feature of ADODB recordsets (.GetString() ) that you can use to output a csv file:
CODE
Function CreateCSV(strSQL As String, strFilePath As String) As Boolean

  Dim strCSV As String, iFreeFile As Integer
  
' Open a recordset
  With CurrentProject.Connection.Execute(strSQL)
    If Not (.BOF And .EOF) Then
    ' Assign records as csv data to variable
      strCSV = .GetString(, , ",", vbNewLine)
    End If
    .Close
  End With
' Check whether file exists already
  If Len(Dir(strFilePath)) > 0 Then
  ' Delete the file
    Kill strFilePath
  End If
' Get a file handle
  iFreeFile = FreeFile
' Open the file
  Open strFilePath For Output As #iFreeFile
' Write data to the file
  Print #iFreeFile, strCSV
  Close #iFreeFile
  CreateCSV = (Len(Dir(strFilePath)) > 0)
      
End Function


Call it like:
CODE
  If CreateCSV("SELECT * FROM YourQuery;", "C:\Users\ws1o\Desktop\test.csv") Then
    MsgBox "'C:\Users\ws1o\Desktop\test.csv' has been created"
  End If


EDIT: No need to set a reference to ADO thumbup.gif

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Oct 2 2017, 10:57 AM
Post#10


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Sweet.

--------------------
Go to the top of the page
 
cheekybuddha
post Oct 2 2017, 10:59 AM
Post#11


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Well, it's a lot less code than the example in the wiki!

acclaim.gif

d wink.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
ws1o
post Oct 2 2017, 11:30 AM
Post#12



Posts: 67
Joined: 30-October 15
From: Central CT USA


Update: My boss just found out we can also send them a tab-delimited file...although that option doesn't seem to exist in MS Access VBA code anymore, either.... pullhair.gif
Go to the top of the page
 
ws1o
post Oct 2 2017, 12:23 PM
Post#13



Posts: 67
Joined: 30-October 15
From: Central CT USA


OK, well, here's what I'm doing for now, to create a tab-delimited file that can have varied field names:

(remember to set a Reference to Microsoft Scripting Runtime)

CODE
Public Sub ExportTabDelimited(str_Query As String, str_FileName As String)

    On Error GoTo Err_ExportTabDelimited

    Dim DB       As DAO.Database
    Dim RS       As DAO.Recordset
    Dim Fld      As DAO.Field
    Dim FSO      As Scripting.FileSystemObject
    Dim TS       As Scripting.TextStream
    Dim str_Line As String
    
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset(str_Query, dbOpenSnapshot, dbReadOnly)
    Set FSO = New Scripting.FileSystemObject
    Set TS = FSO.CreateTextFile(str_FileName, True)
        If RS.EOF Then
            ' no records
        Else
                                '   get column names for header
                For Each Fld In RS.Fields
                    If Len(str_Line & "") > 0 Then
                        str_Line = str_Line & vbTab & Fld.Name
                    Else
                        str_Line = Fld.Name
                    End If
                    DoEvents
                Next
            TS.WriteLine str_Line
            DoEvents
            str_Line = vbNullString
                Do Until RS.EOF
                        For Each Fld In RS.Fields
                                If Len(str_Line & "") > 0 Then
                                    str_Line = str_Line & vbTab & Fld
                                Else
                                    str_Line = Fld
                                End If
                            DoEvents
                        Next
                    TS.WriteLine str_Line
                    DoEvents
                    str_Line = vbNullString
                    RS.MoveNext
                    DoEvents
                Loop
        End If

Exit_ExportTabDelimited:

    On Error Resume Next
    TS.Close
    Set TS = Nothing
    Set FSO = Nothing
    Set Fld = Nothing
    RS.Close
    Set RS = Nothing
    DB.Close
    Set DB = Nothing
    DoCmd.SetWarnings True
    On Error GoTo 0
    Exit Sub

Err_ExportTabDelimited:

    Select Case Err
        Case 0
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & vbNewLine & vbNewLine & Err.Description & vbNewLine & vbNewLine & "in procedure ExportTabDelimited", vbInformation, Now
            Resume Exit_ExportTabDelimited
    End Select

End Sub
Go to the top of the page
 
cheekybuddha
post Oct 2 2017, 01:09 PM
Post#14


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Or you can use the function I posted and change this line:
CODE
' ...
      strCSV = .GetString(, , vbTab, vbNewLine)
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ADezii
post Oct 2 2017, 01:23 PM
Post#15



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Why not keep things simple and loose the External Reference? The following Code will create a TAB-Delimited File in the Current DB Path after being passed a single Argument, the name of a Table, Query, or SQL String.
    CODE
    Public Sub ExportTABDelimited(strDataSource As String)
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As Field
    Dim strBuild As String

    Open CurrentProject.Path & "\TAB Delimited.txt" For Output As #1

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset(strDataSource, dbOpenForwardOnly)

    For Each fld In rst.Fields
      strBuild = strBuild & fld.Name & vbTab
    Next
      Print #1, Left$(strBuild, Len(strBuild) - 1)      'Output Header Row
      strBuild = ""      'RESET
      
    With rst
      Do While Not .EOF
        For Each fld In .Fields
          strBuild = strBuild & fld.Value & vbTab
        Next
        Print #1, Left$(strBuild, Len(strBuild) - 1)    'Each Record
        strBuild = "": .MoveNext
      Loop
    End With
      
    Close #1

    rst.Close
    Set rst = Nothing
    End Sub
  2. Sample Procedure Call:
    CODE
    Call ExportTABDelimited("qryEmployees")
  3. Sample OUTPUT:
    CODE
    Last Name    First Name    E-mail Address    Company    Job Title
    Cencini    Andrew    andrew@northwindtraders.com    Northwind Traders    Vice President, Sales
    Freehafer    Nancy    nancy@northwindtraders.com    Northwind Traders    Sales Representative
    Giussani    Laura    laura@northwindtraders.com    Northwind Traders    Sales Coordinator
    Hellung-Larsen    Anne    anne@northwindtraders.com    Northwind Traders    Sales Representative
    Kotas    Jan    jan@northwindtraders.com    Northwind Traders    Sales Representative
    Neipper    Michael    michael@northwindtraders.com    Northwind Traders    Sales Representative
    Sergienko    Mariya    mariya@northwindtraders.com    Northwind Traders    Sales Representative
    Thorpe    Steven    steven@northwindtraders.com    Northwind Traders    Sales Manager
    Zare    Robert    robert@northwindtraders.com    Northwind Traders    Sales Representative
Go to the top of the page
 
cheekybuddha
post Oct 2 2017, 03:45 PM
Post#16


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


I don't know why you bother iterating each field of each record when the ADODB.Recordset.GetString() method does the whole thing in a single call?

Granted, my version didn't have the headers, but that's a simple addition:
CODE
Function CreateDelimFile(strSQL As String, strFilePath As String, Optional strDelim as String = vbTab) As Boolean

  Dim strRet As String, strHeader As String, _
      iFreeFile As Integer, fld As Object
  
  With CurrentProject.Connection.Execute(strSQL)
    For Each fld In .Fields
      strHeader = strHeader & strdelim & fld.Name
    Next fld
    strHeader = Mid(strHeader, Len(strDelim) + 1)
    If Not (.BOF And .EOF) Then
      strRet = .GetString(, , strDelim, vbNewLine)
    End If
    .Close
  End With
  If Len(Dir(strFilePath)) > 0 Then Kill strFilePath
  iFreeFile = FreeFile
  Open strFilePath For Output As #iFreeFile
  Print #iFreeFile, strHeader
  Print #iFreeFile, strRet
  Close #iFreeFile
  CreateDelimFile = (Len(Dir(strFilePath)) > 0)
      
End Function


dontknow.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 06:05 AM