Exporting data to a csv file 1

[meckeard]

All,

I am using Access 97 to export some data from a table to a .csv file.

I have everything except the part about specifying what values go to which cells.

For example, let's assume that I am looping thru my recordset and need to write the 'name' field in the first cell and the 'address' field in the second cell. How do I get the different values in to different cells?

Here is the code:

Do until objRS.EOF
objRS("Name&quot.value & ";" & objRS("Address&quot.value
objRS.MoveNext
Loop

Thanks,
Mark

 

[oharab]

I'm not sure what you mean, your code should do just that:

Name,Address
Ben,1 Jack Close
Bill,73 The lane
Weed,Flowerpot Lane
.
.
.


What are you getting, and what do you want to get?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[bboffin]

The general logic of your code appears to be correct except that you're not actually writing out the data to the CSV file.

Add:

Open "MyTest.csv" For Output As #1

before the loop

and then replace the line before the MoveNext with:

Write #1 objRS("Name&quot.value, objRS("Address&quot.value

This will automatically produce a CSV file with commas between the items, double quotes around strings and a CRLF after each Write statment.

Don't forget to close the file

Close #1

when you're done.

 

[meckeard]

Ben -- I am getting a comma seperated list, but I thought that each value would appear in each cell when opened in Excel. It does when I create it manaually in notepad and then open it up in Excel.

bboffin -- Sorry, in an attempt to keep my code short, I left that part out. Writing to the text file is not the problem.

Does it matter if each value goes in to its own cell?

Thanks,
Mark

 

[oharab]

Have you tried opening your access generated output in notepad and comparing the two?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[p27br]

do you want to write to excel or to a csv file ?

 

[meckeard]

A csv file, but I open it in Excel. Always have. That's why I noticed the difference when I viewed it this time.

Thx.

 

[JoanneM]

Another option just for info should you wish to try
This is a small form with a combo box listing all tables and queries in your database
You Select the table or query to output to CSV
Then in an unbound textbox underneath you put the name of the file you wish to create
then you press the create CSV button and that's that provided you've copied everything below to the right places.

Hope this is of some use to you

Jo


Create a form with the following controls on it

combo box with row source of
SELECT DISTINCTROW [MSysObjects].[Name] FROM MSysObjects WHERE ((([MSysObjects].[Name]) Not Like "MSys*" And ([MSysObjects].[Name]) Not Like "~*" And ([MSysObjects].[Name]) Not Like "zt*&quot And (([MSysObjects].[Type]) In (1,5))) ORDER BY [MSysObjects].[Name];

name cboTbl


An unbound textbox called txtfilename

cmd button
name: cmdOutputToCSV
caption: OutputToCSV

Event On click

Private Sub cmdOutputToCSV_Click()
'Calls Mkcsv which takes the object selected and outputs it to a csv file
'using the path typed int thebbox
On Error GoTo csverr
Dim obj As String
Me.lblfilepath.Visible = True
Me.Refresh
obj = Me.cboTbl
Call mkcsv(obj)
Me.lblfilepath.Visible = False
csverrexit:
Err = 0
Exit Sub

csverr:
MsgBox Error$(Err)
GoTo csverrexit
End Sub


cmd button close
usual close form code here


the make CSV code calls the following function which you copy & paste into a module

Sub mkcsv(objname As String)
On Error GoTo mkcsverr
Dim mydb As Database
Dim myrs As Recordset
Dim fldcount As Integer
Dim fld As Field
Dim x As Integer
Dim printstr As String
Dim fs As Object
Dim a As Object
Dim fname As String
Set mydb = CurrentDb
Set myrs = mydb.OpenRecordset(objname, dbOpenDynaset)

fname = Forms!frmexporttocsv!txtfilename & ".txt"

Set fs = CreateObject("Scripting.FileSystemObject&quot
Set a = fs.CreateTextFile("c:\" & fname, True)

a.Close

DoCmd.Hourglass True
Open "c:\mycsv.txt" For Output As #1

Do Until myrs.EOF
For x = 0 To myrs.Fields.Count - 1
If x <> myrs.Fields.Count - 1 Then
printstr = printstr & myrs.Fields(x).Value & &quot;,&quot;
Else
Print #1, printstr & &quot;,&quot; & myrs.Fields(x).Value
'Print #1, vbNewLine
End If
Next x
myrs.MoveNext
printstr = &quot;&quot;
Loop
Close #1
DoCmd.Hourglass False
'MsgBox &quot;Text file &quot; & fname & &quot;created to &quot; & PathName

mkcsverrexit:
Err = 0
Exit Sub


mkcsverr:
MsgBox Error$(Err)
GoTo mkcsverrexit
End Sub

 

[p27br]

have you tried the docmd.transfertext method ?

 

[MadJock]

Just noticed, original code Meckeard put's a semi-colon in between values - this should be a comma.

That could be part of the reason that Excel isn't recognising the different columns.

Hope this helps,

Graeme

website:

http://www.graeme-taylor.co.uk