Exporting to CSV delim (quote and concat issues)

[czarbjones]

Hey Gurus,

I'm trying to export a query (QueryDataExpor t) to a comma delim CSV file. The file name should look like this "C:\DSF_2006112 7_124211.csv" however it is exporting as "C:\DSF_.cs v".

Also all of the text fields and header are being exported with quotes "" around them and I'm unsure how to turn this off. Do I have to actually have to create a rst and then code it to go through and remove these manually or is there a function switch of some kind?

Here's what I've done:

Private Sub cmdExport_Click ()
On Error GoTo Err_cmdExport_C lick
Dim MyDate
Dim MyFile
Dim Mystring

MyDate = Now()
Mystring = Format(MyDate, "yyyymmdd_hhmms s")
MyFile = "C:\Customer\DS F_BWS_" & My_String & ".csv"

DoCmd.TransferT ext acExportDelim, , _
"QueryDataExpor t", MyFile, True

Exit_cmdExport_ Click:
Exit Sub

Err_cmdExport_C lick:
MsgBox Err.Description
Resume Exit_cmdExport_ Click

End Sub

Which outputs as:

"Centre","Date" ,"Dept","Sales" ,"Count","Units ","Division "
"1111","2006112 6","0",2,"0","0 ","10",0
"1112","2006112 6","0",4,"0","0 ","10",0

However I'd like it to look like this:

Centre,Date,Dep t,Sales,Count,U nits,Division
1111,20061126,0 ,2,0,0,10,0
1112,20061126,0 ,4,0,0,10,0

Thanks for any help you can provide

Cheers,
Cz. :)

[czarbjones]

P.S. I'm trying to get it to export the data into a file with the date and time as part of the file name (which is not working).

Help please!

[PEB]

Hi,

I've seen u helped people today! What i can say about this command - transfer text! it doesn't started at all for me!

You're lucky to do this!

So i've created my own export function that i'll give you:

Function create_file_from_SQL_SEP(SQL, File_name, sep) As Integer
'?create_file_from_SQL_SEP("Joyau02", "D:\AJoyau.EXP", "; ")
On Error Resume Next
Dim line
Dim mydb As Database
Dim myr As Recordset
Dim i
Dim myupd_last, myupd_current

Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)

Open File_name For Output As #1
' Close before reopening in another mode.

'Stop
myr.MoveLast
myupd_last = myr.RecordCount
    myr.MoveFirst
    For myupd_current = 1 To myupd_last
    
        line = ""
        For i = 0 To myr.fields.Count - 1
            If i = 0 Then
                line = ns(myr(i))
            Else
                line = line + sep + ns(myr(i))
            End If
        Next i
        Print #1, line
        myr.MoveNext
    Next myupd_current

myr.Close
Close #1

mydb.Close
create_file_from_SQL_SEP = True

End Function

so to do your export you write:

i=create_file_f rom_SQL_SEP("My table", "MyFile", ",")

and no more pbs man!

:)

[NeoPa]

PEB,

It's a good idea to use FreeFile() rather than to hard-code 1 as the file number.
Neat function though.

CzarBJones (very royal),
Your Format string would be better written as "yyyymmdd_hhnns s", but yours should still work perfectly.
I can't see why it would resolve to blank :S.
As PEB says, CSV format is very uncontrollable. Certain settings cannot be changed.

[PEB]

PEB,

It's a good idea to use FreeFile() rather than to hard-code 1 as the file number.
Neat function though.

changed.

10x Ade i've never used this function and it gave me an error the hard coding of a file number!

I'll use it!

:)

[czarbjones]

Thanks all for your help and comments.

Peb: I really appreciate the help and your time on the function. It looks very useful but I am getting a compile error at "ns" on the following line:

line = ns(myr(i))

Sub or Function not defined. Is this your own function, an add in or something I can add to my library.

Thanks in advance,
Cz :)

[MMcCarthy]

Thanks all for your help and comments.

Peb: I really appreciate the help and your time on the function. It looks very useful but I am getting a compile error at "ns" on the following line:

line = ns(myr(i))

Sub or Function not defined. Is this your own function, an add in or something I can add to my library.

Thanks in advance,
Cz :)

Cz

I think PEB meant nz not ns.

[czarbjones]

Ahhhh and the light goes on *smile*. Thanks I'll give it a try.

Cheers,
Cz.

[MMcCarthy]

Ahhhh and the light goes on *smile*. Thanks I'll give it a try.

Cheers,
Cz.

It my look for a substitute value if null. If so try ...

 nz(myr(i), "")

[Killer42]

Your Format string would be better written as "yyyymmdd_h hnnss", but yours should still work perfectly.
I can't see why it would resolve to blank[/b]

:) I've highlighted the difference as it's very easy to miss.

However, we don't actually know (with the possible exception of czarbjones) that it is resolving to blank. All we know is that the TransferText doesn't appear to have done what was expected. This might sound like a "distinctio n without a difference", but in debugging you have to watch every little detail. I'd be checking the values passed to it, very carefully. Including the order - did everyone verify the number of commas, etc?

As PEB says, CSV format is very uncontrollable. Certain settings cannot be changed.

czarbjones, are you sure you really need to get rid of the quotes?

And does anyone know, can you set up an export spec in Access, then use it in the TransferText?

[PEB]

Thanks all for your help and comments.

Peb: I really appreciate the help and your time on the function. It looks very useful but I am getting a compile error at "ns" on the following line:

line = ns(myr(i))

Sub or Function not defined. Is this your own function, an add in or something I can add to my library.

Thanks in advance,
Cz :)

Sorry man this is my function also:

Function ns(Stri) As String
On Error Resume Next
Dim result As String
If IsNull(Stri) Then
result = ""
Else
result = Stri
End If
ns = result
End Function

I've ommited to see that i've used it!

U can omit it as u want!

[MMcCarthy]

:)
And does anyone know, can you set up an export spec in Access, then use it in the TransferText?

Yes you can.

Sorry Killer, I haven't read for enough back in this thread. If that is the requirement then yes it is standard practice.

Mary

[Killer42]

Yes you can. ...it is standard practice.

Seems as though that would solve half the problem, then. As I recall, there were two parts.

• File name was being garbaged somehow
  This seems like a fairly straightforward debugging job.
• Unwanted quotes around the fields written to the file.
  Predefining an export specification and using it in the TransferText action would probably resolve this.

So I don't see any need for extra functions. Not that I'm casting aspersions on your function, PEB. It just doesn't seem necessary in this case.

[NeoPa]

And does anyone know, can you set up an export spec in Access, then use it in the TransferText?

Killer,

There is an example of its use in the code I sent you in Sub ExportFile.
It's not obvious as I was unable to send the specs themselves without sending the whole db, but that's what it's doing ;).

[czarbjones]

Hey guys, thanks for all the comments and help.

I tried the function and it created a blank file with no text inside. Peb I then added your additional function for "ns" but same result.

Killer you are correct that original the problem was not a blank file, it was just that the data had quotes around it which I wanted to remove. Sadly yes I have to remove the quotes as that is the spec I've been given and they wont change it despite my request.

I've managed to identify and fix the file name issue that i had, but still need to remove the quotes.

Note sure what you meant by using an "export function" can you provide a sample :)

Cheers,
Cz