how to properly export an sql data to .csv

Question

I am trying to export a very large data from mysql to .csv and import the .csv into another identical table using the

into outfile

in phpmyadmin. But when i look at the excel file it is messed up. It seems that mysql cant determine the longtext format so it goes to another line like this one:

enter image description here

So when i try to import this to an identical table, it is all messed up. Here's my import code to other table:

LOAD DATA INFILE 'final.csv'
INTO TABLE oldforum_nodewithusers
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

By the way i manually typed the headers of the table since i dont know too how to put headers in the export code. Here's my export code:

SELECT 'some table columns'
into outfile 'final.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

So how to fix this? is it in the export part? or the import part?

I KNOW NOW THE PROBLEM, THEY TEXT THAT WENT TO THE NEXT LINE IN THE PICTURE HAS EXCEED THE TEXT LIMIT FOR A CELL IN EXCELL

so when i import it to another table the table will treat the "next lined" text as another row. Anyone has solution for this?

What is preventing you from using a single insert query and skipping the part with the file? — Dan Bracuk, Apr 04 '13 at 19:08

score 0 · Answer 1 · edited Jun 12 '19 at 21:12

I would go with a command line approach (which has worked fine for me). On Linux (and other Unix systems):

$ mysql [connection parameters] yourDB -e"select..." | sed 's/\t/,/g' > yourOutput.csv

Explaining

The [connection parameters] piece must include: -h YourHost (where YourHost must be a valid IP adress or server name, or localhost), -u YourUser and -pYourPassword (notice that you must not leave spaces between -p and YourPassword)
mysql ... -e"select..." outputs the query result to standard out (your console) separating fields with tabs (\t).
| sed 's/\t/,/g' replaces all tabs (\t) with commas. It takes the output from the previous command for processing, and also outputs the result to standard out
> yourOutput.csv redirects the output of the previous command to a file instead of the console

A windows option

As far as I know, there's no "native" alternative to sed in Windows. But you can install Cygwin, which has "windows command-line compatible" features that replicate most of the Linux functionality. Also you may want to take a look to GnuWin32, which has an implementation of sed (among other things)

I also found an alternative in Is there any sed like utility for cmd.exe? to create a VBScript similar to sed, you may want to check it out.

score 0 · Answer 2 · answered Apr 04 '13 at 19:03

Try doing this trick to get the headers:

select col1, col2 . . . -- export columns go here
into outfile . . .
from ((select 1 as isheader, 'Col1' as col1, 'Col2' as col2, . . .    -- headers go here
      ) union all
      (select 0 as isheader, col1, col2, . . . . -- data goes here
      )
     ) t
order by isheader desc

In practice, removing the order by will probably also work:

select *
into outfile . . .
from ((select 'Col1' as col1, 'Col2' as col2, . . .    -- headers go here
      ) union all
      (select col1, col2, . . . . -- data goes here
      )
     ) t

MySQL does not guarantee that the headers will go first, but they do in practice.

Also, to do this, make sure that your columns are properly converted to the string format that you want.

score 0 · Answer 3 · answered Apr 04 '13 at 19:30

0

OMG im so dumb. The answer is very simple, instead of exporting it to a .csv file i exported it to .sql. LOL Very simple and easy

answered Apr 04 '13 at 19:30

Cadz

139
3
21

how to properly export an sql data to .csv

3 Answers3