0

I have a dataset in Excel, of which I have to save an assorted part to a new csv file. I have used SQL to select the part required to be saved. I was hoping somebody would guide me about saving this selection to csv using SQL.

SQL gives me a recordset but I would like to loop through it only as a last resort. I am hoping there is a better approach that directly saves the entire result from within the query itself if possible.

Something like the solution by Remou on Want VBA in excel to read very large CSV and create output file of a small subset of the CSV is what I'm looking for. But unfortunately I can't get that to work.

Community
  • 1
  • 1
user2696565
  • 587
  • 1
  • 8
  • 17
  • Something like the solution by Remou on http://stackoverflow.com/questions/427488/want-vba-in-excel-to-read-very-large-csv-and-create-output-file-of-a-small-subse?rq=1 is what I'm looking for. But unfortunately I can't get that to work. – user2696565 Sep 24 '13 at 21:35
  • You could edit your question with your information instead of commenting. – Diego C Nascimento Sep 24 '13 at 21:36
  • I'm confused as to your question, you've use SQL to subset data in an Excel file, presumably using `OPENROWSET` or some such. Now you want to know how to output that into a CSV? – Hart CO Sep 24 '13 at 21:39
  • I have a dataset in Excel from which I have selected the required part by running a SQL query on the Sheet containing data. Now, I want to save this part to CSV. – user2696565 Sep 24 '13 at 21:41
  • Using SQL Server to connect to the Excel file? – Hart CO Sep 24 '13 at 21:42
  • I used ADODB, pardon me but I'm not sure if that is same as SQL Server. – user2696565 Sep 24 '13 at 21:45

1 Answers1

0

Assuming you are in Excel VBA, has an ADODB opened with a Recordset object connected to a database and you want to save the content into a CSV (from within the Excel VBA).

The easiest is actually still by looping through the records in the recordset.

However, since you already rule that out (for whatever reasons), the next option would be to use the Recordset.Save <StreamObject> method. Create a System.IO.Streamwriter object to pass to the Recordset.Save.

OR, another way if you are using Jet SQL is:

conDB.Execute "SELECT * INTO [Text;Database=" _
               & vsCSVFolder _
               & ";HDR=No;FMT=Delimited].[" _
               & vsCSVFileame _
               & "] FROM Attendance", _
                 DBExport, _
                 adCmdText Or adExecuteNoRecords

Hope this helps.

jlee88my
  • 2,935
  • 21
  • 28
  • I only ruled out looping through the records assuming I could do something like "SELECT Blah INTO Output.csv FROM ExcelSheet ...". So that I could select and save in one single statement. But as you say, I might just have to loop. Thanks for your help anyways. – user2696565 Sep 24 '13 at 22:23