Solved

Ms sql server 7 text export issue

Posted on 2014-11-03
10
94 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 32

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 42
SQL Server RDS clr assembly 4 35
SQL server 2008 SP4 29 33
Azure SQL DB? 3 15
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Viewers will learn how the fundamental information of how to create a table.

707 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

16 Experts available now in Live!

Get 1:1 Help Now