Export sql to csv

[Smee]

I have the following code to export a table to a csv file. Surely there is a better method than this?

Does anyone have a code snippet?

Dim List1 As List
Dim Qry As String
Dim TextWriter1 As TextWriter

Qry="Select * from Orders order by Date,CustCode"
Cursor1=SQL.ExecQuery(Qry)
If Cursor1.RowCount=0 Then
   Cursor1.Close
   Return
End If

TextWriter1.Initialize(File.OpenOutput(File.DirDefaultExternal, "Orders.csv", False))

For i = 0 To Cursor1.RowCount-1
   Cursor1.Position=i
   Datex = Cursor1.GetString("Date") 
   CC = Cursor1.GetString("CustCode") 
   Pc = Cursor1.GetString("ProdCode") 
   Qty =  Cursor1.GetInt("Qty") 
   Cost =  Cursor1.GetInt("Cost") 
   Sent =  Cursor1.GetInt("Sent") 
   TextWriter1.Write(Datex & "," & CC & "," & PC & "," & Qty & "," & Cost & "," & Sent & Chr(13) & Chr(10))
Next
TextWriter1.Flush
TextWriter1.Close
Cursor1.Close

 

[Erel]

You can use DBUtils.ExecuteMemoryTable with StringUtils.SaveCSV.

 

[Smee]

Thanks for the quick reply as always Erel,

Is there any sample code anywhere using these? i tried them earlier but i was unable to do it properly

 

[Smee]

Bit of searching and i got it.

Thanks Erel

 

[Mahares]

Smee says:

I have the following code to export a table to a csv file. Surely there is a better method than this

Mahares asks: What is wrong with the code you outlined in the outset of your thread. I use something similar except that I use an array for the column data and add them to a list. TextWriter1.Write(List1). Did you switch to DBUtils.ExecuteMemoryTable and find that it runs faster. If so, please let us know. I like to change my habit if there are more efficient ways to do it.

 

[Smee]

i did not test for speed but there is less code so to me that is more efficient and easier to work with

 

[omar madraba]

convert to csv file

Bit of searching and i got it.

Thanks Erel

where did you got it my friend???????:sign0085:

 

[Smee]

I meant searching as in reading and got it as in understanding.

but fwiw this was the code snippet

List1=DBUtils.ExecuteMemoryTable(SQL,Qry,Null,0)
su.SaveCSV(File.DirDefaultExternal, "filename.csv", ",",List1)