Solved

Access Table exported to CSV

Posted on 2011-03-23
7
302 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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