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
ecolleyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshSenior Principal Technical Support EngineerCommented:
>>>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
UmeshSenior Principal Technical Support EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ecolleyAuthor Commented:
Thanks.  That makes sense.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.