Re: Inconsistency of Export to a CSV file.

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 02/25/04 

Date: Tue, 24 Feb 2004 21:03:57 -0500

A CSV file is a txt file with a different extension.
The data is comma delimited is all.

The code is an outline not a copy/paste solution.
You have to do some work! <g> 

-- 
Joe Fallon
Access MVP
"Jack K." <anonymous@discussions.microsoft.com> wrote in message
news:09e501c3fb02$a6cb8d10$a001280a@phx.gbl...
> Hi, Mr. Fallon
>    I will try it. However I wonder about your sample
> coding. I want the file as a CSV file, not TXT file. Is
> your code will give me a CSV file.
>    Also when I typed the Set rs = CurrentDb.OpenRecordset
> (strTable, dbopensnapshot), the word dbopensnapshot did
> not give me the Upper case like dbOpenSnapshot. Why? Do I
> have to have the reference to some DAO dll or what else?
>    Thank you for your help.
> Jack K.
> >-----Original Message-----
> >Here is one way using code:
> >
> >Here is a sample of some general export code:
> >
> >Public Sub ExportDelim(strTable As String, strExportFile
> As String,
> >strDelimiter As String, Optional blnHeader As Boolean)
> >
> >   'strTable is the table or query name
> >   'strExportFile is the full path and name of file to
> export to
> >   'strDelimiter is the field deliminator to use like Chr
> (9) for tab or
> >Chr(44) for comma or ??
> >
> >   Dim fld As Field
> >   Dim varData As Variant
> >   Dim rs As Recordset
> >   Dim intFileNum As Integer
> >
> >   'set recordset on table or query
> >   Set rs = CurrentDb.OpenRecordset(strTable,
> dbOpenSnapshot)
> >
> >   'get file handle and open for output
> >   intFileNum = FreeFile()
> >   Open strExportFile For Output As #intFileNum
> >
> >   If blnHeader Then
> >      'output the header row if requested
> >      varData = ""
> >      For Each fld In rs.Fields   'traverse the fields
> collection
> >         varData = varData & fld.Name & strDelimiter
> >      Next
> >
> >      'remove extra last strDelimiter
> >      varData = Left(varData, Len(varData) - 1)
> >
> >      'write out the header row
> >      Print #intFileNum, varData
> >   End If
> >
> >   'now your data
> >   Do While Not rs.EOF
> >      varData = ""
> >      'concatenate the data row
> >      For Each fld In rs.Fields
> >         varData = varData & fld.Value & strDelimiter
> >      Next
> >
> >      'remove extra last strDelimiter
> >      varData = Left(varData, Len(varData) - 1)
> >
> >      'write out data row
> >      Print #intFileNum, varData
> >
> >      rs.MoveNext
> >   Loop
> >
> >   Close #intFileNum
> >   rs.Close
> >   Set rs = Nothing
> >End Sub
> >
> >-- 
> >Joe Fallon
> >Access MVP
> >
> >
> >
> ><anonymous@discussions.microsoft.com> wrote in message
> >news:1495501c3fa43$c0601120$a301280a@phx.gbl...
> >> Hi, Mr. Fallon
> >>   I did try both of your suggestions but it still has
> the
> >> same problem. Is it possible the export data has the
> date
> >> format caused the problem?
> >>   I am really no confidence in ACCESS. I am using the
> >> ACCESS 2002. Is it possible any fix from MicroSoft.
> >>   Please give me some idea or different way to copy or
> >> export my table to a CSV file only.
> >> Thank you,
> >> Jack
> >> >-----Original Message-----
> >> >You can't code variables like this and expect them all
> to
> >> be Strings. (The
> >> >first 2 are Variants.)
> >> >dim a, sTableName, sBackupFile as string
> >> >
> >> >Split them up on one line each or use the As String on
> >> each of them.
> >> >
> >> >---------------------------
> >> >Try exporting to a local drive instead of a Network
> drive.
> >> >See if it works 100% that way.
> >> >If it does , you know the problem is with the network.
> >> >-- 
> >> >Joe Fallon
> >> >Access MVP
> >> >
> >> >
> >> >
> >> >"Jack K." <anonymous@discussions.microsoft.com> wrote
> in
> >> message
> >> >news:1439601c3f801$7e02bbf0$a601280a@phx.gbl...
> >> >> Hi, all the expert. Please help...
> >> >>   I code the Docmd statement like this:
> >> >>   dim a, sTableName, sBackupFile as string
> >> >>
> >> >>   a = "TblExportToMIP Export Specification"
> >> >>   sTableName = "tblExportToMIP"
> >> >>   sBackupFile = "N:\Child Support Expense
> >> >> Data\MIPExport\Willits2202004.csv"
> >> >>
> >> >>   DoCmd.TransferText acExportDelim, a, sTableName,
> >> >> sBackupFile, False, ""
> >> >>
> >> >> Result: Sometimes work, sometimes don't. I did not
> >> change
> >> >> anything at all. However if I debugged and had it
> >> stopped
> >> >> executing at the DoCmd, it will work every time. I am
> >> >> really confuse what ACCESS has inconsistency.
> >> >>   Is any way I can solve this problem?
> >> >> Thanks so much if you can help me!
> >> >> Jack K.
> >> >

Relevant Pages

  • Re: Easiest Way to do this Importing method?
    ... column of info from the CSV file, then paste it on another sheet in the ... > Function CSVFileToArray(inFile As String) As Variant ... > Dim fileHandle As Integer ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Easiest Way to do this Importing method?
    ... column of info from the CSV file, then paste it on another sheet in the ... > Function CSVFileToArray(inFile As String) As Variant ... > Dim fileHandle As Integer ...
    (microsoft.public.excel)
  • Re: Easiest Way to do this Importing method?
    ... column of info from the CSV file, then paste it on another sheet in the ... > Function CSVFileToArray(inFile As String) As Variant ... > Dim fileHandle As Integer ...
    (microsoft.public.excel.programming)
  • Re: Easiest Way to do this Importing method?
    ... column of info from the CSV file, then paste it on another sheet in the ... > Function CSVFileToArray(inFile As String) As Variant ... > Dim fileHandle As Integer ...
    (microsoft.public.excel.misc)
  • Re: Export to file from Access2K
    ... Public Sub ExportDelim(strTable As String, strExportFile As String, ... strDelimiter As String, Optional blnHeader As Boolean) ... Dim varData As Variant ... Dim intFileNum As Integer ...
    (microsoft.public.access.externaldata)