?
Solved

Access Table exported to CSV

Posted on 2011-03-23
7
Medium Priority
?
304 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

770 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