CSV Export - With Selected Fields Only 2

[DAmoss]

Is there a way of exporting a table to a CSV file, but with the ability of being able pick which fields you want to export using VBA code?

No copying to another table please.

Cheers

 

[lameid]

Export a query.

If necessary you can create a querydef object and set its SQL property to whatever you want. Just make sure to save the query before trying to export it (Access exports the saved version of a query in ACC97). Although I think you get an error message if you try to export an unsaved query in later versions.

 

[CautionMP]

I'm not sure I would goof around with QueryDef object.

Assuming you are going to create an interface to allow the user to choose the fields they want it would be very easy to contruct an SQL string using this data, open a recordset based on the SQL string ([tt]rstOutput[/tt]), open a blank file ([tt]Open "Output.csv" For Output As #1[/tt]), then loop through the recordset and write the record data ([tt]Write #1, rstOutput.Fields(0), rstOutput.Fields(1)...[/tt]).

If this holds any interest to you, post back and we can get you moving in the right direction.

Hope this helps,
CMP

 

[PHV]

Another way is to automate excel playing with the CopyFromRecordset method and save as csv.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

CopyFromRecordset? I did not see it in Access or Excel help... What object does it belong to?

 

[PHV]

It's a method of the Excel.Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

Ok ... Obviously it's not easy to do, although the idea of creating an interface to construct the SQL string for outputting sounds like a great idea! (CautionMP)

But I have absolutely no idea how to go about this!

Thanx for your all your replies so far.

 

[lameid]

How about saving the query with nothing selected and then opening it in design view? That may be the easiest but you are giving users the ability to design a query.

Or you could use a multi-select listbox with all the field names in it (code for the multi-select listbox is probably in a FAQ and most certainly in some other thread).

You would then build your SQL statement by concatenating it together.

If this is still too little detail, please post a sample SQL statement of something you might want to export so we can help further.

 

[CautionMP]

This takes a few steps so I will go through it in chunks and I am adding only the most basic functionality. Esentially you double click the first ListBox and it moves the selected field to the end of the second ListBox. When you click the CommandButton Output the form should create the file [tt]Output.csv[/tt] on your [tt]C:\[/tt] drive that matches the fields selected on the form.

Create the form:
[ol][li]Create a new form.[/li][li]Add a ListBox control with the following attributes:[ul][li]Name: lstAvailableFields[/li][li]Row Source Type: Field List[/li][li]Row Source: Pick a table or Query from the list.[/li][/ul][li]Add a second ListBox control with the following attributes:[ul][li]Name: lstOutputFields[/li][li]Row Source Type: Value List[/li][/ul][/li][li]Add a CommandButton with the following attributes:[ul][li]Name: cmdOutput[/li][li]Caption: Output[/li][/ul][/li][/ol]

Add the Functionality:
[ol][li]For [tt]lstAvailableFields[/tt] add the following code to the On Dbl Click event:

  Me.lstOutputFields.RowSource = Me.lstOutputFields.RowSource & _
  ";" & _
  Me.lstAvailableFields.ItemData(Me.lstAvailableFields.ListIndex)

[/li][li]For [tt]cmdOutput[/tt] add the following code to the On Click event:

Dim conCurrent As ADODB.Connection
Dim rstOutput As New ADODB.Recordset
Dim objField As ADODB.Field
Dim intFile As Integer
Dim strSQL As String, strDataLine As String

Set conCurrent = CurrentProject.Connection

'Turn lstOutputFields.RowSource into a valid SQL string
strSQL = Replace(Me.lstOutputFields.RowSource, ";", ", ")
strSQL = "SELECT " & strSQL & " FROM " & Me.lstAvailableFields.RowSource & ";"

'Open the output file C:\Output.csv
intFile = FreeFile
Open "C:\Output.csv" For Output As #intFile

'Open the recordset defined by the form
rstOutput.Open strSQL, conCurrent, adOpenForwardOnly, adLockReadOnly

'Create and write the header line
strDataLine = Chr(34) & "," & Chr(34)
strDataLine = Chr(34) & Replace(Me.lstOutputFields.RowSource, ";", strDataLine) & Chr(34)
Print #intFile, strDataLine

'Now loop through the recordset and write a CSV line for each record
Do
  For Each objField In rstOutput.Fields
    strDataLine = strDataLine & Chr(34) & "," & Chr(34) & objField.Value
  Next objField
  strDataLine = Chr(34) & strDataLine & Chr(34)
  Print #intFile, strDataLine
  strDataLine = ""
  rstOutput.MoveNext
Loop Until rstOutput.EOF

rstOutput.Close
Close #intFile

Set rstOutput = Nothing
Set conCurrent = Nothing

[/li][/ol]

Hope this helps,
CMP

 

[DAmoss]

I've followed all your steps so far, but i'm get a compile error in the cmdOutput code at the following first line.

conCurrent As ADODB.Connection

Error:
User define type not defined

 

[PHV]

You have to reference the Microsoft ActiveX Data Object 2.# Library in menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

Cheers PHV, thats got me past the compile problem.

Now I'm getting another error:

I've just had quick look at the strSQL and there appears to be a , after the SELECT command that shouldn't be there. I generates the following:

"SELECT , NINO, DOB FROM Project Data;"

My lstOutputFields listbox also has a blank field at the top of the fields list when I choose my export fields.

 

[PHV]

Replace this:
Me.lstOutputFields.RowSource = Me.lstOutputFields.RowSource & _
";" & _
Me.lstAvailableFields.ItemData(Me.lstAvailableFields.ListIndex)
With this:
With Me!lstOutputFields
.RowSource = IIf(.RowSource > "", .RowSource & ";", "") & _
Me!lstAvailableFields.ItemData(Me!lstAvailableFields.ListIndex)
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

Cheers again PHV.

The code now runs and produces a CSV file ... but the contents look nothing like they should, it looks like this:

NINO DOB SDATE
NINO" DOB SDATE",",23/9/1975"

 

[PHV]

...
'Create and write the header line
strDataLine = Chr(34) & "," & Chr(34)
strDataLine = Chr(34) & Replace(Me.lstOutputFields.RowSource, ";", strDataLine) & Chr(34)
Print #intFile, strDataLine
'Now loop through the recordset and write a CSV line for each record
Do
strDataLine = ""
For Each objField In rstOutput.Fields
strDataLine = strDataLine & Chr(34) & "," & Chr(34) & objField.Value
Next objField
Print #intFile, Mid(strDataLine, 3) & Chr(34)
rstOutput.MoveNext
Loop Until rstOutput.EOF
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Sorry about the errors, I did a syntax check but never ran the code (shame on me), I was more focused on getting the concept documented than writing and testing the code.

Once you have the general functionality, er, eh, functioning there are a lot of refinements that can be done to put some gloss on the whole process (like the change to the [tt]OnDoubleClick[/tt] event enhancement that you alredy discovered).

I will re-visit the code when I get back to my machine tomorrow morning.

CMP

 

[DAmoss]

PHV ... Thank you again for your help! It now does what it says on the tin!

CMP ... Thank you for all your hard work with original idea and vba code. Let me know if you think of any enhancements.

I reckon I should be able to do away with the listbox's and automate the process by tagging the fields that I need to export.