• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1612
  • Last Modified:

MYSQL - OUTFILE to fixed field length with headers

I'm trying to export a file from MySQL in fixed length format.  I found some code that I'll paste below, which doesn't seem to work.  My understanding was that if I use: TERMINATED BY '' and basically put nothing in the single quotes, that that would fill the space with spaces, therefore creating a fixed field format, but it doesn't seem to work.  It just creates a compact version with no delimiters.

BONUS Question:
My ultimate goal is to create a text file with the specific fields listed in the attached image.  The text file is an odd header format type file that I'm not too familiar with.  I put an example of what it ends up looking like.  I just used 3 fields to show you.  Basically the first line is tagged with HDR and then the appropriate fields follow in fixed field format.  Then the next line is tagged with DET and the next fields follow.  Then the record ends and a new record with these 2 lines, and so on and so on.  Has anyone done this before?  Using MySQL and ASP?  Thanks!
HDR CUSTOMERNUMBER PURCHASEORDER
DET PARTNUMBER QUANTITY
HDR CUSTOMERNUMBER PURCHASEORDER
DET PARTNUMBER QUANTITY
Set RS = Conn.Execute ("SELECT hdrrecordlayout,customernumber,ordersid,companyname,soldtoaddresskey,shiptoaddresskey,shipviafield,dropcompanyname,dropaddress1,dropaddress2,dropcity,dropstate,dropzipcode,dropcountry,dropphone,detrecordlayout,partnumber,quantity,unitprice,memofield,shiptoaddresskeychange,printmemo INTO OUTFILE 'data.txt' FIELDS TERMINATED BY '' ENCLOSED BY '' LINES TERMINATED BY '\n' FROM orders;")

Open in new window

fields.jpg
0
ecolley
Asked:
ecolley
  • 2
1 Solution
 
UmeshCommented:
>>>I'm trying to export a file from MySQL in fixed length format.  I found some code that I'll paste below, which doesn't seem to work.  My understanding was that if I use: TERMINATED BY '' and basically put nothing in the single quotes, that that would fill the space with spaces, therefore creating a fixed field format, but it doesn't seem to work.  It just creates a compact version with no delimiters.


If you use TERMINATED BY '' and put nothing inside

Your OUT file's column width will be decided by

ColumnSize  = Actual_Occupied+Blank spaces
25    = Umesh Shastry+spaces
e.g

CREATE TABLE `employees` (              
             `Name` varchar(25) default NULL,      
             `phone_number` int(11) default NULL  
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

SELECT * INTO OUTFILE '/out.txt'
  FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY ''
  LINES TERMINATED BY '\n'
  FROM employees;



name                      phone
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   
Umesh Shastry            89898989   

Open in new window

0
 
UmeshCommented:
Coming to your next question...  MySQL won't allow you to append data to the existing file using the SELECT INTO OUT FILE technique..  Instead you can make use of any server side script  and do the required stuff.
0
 
ecolleyAuthor Commented:
Thanks.  That makes sense.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now