How to convert SQL file to CSV?

Question

I dumped a sql table for cities, regions & countries.

I have the .sql files in my on-premises. I want to convert them to CSV format.

So I have two questions

1): In Mac, how can I do this?

2): While doing researcH, I found this site. Therefore I tried with this code:

CREATE TABLE IF NOT EXISTS `countries` (

  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `code` varchar(10) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=231 DEFAULT CHARSET=latin1;

-- Dumping data for table dddblog.countries: ~218 rows (approximately)

/*!40000 ALTER TABLE `countries` DISABLE KEYS */;

INSERT INTO `countries` (`id`, `name`, `code`) VALUES

    (1, 'Andorra', 'ad'),

    (2, 'United Arab Emirates', 'ae'),

    (3, 'Afghanistan', 'af'),

    (4, 'Antigua and Barbuda', 'ag'),

    (5, 'Anguilla', 'ai'),

    (6, 'Albania', 'al'),

    (7, 'Armenia', 'am'),

    (8, 'Netherlands Antilles', 'an'),

    (9, 'Angola', 'ao'),

    // Other countries

/*!40000 ALTER TABLE `countries` ENABLE KEYS */;

And when I click on Convert, I get an error: Missing SELECT STATEMENT.

Answer 1

To convert your SQL files to CSV format on a Mac, you can follow these steps:

1. Open Terminal on your Mac.

2. Navigate to the directory where your SQL files are located using the `cd` command. For example, if your files are in the "Documents" folder, you can use the following command:

  

   cd ~/Documents

3. Once you are in the correct directory, you can use the `mysql` command-line tool to import the SQL files and export them as CSV files. Here's an example command:

  

   mysql -u username -p -e "SELECT * FROM database_name.table_name" > output.csv

   

   Replace `username` with your MySQL username, `database_name` with the name of the database containing your table, `table_name` with the name of the table you want to export, and `output.csv` with the desired name for your CSV file.

4. Press Enter to execute the command. You will be prompted to enter your MySQL password.

5. The command will execute the SELECT statement and save the output as a CSV file in the current directory.

Regarding the error you encountered on the website you mentioned, it seems to require a SELECT statement to generate the CSV output. Here's an example of how you can modify your SQL code to generate the CSV file using that website:

SELECT * FROM countries INTO OUTFILE 'path/to/output.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Replace `'path/to/output.csv'` with the actual path where you want to save the CSV file. Make sure to specify a valid file path accessible by the MySQL server.

This modified SQL code will export the data from the "countries" table into a CSV file with fields separated by commas (`,`), enclosed in double quotes (`"`), and each record on a new line (`\n`).

After making these modifications, you can try again on the website to convert your SQL code to CSV format.

Answer 2

I think you need to add a SELECT statement. Your message missing SELECT STATEMENT is saying all of this. 

Try it with this code - worked for me:

CREATE TABLE IF NOT EXISTS `countries` (

  `id` smallint(5) NOT NULL ,

  `name` varchar(255) NOT NULL,

  `code` varchar(10) NOT NULL

);

INSERT INTO `countries` (`id`, `name`, `code`) VALUES

    (1, 'Andorra', 'ad'),

    (2, 'United Arab Emirates', 'ae'),

    (3, 'Afghanistan', 'af'),

    (4, 'Antigua and Barbuda', 'ag'),

    (5, 'Anguilla', 'ai'),

    (6, 'Albania', 'al'),

    (7, 'Armenia', 'am'),

    (8, 'Netherlands Antilles', 'an'),

    (9, 'Angola', 'ao')

    ;

SELECT     `id`, `name`, `code` FROM `countries`

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.

Answer 3

To convert your SQL file to CSV format on a Mac, you can use the command line tool `mysql` and `SELECT ... INTO OUTFILE` statement to export the table data as a CSV file. Here's how you can do it:

1. Open Terminal on your Mac. You can find it in the Utilities folder within the Applications folder.

2. Navigate to the directory where your SQL file is located using the `cd` command. For example, if your SQL file is in the Downloads folder, you can use the following command:

   cd ~/Downloads

3. Launch the MySQL command line tool by entering the following command:

   mysql -u <username> -p

   Replace `<username>` with your MySQL username. You'll be prompted to enter your MySQL password after executing this command.

4. Once you're in the MySQL command line tool, switch to the database where the table is located using the following command:

   USE <database_name>;

   Replace `<database_name>` with the name of your database.

5. Now, you can export the table data as a CSV file using the `SELECT ... INTO OUTFILE` statement. Here's an example command:

   SELECT id, name, code

   INTO OUTFILE '/path/to/output/file.csv'

   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

   LINES TERMINATED BY '\n'

   FROM countries;

   Replace `/path/to/output/file.csv` with the desired file path where you want to save the CSV file. Make sure the directory exists and you have write permissions.

   This command selects the specified columns (`id`, `name`, and `code`) from the `countries` table and exports the result into the specified CSV file with fields separated by commas and optionally enclosed in double quotes. Each line is terminated by a newline character.

6. After executing the `SELECT ... INTO OUTFILE` statement, you can exit the MySQL command line tool by entering:

   exit

Now you should have the table data exported to a CSV file in the specified location.

Regarding the error you encountered on the website you mentioned, it seems like the site is expecting a `SELECT` statement to convert the SQL data to CSV. You can modify your SQL code to include a `SELECT` statement before the `INSERT` statements, like this:

SELECT * FROM countries;

Then you can try again on the website to convert the SQL file to CSV format.

Answer 4

To convert your SQL file to CSV format on a Mac, you can follow these steps:

1. Open a terminal window on your Mac.

2. Navigate to the directory where your SQL file is located using the `cd` command. For example, if your SQL file is in the Documents folder, you can use the command: `cd ~/Documents`.

3. Once you are in the correct directory, you can use the following command to convert the SQL file to CSV format:

   sqlite3 -header -csv <your_file_name>.sql > <output_file_name>.csv

   Replace `<your_file_name>` with the name of your SQL file (including the extension) and `<output_file_name>` with the desired name of your CSV output file.

   Note: The `sqlite3` command-line tool is preinstalled on macOS, which allows you to execute SQLite commands.

4. After executing the command, a new CSV file will be created in the same directory containing the converted data.

Regarding the error you encountered when using the online site for conversion, it seems like the site expects a SELECT statement to be present in the SQL code. However, the code you provided is a table creation and data insertion script, which does not include any SELECT statements.

If you still wish to use that online site for conversion, you can modify your code by adding a SELECT statement at the end. For example:

SELECT * FROM `countries`;

This will select all the data from the `countries` table, allowing the conversion site to generate the CSV output correctly.