Solved

Ms sql server 7 text export issue

Posted on 2014-11-03
10
99 Views
Last Modified: 2014-12-09
Hi,
Am exporting data from a sql 7.00.842 server running on Chinese windows be 4.0 server,  build 1381 sp6.  Db codepage is cp950 though the text is all English,  with some ref tags in the longer data.

When exporting using the data export wizard,  I'm seeing a 2xxxxxx length text field output to comma delimited CSV,  with or without enclosing quote marks.  It will only output as ANSI, no unicode.

Opening the text file in excel 2007 I'm not seeing the normal CSV import wizard,  opens straight into a sheet... The last longer field T the end of the record with rtf tags (several preceding fields of similar size and content appear to be unaffected so far)  is merged with the first field which is a unique record identifier number.

It's a pretty old box so I'm reluctant to tinker with it until I understand better what the issue could be.

Any pointers or suggested fixes greatly appreciated...
0
Comment
Question by:Ringah
  • 5
  • 4
10 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40419890
Well, use a text editor like Notepad++ or sublime. Excel may already have formatted the content to visualize it.
0
 

Author Comment

by:Ringah
ID: 40420904
Thanks,  tried that,  the first and last fields are definitely concantentenated..thinking maybe need to code to detail to..
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40427604
Can you show an example of a few lines, please? If possible with output from a hex editor, showing the actual character values involved. You can use Notepad++ or UltraEdit for the task.

BTW, you can also check for the LF and CR characters in your text stream by importing it into a test table and searching for them (char(10) and char(13) resp.). If you use charindex to do that, and find that you're just getting char(10) alone - no char(13) - then you're reading in text originating in a Linux or UNIX system of some sort, or else text that's been processed by such a system while en route to you, and you can instruct SSIS accordingly.

Mike
0
 

Author Comment

by:Ringah
ID: 40432755
Hi Mike,

Here's a sample of the text output from Notepad++ :

"ProjectID","CategoryID","Reference","Grouping","BC","Title","CV","Strategy","Status","Status","Teaming","LobbyStatus","Misc","Remark","StartDate","CloseDate","Group","FRPrefix"
101,106,"ABCD/1 ",,,"1-2 Any Street, District  [Client] ",,"
{\colortbl\red0\green0\blue0;
\deflang1033\pard\plain\f3\fs16 (In progress)
\par",,"
\viewkind4\uc1\pard\f0\fs16 07.6.00 ABC sent DEF letter(draft prepared).
\par
\par \lang1028\f1 Last followed up 12.1.2011.\lang1033\f2
\par",,,"
{\colortbl\red0\green0\blue0;
\deflang1033\pard\tx-720\plain\lang2057\f3\fs16 4.3.01 informed that Group had intervened.
\par \pard\plain\lang2057\f3\fs16 Targetted for adjustment. \plain\f2\fs16
\par",,21-2-1997 0:00:00,22-3-2011 0:00:00,1,0

Can't see any LF and CR chars in there anywhere...the data is from SQL 7 on Chinese NT4...its an old server...what this exercise did reveal is that mostly its the last of the longer fields ("Misc" >255 chars) with RTF tags - in later versions of SQL would be memo field but in this one its TEXT, no unicode - that seems to merge into the output from the first field after running through DTS, so am digging into different ways of packaging to see if its a glitch that I can happen upon a workaround for...failing that the first field is a number, so far the contents of 'Misc' field seem to be null, or start with RTF code or a letter rather than a number, so could be ok to parse by script...

Thanks for looking at this...
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 40434195
Your lines:
ress)
\par",,"
\viewk

Open in new window

show as
72 65 73 73 29 0A 5C 70 61 72 22 2C 2C 22 0A
r  e  s  s  ) [LF] \ p  a  r  "  ,  ,  " [LF]   

Open in new window

in UltraEdit, so what you've got is text that's been processed by a Linux or UNIX system. Tell SSIS to import it with UNIX line breaks (0A) and not Windows ones (which are really from DOS which inherited them from CP/M and DEC machines!). See the attached screenshot for the screen in SSIS where you tell it!

hth

Mike
Screenshot-from-2014-11-10-21-52-30.png
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Ringah
ID: 40435119
Mike,

Got it...you were spot on...the CSV was exported by DTS from SQL 7 so guess this could be a legacy feature for compatibility or other reasons....anyway sincere thanks for the insight, and the fix...
0
 

Author Closing Comment

by:Ringah
ID: 40435130
Spot on, the insight, and the fix...invaluable for anyone exporting from older versions of MS SQL Server,
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40436412
Thanks for the praise! Thanks also for returning and confirming the fix.

Mike
0
 

Author Comment

by:Ringah
ID: 40488739
Mike,

A quick followup - after I got the data into FM Pro found that vertical spacing (manual - RETURN key used in the legacy VBA/SQL7 app) was consistently out by +-2 or 4 lines where more than one linespace is present, eg...

Col A                 Col B                Col C
Lorem ipsum  Lorem Ipsum  Lorem Ipsum

Lorem ipsum  Lorem Ipsum  Lorem Ipsum


                                                    Lorem Ipsum



Lorem Ipsum

I guess I could have exported CF instead of LF to the CSV using SQL7 DTS...cringe...

So, to come out as a single linespace at the corresponding vertical position in the new FM Pro 13 text field...eg..

Col A                 Col B                Col C
Lorem ipsum  Lorem Ipsum  Lorem Ipsum

Lorem ipsum  Lorem Ipsum  Lorem Ipsum

 ....I need to export from SQL 7 DTS with Unix-OS/X (LF) breaks as you originally suggested (rather than the SQL 7 DTS defaults which seem to be ANSI / CF LF) ?

Again, many thanks...
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40490230
Hi Ringah!

Thanks ...... we try hard to fix people's problems :)

Good luck with the project.

Mike
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

948 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

20 Experts available now in Live!

Get 1:1 Help Now