Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

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...
0
Ringah
Asked:
Ringah
  • 5
  • 4
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
DcpKingCommented:
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
 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now