Ms sql server 7 text export issue

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

[Webinar] Streamline your web hosting managementRegister Today

x
 
DcpKingConnect With a Mentor Commented:
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
 
ste5anSenior DeveloperCommented:
Well, use a text editor like Notepad++ or sublime. Excel may already have formatted the content to visualize it.
0
 
RingahAuthor Commented:
Thanks,  tried that,  the first and last fields are definitely concantentenated..thinking maybe need to code to detail to..
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
DcpKingCommented:
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
 
RingahAuthor Commented:
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
 
RingahAuthor Commented:
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
 
RingahAuthor Commented:
Spot on, the insight, and the fix...invaluable for anyone exporting from older versions of MS SQL Server,
0
 
DcpKingCommented:
Thanks for the praise! Thanks also for returning and confirming the fix.

Mike
0
 
RingahAuthor Commented:
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
 
DcpKingCommented:
Hi Ringah!

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

Good luck with the project.

Mike
0
All Courses

From novice to tech pro — start learning today.