Solved

Access Table exported to CSV

Posted on 2011-03-23
7
294 Views
Last Modified: 2012-05-11
I'm looking for a way to export an access table, to a CSV file, using VB.NET.  Is there an easy way to do this, rather than having to manually escape the filed data?  Here's a sample of my code - this is a compiled DLL being called from a web page.  The idea is that after adding all th records to a table, then export that table to a CSV file.


        If RecordCount(strSQL) > 0 Then
            While ConRdr.Read
                'Debug.Print(ConRdr("last").ToString)
                Select Case IncludeContacts
                    Case 2, 3
                        OnlyOneRow = True
                End Select

                insSQL = "INSERT INTO exported "
                insSQL += "([company] , [address] , [address_2] , [city] , [state] , [zip] , [county] , [naic] , [sic] , [phone] , [extension] , [fax] , [URL] , [do_not_call] , [mtb] , [jobexpo] , [cawm] , [workkey] , [bci] , [construction] , [disability] , [hiring] , [healthcare] , [insurance] , [it] , [manufacturing] , [number_of_employees] , [keywords] , [service] , [contact_first] , [contact_last] , [contact_title] , [contact_email] , [contact_phone] , [contact_extension] , [contact_fax] , [contact_notes] , [contact_reporting]) VALUES "
                insSQL += "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?)"

                cmd = New OleDbCommand(insSQL, ReportingDB)

                cmd.Parameters.AddWithValue("company", Emprdr("company"))
                cmd.Parameters.AddWithValue("address", Emprdr("address"))
                cmd.Parameters.AddWithValue("address_2", Emprdr("address_2"))
                cmd.Parameters.AddWithValue("city", Emprdr("city"))
                cmd.Parameters.AddWithValue("state", Emprdr("state"))
                cmd.Parameters.AddWithValue("zip", Emprdr("zip"))
                cmd.Parameters.AddWithValue("county", Emprdr("county"))
                cmd.Parameters.AddWithValue("naic", Emprdr("naic"))
                cmd.Parameters.AddWithValue("sic", Emprdr("sic"))
                cmd.Parameters.AddWithValue("phone", Emprdr("phone"))
                cmd.Parameters.AddWithValue("extension", Emprdr("extension"))
                cmd.Parameters.AddWithValue("fax", Emprdr("fax"))
                cmd.Parameters.AddWithValue("URL", Emprdr("url"))
                cmd.Parameters.AddWithValue("do_not_call", Emprdr("do_not_call"))
                cmd.Parameters.AddWithValue("mtb", Emprdr("mtb"))
                cmd.Parameters.AddWithValue("jobexpo", Emprdr("jobexpo"))
                cmd.Parameters.AddWithValue("cawm", Emprdr("cawm"))
                cmd.Parameters.AddWithValue("workkey", Emprdr("workkey"))
                cmd.Parameters.AddWithValue("bci", Emprdr("bci"))
                cmd.Parameters.AddWithValue("construction", Emprdr("construction"))
                cmd.Parameters.AddWithValue("disability", Emprdr("disability"))
                cmd.Parameters.AddWithValue("hiring", Emprdr("hiring"))
                cmd.Parameters.AddWithValue("healthcare", Emprdr("healthcare"))
                cmd.Parameters.AddWithValue("insurance", Emprdr("insurance"))
                cmd.Parameters.AddWithValue("it", Emprdr("it"))
                cmd.Parameters.AddWithValue("manufacturing", Emprdr("manufacturing"))
                cmd.Parameters.AddWithValue("number_of_employees", Emprdr("number_of_employees"))
                cmd.Parameters.AddWithValue("keywords", Emprdr("keywords"))
                cmd.Parameters.AddWithValue("service", Emprdr("service"))
                cmd.Parameters.AddWithValue("contact_first", ConRdr("first"))
                cmd.Parameters.AddWithValue("contact_last", ConRdr("last"))
                cmd.Parameters.AddWithValue("contact_title", ConRdr("title"))
                cmd.Parameters.AddWithValue("contact_email", ConRdr("email"))
                cmd.Parameters.AddWithValue("contact_phone", ConRdr("phone"))
                cmd.Parameters.AddWithValue("contact_extension", ConRdr("extension"))
                cmd.Parameters.AddWithValue("contact_fax", ConRdr("fax"))
                cmd.Parameters.AddWithValue("contact_notes", ConRdr("notes"))
                cmd.Parameters.AddWithValue("contact_reporting", ConRdr("reporting"))

                cmd.ExecuteNonQuery()
                cmd.Dispose()

Open in new window

0
Comment
Question by:SteveCantin
7 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35202880
You could create a macro in Acess to do the export then from vb use the following:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21510169.html
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 35202917
Here is the code for the vba macro:

Dim conCurrent As ADODB.Connection
Dim rstOutput As New ADODB.Recordset
Dim objField As ADODB.Field
Dim intFile As Integer
Dim strSQL As String, strDataLine As String

Set conCurrent = CurrentProject.Connection

'Turn lstOutputFields.RowSource into a valid SQL string
strSQL = Replace(Me.lstOutputFields.RowSource, ";", ", ")
strSQL = "SELECT " & strSQL & " FROM " & Me.lstAvailableFields.RowSource & ";"

'Open the output file C:\Output.csv
intFile = FreeFile
Open "C:\Output.csv" For Output As #intFile

'Open the recordset defined by the form
rstOutput.Open strSQL, conCurrent, adOpenForwardOnly, adLockReadOnly

'Create and write the header line
strDataLine = Chr(34) & "," & Chr(34)
strDataLine = Chr(34) & Replace(Me.lstOutputFields.RowSource, ";", strDataLine) & Chr(34)
Print #intFile, strDataLine

'Now loop through the recordset and write a CSV line for each record
Do
  For Each objField In rstOutput.Fields
    strDataLine = strDataLine & Chr(34) & "," & Chr(34) & objField.Value
  Next objField
  strDataLine = Chr(34) & strDataLine & Chr(34)
  Print #intFile, strDataLine
  strDataLine = ""
  rstOutput.MoveNext
Loop Until rstOutput.EOF

rstOutput.Close
Close #intFile

Set rstOutput = Nothing
Set conCurrent = Nothing

Open in new window

0
 
LVL 3

Expert Comment

by:babesia
ID: 35202941
I use  Open  and Print commands and just loop though the records

Open d:\temp.csv For Output As #1

Print #1, "Hello world"
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:SteveCantin
ID: 35203569
Access is not installed on the target machine, so I don't thank that will work?

If looping through the records is done, won't the field values have to be parsed and double quotes escaped?  I seem to recall doing this a while back, and carriage returns present in memo fields also presented a problem

Thanks!
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35211534
The Access database does not need to be on the person's machine.  If it is on a server that they have access to, it will work.
0
 
LVL 19

Expert Comment

by:Amandeep Singh Bhullar
ID: 37272020
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now