Solved

Space Delimited Sql File

Posted on 2016-08-31
4
104 Views
Last Modified: 2016-09-15
A vendor has asked me to create an extract file.
In this extract file, certain fields need to be in a particular position.
For example:
Employee Position 1-8
Employee First Name Position 9-26
Employee Middle Name Position 27-27
Employee Last Name Position 28-42

I was thinking about doing this in oracle sql, anyone have any suggestions on how to do this?
0
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 41778345
This is quite simple in Oracle SQL.  Simply concatenate the four column values together into one result column, and use "to_char" on the first column (assuming that is a numeric column) and use either "rpad" or "substr" (or maybe both) on the first and last name columns, plus possibly an "nvl" on the middle name column in case that is null (blank).

Something like this should work (assuming the actual column names are:
employee_id
first_name
middle_name
last_name)

select to_char(employee_id,'99999999')
||rpad(substr(first_name,1,17),17,' ')
||nvl(middle_name,' ')
||rpad(substr(last_name,1,15),15,' ') "Output"
from [your_table]
where ...
order by  ...

(The "where..." and "order by ..." clauses are optional.)

I think the lengths for first_name and last_name are correct.  But, you will have to test them.

If you use SQL*Plus to run this, you will want to use these commands before running the query:
set pagesize 0;
set linesize 80;
set trimspool off;
set head off;
set feedback off;
spool C:\temp\my_file.txt
 [your SQL query here]
spool off;

The "spool" command will open an output file, so change "\temp" to a valid directory on your system if necessary, and change "my_file.txt" to whatever you want the output file to be named.
1
 

Author Comment

by:metalteck
ID: 41791585
This is working for me. What is the code I would need to use for a date?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41791658
That depends on what format you want the "date" column value displayed in, and whether you want to include the time-of-day, or not.  By default, Oracle "date" columns include the time-of-day, as well as the date.  But, depending on the application, the time-of-day may or may not be included.  If it is not included by your application, that is the same as an actual time of exactly midnight.

For a date column, you would want to use the Oracle "to_char" operator and a format mask so Oracle puts the "date" value into characters as your vendor wants them to be displayed.

For example, if your "date" column is named "my_date" and the actual "date" value is Sept 9, 2016 1:13:45pm, you could any of these in your query, or something similar:

Query expression                                                 Result
--------------------------------------------------------------    --------------
to_char(my_date,'MM/DD/YYYY')                        09/09/2016
to_char(my_date,'YYYYMMDD')                           20160909
to_char(my_date,'Mon. Dy, YYYY')                       Sep. 9, 2016
to_char(my_date,'MM/DD/YYYY HH24:MI:SS')   09/09/2016 13:13:45

Note, there are dozens of other possible combinations or variations for format elements for "date" values in Oracle queries when you use "the "to_char" operator.  Check the Oracle documentation for "to_char" or "format mask" for a complete list.

You can concatenate this "to_char" column result just like you can with the other datatypes, something like this:
select to_char(employee_id,'99999999')
 ||rpad(substr(first_name,1,17),17,' ')
 ||nvl(middle_name,' ')
 ||rpad(substr(last_name,1,15),15,' ')
 ||to_char(my_date,'MM/DD/YYYY') "Output"
 from [your_table]
0
 

Author Comment

by:metalteck
ID: 41791738
Ok, I will try that.
And what is the syntax for blank spaces?
My extract has some fields that will be blank, and at various lengths.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

724 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