Solved

Ms sql server 7 text export issue

Posted on 2014-11-03
10
109 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 45
how to just get time from a date 6 32
Stored Proc - Rewrite 42 56
SQL - Curser to do an insert based on a select 2 10
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 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