adding a mysql column to a tab delimited file

Posted on 2011-04-22
Last Modified: 2012-05-11

I have a mysql database with a table called Feature and an autoinc primary key.
I also have a tab delimited file with one row per feature. This file does not contain the Feature autoinc primary key. The rows in the tab delimited file are in the same order as the rows were inserted into the mysql database.

Basically i want to add the feature_id pk as a column to this tab delimited file. I could write a script but I was wondering if there was an easier way to export a mysql table column(s) to an existing tab delimted file

I've tried searching for this but all the results that come back just tell me how to export data as a new tab file but i want to add to an existing file

Question by:andieje
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    If you open it in excel it's pretty easy to add a autoincrement Pk column
    LVL 24

    Expert Comment

    Is your ultimate objective to import the file into the table?  Why not just use the load data command, map the columns, and let the auto_increment in mysql assign the values?

    LVL 76

    Expert Comment

    Is the data in the mysql database complete, accurate and have the identical information that is in your tab delimited file??
    If the answer is Yes, given that you want to match the id column from the mysql to the name in the Tab delimited file, exporting the data from mysql is the way to go.
    Alternatively, you can insert a column in your Tab delimited file while it is open in excel, or openoffice equivalent or use a while/for loop
    to add an autoincrementing number to each row.
    A2 =($A1+1)
    Fill down
    Note that Saving data in a tab delimited/comma delimited value format will only save the values and strip out the function.

    Author Comment

    I should have pointed out that i want the process to be automated by a script or a mysql command i can call from a shell script.

    Yes, arnold, the data in the database is identical to the data in the tab file except that the tab file doesn't have the autoinc primary key

    Author Comment

    My ultimate objective is to get the autoinc primary key into the tab file using a script and not a spreadsheet
    LVL 76

    Accepted Solution

    Then use the
    echo "select * from tableName" |mysql -u <username> --password="password"  | sed -e 's/\s+/\t/g' > fileofinterest

    in a cron job.

    fileofinterest will have all the data you want with all the autoinc information in tab separated format.

    Author Comment

    that just exports the fields from the table into a new file and not into my existing tab file.

    However i could then merge the 2 tables with the paste command
    LVL 76

    Expert Comment

    If the tab file is the same data that you imported what is the issue with exporting the data back that will include the auto-increament?

    What is the end goal for you?

    Author Comment

    the tab file i have now contains a lot more data than the data that was originally imported. i just wanted to add the id from the database to it (i wasn't going to import the new extended data set)

    but exporting the id and pasting the 2 files works fine

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    MySQL 5.6 or MariaDB 15 63
    Need help with a query 7 53
    MySQL Query for stock delivered and sold 11 39
    MySQL tables for vtiger CRM 4 28
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now