Solved

VB write data to csv

Posted on 2012-03-13
17
268 Views
Last Modified: 2012-03-14
I am trying to take records from an MS Access query in my windows application and create a csv with the records. Here is the code I have so far.

Dim DBName As String = "Time & Pay.mdb"
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        conn.Open()
        Dim csv As String = "\Paryoll.csv"
        Dim tw = System.IO.StreamWriter(GetPath() & csv)
        Dim sql = "SELECT * FROM qry_EarningsOT"
        Dim consql As New SqlCommand
        consql.CommandText = sql
        consql.Connection = conn
        Dim datRead As SqlDataReader
        datRead = consql.ExecuteReader
        Do Until datRead.Read = False
            tw.WriteLine(datRead.GetString(0) & " , " & datRead.GetString(1) & " , " & datRead.GetString(2) & _
                         " , " & datRead.GetDouble(3) & " , " & datRead.GetString(4) & " , " & datRead.GetDouble(5))
        Loop
        tw.Close()
        conn.Close()
0
Comment
Question by:rawilken
17 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 37718551
And what exactly is your question ? Isn't it working or do you get an error somewhere ?
0
 
LVL 9

Accepted Solution

by:
chwong67 earned 350 total points
ID: 37718847
I assume the database is MS Access.

Dim DBName As String = "Time & Pay.mdb"
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & getpath() & "\" & DBName
        conn.Open()
        Dim csv As String = "\Paryoll.csv"
        Dim tw As New System.IO.StreamWriter(getpath() & csv)
        Dim sql = "SELECT * FROM qry_EarningsOT"
        Dim consql As New OleDbCommand
        consql.CommandText = sql
        consql.Connection = conn
        Dim datRead As OleDbDataReader
        datRead = consql.ExecuteReader
        Do Until datRead.Read = False

            tw.WriteLine(datRead.GetString(0) & " , " & datRead.GetString(1) & " , " & datRead.GetString(2) & _
                         " , " & datRead.GetDouble(3) & " , " & datRead.GetString(4) & " , " & datRead.GetDouble(5))
        Loop
        tw.Close()
        conn.Close()

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37719589
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:rawilken
ID: 37719963
There error I get is that the streamwriter is a type and cannot be used as an expression...

 Public Function PrintDate(strExport As String) As Boolean
        Dim DBName As String = "Time & Pay.mdb"
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        conn.Open()
        Dim csv As String = "\Paryoll.csv"
        Dim tw = System.IO.StreamWriter(GetPath() & csv)
        Dim sql = "SELECT * FROM qry_EarningsOT"
        Dim consql As New OleDb.OleDbCommand
        consql.CommandText = sql
        consql.Connection = conn
        Dim datRead As OleDb.OleDbDataReader
        datRead = consql.ExecuteReader
        Do Until datRead.Read = False
            tw.WriteLine(datRead.GetString(0) & " , " & datRead.GetString(1) & " , " & datRead.GetString(2) & _
                         " , " & datRead.GetDouble(3) & " , " & datRead.GetString(4) & " , " & datRead.GetDouble(5))
        Loop
        tw.Close()
        conn.Close()
    End Function
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37720057
I think you forgot a NEW:

Dim tw  AS NEW System.IO.StreamWriter(GetPath() & csv)
0
 

Author Comment

by:rawilken
ID: 37720264
Error Message...

Overload resolution failed because no accessible 'New' can be called without a narrowing conversion:
    'Public Sub New(path As String)': Argument matching parameter 'path' narrows from 'Object' to 'String'.
    'Public Sub New(stream As System.IO.Stream)': Argument matching parameter 'stream' narrows from 'Object' to 'System.IO.Stream'.      C:\Data Control\Time and Pay\Time_and_Pay\Time_and_Pay\TimeAndPay.vb      84      18      Time_and_Pay
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37720273
0
 

Author Comment

by:rawilken
ID: 37720738
I get no obvious error messages with the code written this way..

Dim tw As StreamWriter
tw = (GetPath() & csv)

I still need to test it though.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37720834
just try:

Dim tw As System.IO.TextWriter = New StreamWriter(GetPath() & csv)
0
 

Author Comment

by:rawilken
ID: 37721223
Two things..
1 I assume this overwrites the existing csv file if there is one
2 How do I append another dataset to the same csv
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37721328
0
 

Author Comment

by:rawilken
ID: 37721682
Error thrown...
error-1.JPG
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37721717
one of the value supposed to be double (fields 3 and 5) is not valid.

try this:
dim f3 as double
double.tryparse(datRead.getdouble(3).tostring, f3)

dim f5 as double
double.tryparse(datRead.getdouble(5).tostring, f5)

and use f3 and f5 in your .WriteLine
0
 

Author Comment

by:rawilken
ID: 37721778
Specified cast on f5 is not valid

Do Until datRead.Read = False
            Dim f3 As Double
            Double.TryParse(datRead.GetDouble(3).ToString, f3)
            Dim f5 As Double
            Double.TryParse(datRead.GetDouble(5).ToString, f5)
            tw.WriteLine(datRead.GetString(0) & " , " & datRead.GetString(1) & " , " & datRead.GetString(2) & _
                         " , " & datRead.GetDouble(f3) & " , " & datRead.GetString(4) & " , " & datRead.GetDouble(f5))
        Loop

That field is a double, currency format in my database.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 150 total points
ID: 37721884
change to :

 tw.WriteLine(datRead.GetString(0) & " , " & datRead.GetString(1) & " , " & datRead.GetString(2) & _
                         " , " & f3 & " , " & datRead.GetString(4) & " , " & f5)
0
 

Author Comment

by:rawilken
ID: 37722044
Same error message...
Speficied case is not valid.
Double.TryParse(datRead.GetDouble(5).ToString, f5)
0
 

Author Closing Comment

by:rawilken
ID: 37722443
I also had to modify for null values where they existed.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question