creating a CSV file

[davejam]

ok, i'm really stuck here

i want to create an export of data, i am using an ASP page and have my SQL statement correct and pulling into a recordset (objRs)

this is where i'm stuck, i understand a csv file is made up of values seperated by commas, then at the end of the line (next record) there is an enter....

how do i create this is code, and have it downloadable... i'm falling at the first hurdle here and can't find the first steps anywhere. (everyone seems more than happy to talk about what to do after... but i don't even know if i use <br> vbcrlf chr(13) or what!!!)

Anywhoo,

thanks in advance for any help, tips or ideas.

Cheers

thankyou

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[jfdabiri]

well, vbcrlf is chr(13).
anyway. you need to string the fields together in a row from your sql table and put a chr(13) at end of each line
like this:
each_line = ""
....
objrecordset.movfirst 'first record of the recordset
do while not objrecordset.eof
each_line =ObjRecordset.Fields.Item("column1") & "," & - ObjRecordset.Fields.Item("column2") & "," & _
ObjRecordset.Fields.Item("column3") & _
vbcrlf
objrecordset.movenext
loop

after this, write this line to the file.

 

[davejam]

thankyou, gives me a helping start, was stressing there....

what is the best way of creating the csv file, and is there a way to stream it rather than saving it to the server??

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[davejam]

also do i need to put quotations in for fields that are text... with posible spacings... think i read something about this ???

thanks

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[jfdabiri]

you don't need quotations. just the filed from sql is delimited by a comma: the data will look like this:
field one,field two,field three,field four
field one,field two,fieldthree,fieldfoud

etc.
i'm not sure about server and the other stuff.

 

[jfdabiri]

here's the code:

 ' create your recordset by retrieving the rows 
 ' from sql/access table

ForReading = 1
ForWriting = 2
ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
    ("c:\your_file.txt", ForWriting, True) 
each_line = ""
objrecordset.movefirst    'first record of the recordset
do while not objrecordset.eof
  each_line =ObjRecordset.Fields.Item("column1") & "," & -             ObjRecordset.Fields.Item("column2") & "," & _ 
             ObjRecordset.Fields.Item("column3") & _ 
             vbcrlf
  objTextFile.WriteLine(each_line) 
  objrecordset.movenext
loop

objTextFile.Close
msgbox("finished writing text file")

 

[tsuji]

> each_line =ObjRecordset.Fields.Item("column1") & "," & -
ObjRecordset.Fields.Item("column2") & "," & _
ObjRecordset.Fields.Item("column3") & _
vbcrlf
objTextFile.WriteLine(each_line)
This will result in one blank line every other data line. Also two spurious lines at the end? Maybe this may improve on it?
[tt]
objrecordset.movefirst 'first record of the recordset
do while not objrecordset.eof
each_line = ""
for i=0 to objrecordset.fields.count-1
each_line=each_line & objrecordset(i).value
if i<>objrecordset.fields.count-1 then
each_line=each_line & ","
end if
next
objrecordset.movenext
'The ending may depend, deciding with or without the conditional
if not objrecordset.eof then each_line=each_line & vbcrlf
objTextFile.Write(each_line)
loop
objTextFile.close
[/tt]

 

[jfdabiri]

thanks tsuji. that would work better.

 

[davejam]

thankyou managed to get it all working, and found out about the blank line as i went along and created the string on its own before adding it to the text file.

ok... what i really need now is to get it to stream this without creating a physical file.... so when a client clicks on export it comes up asking where they would like it saved, and it saves it to their machine.

also ideally as an end point i'd want to be exporting this as excel..... as above, streaming rather than a physical file, but in an excel format / file extension.

Any ideas peeps??

Cheers

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[jfdabiri]

why don't you just export from access to excel without going through this text deal?
here's the code to go from a table in access to excel:

Const USER_PROFILE = &H28& 
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(USER_PROFILE)
Set objFolderItem = objFolder.Self
mypath = objFolderItem.Path
eachpart = split(mypath,"\")
user = eachpart(2)
table_name = "my_table" 
sqlstring = "SELECT * INTO [Excel 8.0;HDR=Yes;" & _
        "Database=c:\documents and settings\" & user & "\desktop\" & _        
          table_name & ".xls].Sheet1 FROM " &  table_name   
objConnection.Execute sqlring
wscript.sleep 400