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
taylor99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.