Solved

Access Table exported to CSV

Posted on 2011-03-23
7
296 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

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