469,356 Members | 1,988 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Exporting to CSV delim (quote and concat issues)

Hey Gurus,

I'm trying to export a query (QueryDataExport) to a comma delim CSV file. The file name should look like this "C:\DSF_20061127_124211.csv" however it is exporting as "C:\DSF_.csv".

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_Click
Dim MyDate
Dim MyFile
Dim Mystring

MyDate = Now()
Mystring = Format(MyDate, "yyyymmdd_hhmmss")
MyFile = "C:\Customer\DSF_BWS_" & My_String & ".csv"

DoCmd.TransferText acExportDelim, , _
"QueryDataExport", MyFile, True

Exit_cmdExport_Click:
Exit Sub

Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click

End Sub

Which outputs as:

"Centre","Date","Dept","Sales","Count","Units","Di vision"
"1111","20061126","0",2,"0","0","10",0
"1112","20061126","0",4,"0","0","10",0

However I'd like it to look like this:

Centre,Date,Dept,Sales,Count,Units,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. :)
Nov 27 '06 #1
20 5121
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!
Nov 27 '06 #2
PEB
1,418 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function create_file_from_SQL_SEP(SQL, File_name, sep) As Integer
  3. '?create_file_from_SQL_SEP("Joyau02", "D:\AJoyau.EXP", "; ")
  4. On Error Resume Next
  5. Dim line
  6. Dim mydb As Database
  7. Dim myr As Recordset
  8. Dim i
  9. Dim myupd_last, myupd_current
  10.  
  11. Set mydb = CurrentDb()
  12. Set myr = mydb.OpenRecordset(SQL)
  13.  
  14. Open File_name For Output As #1
  15. ' Close before reopening in another mode.
  16.  
  17. 'Stop
  18. myr.MoveLast
  19. myupd_last = myr.RecordCount
  20.     myr.MoveFirst
  21.     For myupd_current = 1 To myupd_last
  22.  
  23.         line = ""
  24.         For i = 0 To myr.fields.Count - 1
  25.             If i = 0 Then
  26.                 line = ns(myr(i))
  27.             Else
  28.                 line = line + sep + ns(myr(i))
  29.             End If
  30.         Next i
  31.         Print #1, line
  32.         myr.MoveNext
  33.     Next myupd_current
  34.  
  35. myr.Close
  36. Close #1
  37.  
  38. mydb.Close
  39. create_file_from_SQL_SEP = True
  40.  
  41. End Function
  42.  
  43.  
so to do your export you write:

i=create_file_from_SQL_SEP("Mytable", "MyFile", ",")

and no more pbs man!

:)
Nov 27 '06 #3
NeoPa
32,185 Expert Mod 16PB
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_hhnnss", 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.
Nov 27 '06 #4
PEB
1,418 Expert 1GB
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!

:)
Nov 27 '06 #5
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 :)
Nov 28 '06 #6
MMcCarthy
14,534 Expert Mod 8TB
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.
Nov 28 '06 #7
Ahhhh and the light goes on *smile*. Thanks I'll give it a try.

Cheers,
Cz.
Nov 28 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1.  nz(myr(i), "")
Nov 28 '06 #9
Killer42
8,435 Expert 8TB
Your Format string would be better written as "yyyymmdd_hhnnss", 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 "distinction 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?
Nov 28 '06 #10
PEB
1,418 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Function ns(Stri) As String
  2. On Error Resume Next
  3. Dim result As String
  4. If IsNull(Stri) Then
  5. result = ""
  6. Else
  7. result = Stri
  8. End If
  9. ns = result
  10. End Function
  11.  
I've ommited to see that i've used it!

U can omit it as u want!
Nov 28 '06 #11
MMcCarthy
14,534 Expert Mod 8TB
:)
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
Nov 28 '06 #12
Killer42
8,435 Expert 8TB
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.
Nov 28 '06 #13
NeoPa
32,185 Expert Mod 16PB
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 ;).
Nov 28 '06 #14
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
Nov 30 '06 #15
Killer42
8,435 Expert 8TB
In Access, when you do an Export (through the menus) you can define an export specification. This lets you set all kinds of details about the data format (for example delimited or fixed width fields), field delimiter, text delimiter (your unwanted quotes) and so on.

Once you do that, you can save the specification with a name. Then in your code, according to NeoPa, you can say to use that spec.

