• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

MySQL - Exporting data to a csv on a remote client PC

Hi all,

I have an application and a MySQL DB located on a central server, I need to add the fucntionality, to enable users logging onto my app, to download information from the db to their local PCs.

Is there an SQL command which allows you to do this directly or do I need to use the "select into outfile" command to capture the data locally then ftp it across to the client?

Any advice is most appreciated
0
taylor99
Asked:
taylor99
1 Solution
 
Ivo StoykovCommented:
depends on what is your app, what protocol uses to connect, etc.
0
 
DCMBSCommented:
you might be able to do this using mysqldump.  You would need to do something like
create table MyDataExport as select (your select query)
This will create a table containg the results of your Select Query which you would then be able to export to the local machine using mysqldump with the -T option and --fields-terminated-by, --fields-enclosed-by, --fields-optionally-enclosed-by, --fields-escaped-by, --lines-terminated-by.

This is obviously a workaround but I don't know any way to it directly.
0
 
kingjelyCommented:
In what format do they need it. If they need to access the data as a .csv for example
Could you go to 'file' > export result set > Export as .csv file

0
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.

 
aninditoyogaCommented:
Easy Solution : ESF Database Migration Toolkit - 6.3.14 (http://www.easyfrom.net/) Try before buy :)
0
 
taylor99Author Commented:
Hi All,

Thanks for the prompt replies, I need to build this function into my app so users can "download" selected tables / results, therefore I can't use 3rd party software or query browser to achieve this.  

ivostoykov, in answer to your question, the app has been created using the .net framework and uses the MySQLConnection Class to connect to the db.

I was hoping there was a variation of the export or mysqldump commands which would allow the entry of an IP address in the file path, but this doesn't seem possible.
0
 
SoLostCommented:
While this hasn't been tested, could you not simply use a StreamWriter to create your own text file and output the data to it yourself?
        Dim TextLine As String = ""
        Dim DS As DataSet = Nothing
        Dim DV As DataView = Nothing
        Dim DRV As DataRowView = Nothing

        DV = DS.Tables(0).DefaultView

        ' Create the file here

        For row As Integer = 0 To DV.Count - 1
            DRV = DV.Item(row)
            TextLine = ""

            For col As Integer = 0 To DS.Tables(0).Columns.Count - 1
                ' Add a comma seperator
                If col > 0 Then TextLine &= ","

                ' If the field has a comma in it.  Surround the field in
                ' quotation marks
                If DRV.Item(col).ToString.Contains(",") Then
                    TextLine &= """" & DRV.Item(col).ToString & """"
                Else
                    TextLine &= DRV.Item(col).ToString
                End If
            Next

            ' Write the row to the file here

        Next

        ' Close the file here

Open in new window

0
 
DCMBSCommented:

If you write a script to use mysql dump with options I listed above and specify a file using the -result-file option then this should run on the users PC and dump the results locally to the named results file

i.e create a script containing something like the following command. (You will have to experiment to find the vaules for the parameters so that you end up with a proper csv file.  check http://dev.mysql.com/doc/refman/5.1/en/load-data.html for how to specify values for the parameters)

mysqldump mydatabase mytable --fields-terminated-by  --fields-enclosed-by  --fields-optionally-enclosed-by  --fields-escaped-by --lines-terminated-by --results-file="C:\mycsvfile.csv"

If you include a link to this script for the user to run the script on his loacl machine then it should run locally and dump the results to a local file.
0
 
taylor99Author Commented:
I found the answer to this question at

http://forums.asp.net/t/1185493.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now