Solved

SQL RECORDSET IN ASCII FORMAT WITH SINGLE TAB DELIMITOR

Posted on 2013-10-24
16
288 Views
Last Modified: 2016-02-11
Hello I have the below sql query:

	select P.MedRecNo ,CONVERT(char(10),DispenseDt,101)  as Dispensedt,'BB','H0004',(CAST(Qty AS INT)) as QTY,Qty/TtlPrice as UnitPrice  from Billing B
 left join Patients p on B.PatID = p.PatID and B.FacID = p.FacID
 where B.facid ='1' and B.MOP='FACI' 
and Billed between '2013-9-1 00:00:00' and '2013-9-30 00:00:00'
 and B.InvoiceGrp in ('MEDA','MGCARE')

Open in new window


I have to generate the recordset as

MedRecNo<Tab>Dispensedt<Tab>BB<Tab>H0004<Tab>QTY<Tab>UnitPrice  
 but the data should be in ASCII format.
0
Comment
Question by:Star79
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 39598550
The low-tech solution would be to just throw a tab character between each column, and return it as a single column
Declare @tab char(1) = char(9) 

select P.MedRecNo  + @tab + CONVERT(char(10),DispenseDt,101) + @tab + 'BB'+ @tab +'H0004'+ @tab +(CAST(Qty AS INT)) + @tab + CAST(Qty/TtlPrice as varchar(max)) as column_name
from Billing B
   left join Patients p on B.PatID = p.PatID and B.FacID = p.FacID
where B.facid ='1' and B.MOP='FACI' 
   and Billed between '2013-9-1 00:00:00' and '2013-9-30 00:00:00'
 and B.InvoiceGrp in ('MEDA','MGCARE')

Open in new window

0
 

Author Comment

by:Star79
ID: 39598575
how to convert the result set to ascii
Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39598681
Explain what you mean by 'result set to ascii'.
If you want the string '<Tab>' instead of the tab character, then just replace the above with..

Declare @tab char(5) = '<tab>'
0
 

Author Comment

by:Star79
ID: 39598709
hello jim, I want the columns to be returned as ASCII character
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39598842
Give us an example of the output you wish to see.
0
 

Author Comment

by:Star79
ID: 39598931
sorry i dont have an example..just that I have the requirement whihc is the same query as you made with the tab but the columns need to be convered to ASCII
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 39598977
Is this some kind of a homework assignment?  The Experts are limited in what they can do for homework questions.

The expert has ask you to post an example and if you can provide that, how do you know when a solution is working?  What are you going to compare it to?

COBOLdinosaur, Topic Advisor.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39599003
Are you trying to say you need the result exported into a file, coded in your current code page (because ASCII is a 7bit subset of the 8bit code page representation in non-Unicode file formats)? If so, which tool are you using? Is commandline ok?
0
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.

 
LVL 100

Expert Comment

by:mlmcc
ID: 39599335
Do you want the output to be like

ABC<tab>DEF

065 066 067 009 068 069 070

If so what tools do you want to do this in?  Do you want it to be just a SQL command or can a .Net application be written to do the output?

mlmcc
0
 

Author Comment

by:Star79
ID: 39600273
hello mlmcc,
yes I want something like
ABC<tab>DEF

yes the abc and def should be ASCII and I will have to use the sql command only as we dont use .net here

The client wants a data extract in ascii format delimited by tab.

Thanks.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39600345
I think your use of the term ASCII is confusing.  ASCII is a character encoding used by computers to internally represent alphanumeric data.

If I create a text file and look a it in Notepad what would it show?
ABC     DEF    GHI   JKL

The spaces between are tabs
I believe the first comment should provide that output

or do you want to see the ASCII values of the characters as in

65666709797172097374750976777809

mlmcc
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39600373
>yes I want something like ... ABC<tab>DEF
And you were given it in the very first comment.

>sorry i dont have an example..just that I have the requirement whihc
Demonstrate for us that you know what you are asking for, because right now we're not convinced, and an example output would be an excellent place to start.

'I have a requirement' without giving us an example smells like homework, and my mind-reading skills have some limitations.
0
 

Author Comment

by:Star79
ID: 39600374
thanks mlmcc..
infact Iam confused too

Text files stored in ASCII format are  called ASCII files.

So once I run the first comment query and copy the results to a notepad and save as test.txt
Is test.txt in ASCII format
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 39600399
--Is test.txt in ASCII format?
Yes

Remember ASCII refers to the representation of the character set in the computer (binary) and not what you and I see in Notepad.

To see it in true ASCII representation you need to look at it in a HEX editor which shows the actually encoding.  

I could store files in EBCIDIC format or encoding, IBM mainframes of old did that.  It is just a different encoding scheme and the ordering of the character set is different.  You can see the difference in the encoding at this link

http://publib.boulder.ibm.com/infocenter/lnxpcomp/v8v101/index.jsp?topic=%2Fcom.ibm.xlf101l.doc%2Fxlflr%2Fasciit.htm

mlmcc
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39600412
UltraEdit is a super text editor app, which offers a 30-day free trial.

Download the app, then open it, then open any text file, then hit {ctrl}-H to view all characters in the file in Hex.

I've used this app multiple times when dealing with files created by various mainframes that insert characters that SSIS could not handle.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39600612
Of course there are other tools than UltraEdit for this specific purpose, which are free, like HxD (and many other Hex Viewers).
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now