?
Solved

mySQL import scripting

Posted on 2011-10-25
7
Medium Priority
?
329 Views
Last Modified: 2012-05-12
how do I import non-delimited data into mysql?

the column location is identified by field number

example

column 1 is fields 1 through 14
column 2 is fields 15 through 40
0
Comment
Question by:gevansmdes
7 Comments
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 37026352
Here's the MySQL page http://dev.mysql.com/doc/refman/5.1/en/load-data.html but I don't see a method for position-delimited files.  I would import it into Excel/LibreOffice first and then export as CSV.  Then MySQL or phpmyadmin can import it.
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37029920
see below example

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37029984
First create temparaly table
then use below code to import file
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

then write following code to export
SELECT Field14,Field40 INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

then Import back using following code
LOAD DATA INFILE 'result.txt' INTO TABLE persondata (Field1,Field15);


or you can use following steps
http://computeraxe.com/how-to-import-data-file-mysql-phpmyadmin/
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:ukerandi
ID: 37029987
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37029994
this will work
load data infile 'yourfilename.txt'
into table [Yourtablename]
fields terminated by '|'
lines terminated by '\n'
(col1,
col15,
@col14,
@col40)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37030750
Please post a representative example of the data in the code snippet (or attach a file).  Once we can see exactly what sort of thing you have we can probably offer some concrete suggestions.  Thanks, ~Ray
0
 

Author Closing Comment

by:gevansmdes
ID: 37056565
thank you
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

809 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