Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Backstory: I got a huge wall of sql code from a program which I want to use to dump data in excel. Executing this code from VBA produces a lot of errors and the chunk of code is to big and jumbled to go through.

Running the query in C# works fine so I've written a program which grabs the data and dumps in a file I call excelDump.csv

For ease of use in order to avoid having to run this program separately I call the program via vba macro. Once the program has executed I grab (in vba) the data in the dumb and populate my excel sheet.

The c# program gets called, the console shows everything I want, it closes with the correct exit code and I confirm this code in VBA before trying to open the dump.

Here is the code that I run in C#

public void DumpToExcel()
       {
           using (System.IO.StreamWriter fs = new System.IO.StreamWriter("excelDump.csv"))
           {
               // Loop through the fields and add headers
               for (int i = 0; i < myReader.FieldCount; i++)
               {
                   string name = myReader.GetName(i);
                   if (name.Contains(","))
                       name = "\"" + name + "\"";

                   fs.Write(name + ",");
               }
               fs.WriteLine();

               // Loop through the rows and output the data
               while (myReader.Read())
               {
                   for (int i = 0; i < myReader.FieldCount; i++)
                   {
                       string value = myReader[i].ToString();
                       if (value.Contains(","))
                           value = "\"" + value + "\"";

                       fs.Write(value + ",");
                   }
                   fs.WriteLine();
               }

               fs.Close();
           }
       }


What I have tried:

At first I thought the problem was that the program was unable to overwrite the file so I tried deleting the file but no go. The program works fine when I run it from visual studio or the release build from exe. But calling it from shell doesnt create a new file. Using file.delete can delete the old one just fine.

I thought maybe the c# program finishes to quickly before the files is created but Ive waited before importing data and still nothing.
Posted
Updated 19-Oct-16 20:18pm
Comments
Bernhard Hiller 19-Oct-16 3:42am    
What's "myReader"? Does it try to read the Excel file which contains the macro?
Member 11683251 19-Oct-16 3:46am    
OdbcDataReader myReader;

It runs the SQL query. This programs grab data from the sql and dumps to excel. Another excel imports this data into itself. I call this program from the importing excel program via shell.
Bernhard Hiller 19-Oct-16 3:52am    
What happens when you replace "excelDump.csv" with a full path (e.g. "c:\\temp\\excelDump.csv")? There could be an issue with the "current working directory".
Member 11683251 19-Oct-16 3:58am    
That seems to work, the files was created and correct data is in it.
Thanks a lot! Post it as an answer and I'll accept it. :)

You also need to encapsulate a field with double quotes if the field itself contains one or more double quotes.
 
Share this answer
 
The working directory for execution of the macro may differ from the folder of the file. With a full path like "c:\\temp\\excelDump.csv", you are on the safer side.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900