Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


adding a mysql column to a tab delimited file

Posted on 2011-04-22
Medium Priority
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 39

Expert Comment

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

Expert Comment

ID: 35451529
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?

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html
LVL 81

Expert Comment

ID: 35453029
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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 35453753
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

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

Accepted Solution

arnold earned 2000 total points
ID: 35454612
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

ID: 35457015
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 81

Expert Comment

ID: 35457098
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

ID: 35463118
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

580 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