This may solve your quote problem.
Nov 30 '06 #16
That almost sounds too good to be true. I've setup the export schema for comma delimited, no quotes and saved it, but how do I then reference it?

Any ideas?
Nov 30 '06 #17
NeoPa
32,185 Expert Mod 16PB
Expand|Select|Wrap|Line Numbers
  1. 'THIS IS EXAMPLE CODE ONLY - DESIGNED FOR MY DATABASE!
  2.  
  3. 'ExportFile exports tbl{strName} to {conImpExpFolder}\{strName}.New
  4. Public Sub ExportFile(strName As String)
  5.     Dim strSpec As String, strTable As String
  6.     Dim strOut As String, strFile As String
  7.  
  8.     strSpec = strName & " Spec"
  9.     strTable = "tbl" & strName
  10.     strOut = conImpExpFolder & strName & "New.Txt"
  11.     strFile = conImpExpFolder & strName & ".New"
  12.     Call DoCmd.TransferText(TransferType:=acExportFixed, _
  13.                             SpecificationName:=strSpec, _
  14.                             TableName:=strTable, _
  15.                             FileName:=strOut, _
  16.                             HasFieldNames:=False)
  17.     On Error Resume Next
  18.     Call Kill(strFile)
  19.     On Error GoTo 0
  20.     Name strOut As strFile
  21. End Sub
TransferText Method


The TransferText method carries out the TransferText action in Visual Basic. For more information on how the action and its arguments work, see the action topic.

Syntax

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

The TransferText method has the following arguments.

Argument Description
transfertype One of the following intrinsic constants:
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim (default)
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant
(acImportDelim) is assumed.
Notes You can link to data in a text file or HTML file, but this data is read-only in Microsoft Access.
Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).

specificationname A string expression that's the name of an import or export specification you've created and saved in the current database.
For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
tablename A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.
filename A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.
hasfieldnames Use True (1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed.
This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.
HTMLtablename A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked.
The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.
codepage A Long value indicating the character set of the code page.
Nov 30 '06 #18
I still had issues until I decided to approach it a different way. Instead of trying to get the formating correct on the export, I figured I'd export it, then open the file and just search and replace on the quote strings then save it using the date and time filename.

Obviously if there's too much data the readall() could grind the computer to a halt by using up the virtual memory, but my requirements are very low (few hundred records each time), so seems to be a reasonable work around. I'll post what I did below in case it helps anyone else.

P.S. Thanks to everyone who chipped in to help me on this thread. :)

Expand|Select|Wrap|Line Numbers
  1. Dim MyDate
  2. Dim MyFile
  3. Dim Mystring
  4.  
  5. MyDate = Now()
  6. Mystring = Format(MyDate, "yyyymmdd_hhnnss")
  7. MyFile = "C:\Customer\DSF_" & Mystring & ".csv"
  8.  
  9. DoCmd.TransferText acExportDelim, , _
  10.     "QueryDataExport", "c:\customer\data.csv", True
  11.  
  12. Const ForReading = 1
  13. Const ForWriting = 2
  14.  
  15. Set objFSO = CreateObject("Scripting.FileSystemObject")
  16. Set objFile = objFSO.OpenTextFile("c:\customer\data.csv", ForReading)
  17.  
  18. strContents = objFile.ReadAll()
  19. objFile.Close
  20.  
  21. strOldText = Chr(34)
  22. strNewText = ""
  23.  
  24. strContents = Replace(strContents, strOldText, strNewText)
  25.  
  26. Set objFile = objFSO.OpenTextFile(MyFile, ForWriting, True)
  27. objFile.Write strContents
  28. objFile.Close
Dec 1 '06 #19
Killer42
8,435 Expert 8TB
I guess if it works, it works. But personally I think that using the export specification would be the more elegant solution. It effectively does the job in a single step, rather than two or more.
Dec 3 '06 #20
Killer I agree, but sometimes real world deadlines don't allow for the most elegant of programming lol. I was just having no luck and out of time. One of those ugly work arounds that, well, works. :)
Dec 13 '06 #21

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Gerald Aichholzer | last post: by
12 posts views Thread by Marek Lewczuk | last post: by
4 posts views Thread by Dom Hicklin | last post: by
1 post views Thread by Trint Smith | last post: by
4 posts views Thread by Martin Evans | last post: by
4 posts views Thread by mpar612 | last post: by
2 posts views Thread by pradeep | last post: by
3 posts views Thread by Alex Snast | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.