Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Table exported to CSV

Posted on 2011-03-23
7
Medium Priority
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

609 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