asked on

Recordset so CSV

I want to export a recordset to a CSV(TEXT) file, the code I got so far, thanks b0lsc0tt is this:

Dim strFileContents
strFileContents = "First,line,of,my,file" & vbcrlf

Dim objFS, objTS, strFile
strFile = "c:\inetpub\wwwroot\webapply\test.csv"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.CreateTextFile(strFile)
objTS.Write strFileContents
objTS.Close
Set objTS = Nothing

1) What I need to do is replace the first,line,of,my,file with actual fields from the recordset and,

2) Create a dynamic name for the test.csv file so the name is the actual id of the spesific recordset

LordWabbit

Heres a way to cycle through the columns regardless of the how many there are, hope it helps

Do While rs.EOF = False
For intCol = 0 To rs.Fields.Count - 1
If Len(strLine) > 0 Then strLine = strLine & ","
strLine = strLine & rs(intCol)
Next
WriteLineToCSV(strLine)
strLine = ""
rs.MoveNext()
Loop

Can't quite remember if the -1 is required for the fields count (pretty sure it is but if your are missing your last column take it out, it's been a while since I worked with ADODB). Oh yeah the For loop might also need to start from 1 instead of 0 MS tried to get rid of the whole 0 based count thing (but wasted their time and muddied the waters yet again).
Also what might be a good idea is to replace any commas in the info
strLine = strLine & Replace(rs(intCol), ",", "")

Point 2 I can't help you with, AFAIK ADODB recordsets do not have UID's. Or if they do they are not readily accessible.

intreeg

If you are trying to set the filename to the value of the Primary key for the record set, (which Im not sure if that is what your second request is) you could store the value in a variable. After you populate the file and close it, you could then rename it to "variableName.csv"... just a thought.

ACilliers

ASKER

Thanks, for the help so far LordWabbit, but how (and where) to I implement that in my code?

Sorry, bit of a newby!

ASKER CERTIFIED SOLUTION

LordWabbit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial