Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A good Suggestion for a Delimiter File 1

Status
Not open for further replies.
meny21

meny21

IS-IT--Management
Oct 4, 2002
101
MX
Hi Guys,

I Need Ideas about have to build this issue, maybe you got good sugestions...

The thing is that I need to create a File using Pipes "|" delimiters, for example like this:

CFD|2.0||5847|012345678901234567890|4598|2007|ABC|A|1|20/09/2007|15:12:03|

First I thought in create a SQL Table or View that Had all the information that I needed, then use DTS and create my file... And it works!!! but here in this file I have Header and Detail Information like this

Header1|Header1|Header1|Detail1|Detail1|Detail2|Detail2|Detail2|Detail3|Detail3|

*Note different Details and one header*

And of course I couldn't create only one sql table because it repeats the Header in every row that I have for detail, I Mean:

Field1 Field2 Field3 Field4
Header1, Header1, Detail1, Detail1
Header1, Header1, Detail2, Detail2, etc....

and If I export this to text file it repeats the header in every text row when I know that I use the header just once...

Any suggestions guys?

I appreciate your help and I hope I explain myself

Regards

MR





 
Sort by date Sort by votes
How did you define the DTS package? Normally, if you use a Transform Data Task to create a "Text File (Destination)" output file, and specify "First row contains column names", it'll output a file something like this:

Hdr1|Hdr2|Hdr3....
Fld1|Fld2|Fld3....(row 1)
Fld1|Fld2|Fld3....(row 2)
etc.

Can you post the view SQL and the details of the DTS package?
 
Upvote 0 Downvote
Thanks, for example I have this table Created:

TableHeader:
HeaderField1
HeaderField2
HeaderField3

DetailTable:
DetailField1
DetailField2
DetailField3
DetailField4

And I need to convert this SQL Tables on this Delimiter Text File:

HeaderField1|HeaderField2|HeaderField3|DetailField1|DetailField2|DetailField3|DetailField4

I think is better explain..

Thanks again..

MR
 
Upvote 0 Downvote
So you want the output file to look like this?
HeaderField1|HeaderField2|HeaderField3|DetailField1|DetailField2|DetailField3|DetailField4
HeaderField1|HeaderField2|HeaderField3|DetailField1|DetailField2|DetailField3|DetailField4
HeaderField1|HeaderField2|HeaderField3|DetailField1|DetailField2|DetailField3|DetailField4

If so, then just add the column header names to your view:
Code: 
CREATE VIEW MyView
AS
SELECT 'Header1', 'Header2', 'Header3', Field1, Field2, Field3 .....
FROM Mytable1
JOIN ......
Make sense?
 
Upvote 0 Downvote
Thanks TheSql, I think with an example will be a better and understable view...

HeaderTable:
Index
field1 (InvoiceNo)
field2 (InvoiceDescription)

DetailTable
Index
Field1 (ItemCode)
Field2 (Item Description)

Can be one header and "N" details

For example:

InvoiceNo: 1000
InvoiceDescription: "My Invoice"

With some details:

ItemCode: 20
Item Description: "Book"

ItemCode: 30
Item Description: "Magazine"

And the output will be:

1000|My Invoice|20|Book|30|Magazine

It is possible?

Thanks a lot!

 
Upvote 0 Downvote
Gotcha. That won't be very easy in SQL - the reason being that you don't know how many details may be associated with the invoice. You may be able to do something tricky in DTS, but I'd just do it in a client programming language (ex. vb, c#, even ASP etc). It would be very easy there. Alternatively, you could use a sproc and have a SQL cursor iterate through each of the details, appending the field values to an output string, but again, the performance would suffer greatly (cursors are SLOW).

I'd stick with building the file in another language though.
 
Upvote 0 Downvote
Thanks TheSQL I will check the Programming option...

Regards!
 
Upvote 0 Downvote
Why not XML?
Just asking :)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
Borislav, how would you do it using XML? Using a client language, or from within SQL? I'm not questioning your solution - I just don't use XML very frequently.

Thanks
 
Upvote 0 Downvote
Hi bborissov,

I'm new in XML, what do you think? Will be easier? if yes how can I start about trying with XML for this issue?

I was thinking in the programming by XML looks interesting...

Regards!
 
Upvote 0 Downvote
First. Sorry for late reply, I've been busy till yesterday and have no time to answer. Yesterday I felt so stupid :)

Every modern FrontEnd languages can handle XMLs.
BUT XML files are not the same as regular TEXT files, no matter what their format are. They by default is bigger, and present full table structures and field by field values.
I can't explain it better, sorry (just have not enough english words for that), but here an example:
Code: 
DECLARE @Test  TABLE (Id int, Name varchar(200))
DECLARE @Test1 TABLE (Id int, FK int, Details varchar(200))
INSERT INTO @Test VALUES(1, 'Test1')
INSERT INTO @Test VALUES(2, 'Test2')

INSERT INTO @Test1 VALUES(1,1, 'Detail1_1')
INSERT INTO @Test1 VALUES(2,1, 'Detail1_2')
INSERT INTO @Test1 VALUES(3,1, 'Detail1_3')

INSERT INTO @Test1 VALUES(4,2, 'Detail2_1')
INSERT INTO @Test1 VALUES(5,2, 'Detail2_2')
INSERT INTO @Test1 VALUES(6,2, 'Detail2_3')
INSERT INTO @Test1 VALUES(7,2, 'Detail2_1')
INSERT INTO @Test1 VALUES(8,2, 'Detail2_2')
INSERT INTO @Test1 VALUES(9,2, 'Detail2_3')

SELECT *
       FROM @Test Test
INNER JOIN @Test1 Test1 ON Test.Id = Test1.FK
FOR XML AUTO

That will produce the following result:
Code: 
<Test Id="1" Name="Test1">
  <Test1 Id="1" FK="1" Details="Detail1_1" />
  <Test1 Id="2" FK="1" Details="Detail1_2" />
  <Test1 Id="3" FK="1" Details="Detail1_3" />
</Test>
<Test Id="2" Name="Test2">
  <Test1 Id="4" FK="2" Details="Detail2_1" />
  <Test1 Id="5" FK="2" Details="Detail2_2" />
  <Test1 Id="6" FK="2" Details="Detail2_3" />
  <Test1 Id="7" FK="2" Details="Detail2_1" />
  <Test1 Id="8" FK="2" Details="Detail2_2" />
  <Test1 Id="9" FK="2" Details="Detail2_3" />
</Test>
As you see here you have all, Header record and all child records. But sometimes you need to be more specific, field types etc. Then you have to build a XML schema. With that schema all outside languages could create a proper cursor, table, recordset based on that schema.
The same code with one addition:
Code: 
DECLARE @Test  TABLE (Id int, Name varchar(200))
DECLARE @Test1 TABLE (Id int, FK int, Details varchar(200))
INSERT INTO @Test VALUES(1, 'Test1')
INSERT INTO @Test VALUES(2, 'Test2')

INSERT INTO @Test1 VALUES(1,1, 'Detail1_1')
INSERT INTO @Test1 VALUES(2,1, 'Detail1_2')
INSERT INTO @Test1 VALUES(3,1, 'Detail1_3')

INSERT INTO @Test1 VALUES(4,2, 'Detail2_1')
INSERT INTO @Test1 VALUES(5,2, 'Detail2_2')
INSERT INTO @Test1 VALUES(6,2, 'Detail2_3')
INSERT INTO @Test1 VALUES(7,2, 'Detail2_1')
INSERT INTO @Test1 VALUES(8,2, 'Detail2_2')
INSERT INTO @Test1 VALUES(9,2, 'Detail2_3')


SELECT *
       FROM @Test Test
INNER JOIN @Test1 Test1 ON Test.Id = Test1.FK
FOR XML AUTO[COLOR=red][b], XMLSCHEMA [/b][/color]
Will produce this:
Code: 
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:sqltypes="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/sqltypes"[/URL] elementFormDefault="qualified">
  <xsd:import namespace="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/sqltypes"[/URL] schemaLocation="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"[/URL] />
  <xsd:element name="Test">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="schema:Test1" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
      <xsd:attribute name="Id" type="sqltypes:int" />
      <xsd:attribute name="Name">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1049" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="200" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="Test1">
    <xsd:complexType>
      <xsd:attribute name="Id" type="sqltypes:int" />
      <xsd:attribute name="FK" type="sqltypes:int" />
      <xsd:attribute name="Details">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1049" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="200" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<Test xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" Id="1" Name="Test1">
  <Test1 Id="1" FK="1" Details="Detail1_1" />
  <Test1 Id="2" FK="1" Details="Detail1_2" />
  <Test1 Id="3" FK="1" Details="Detail1_3" />
</Test>
<Test xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" Id="2" Name="Test2">
  <Test1 Id="4" FK="2" Details="Detail2_1" />
  <Test1 Id="5" FK="2" Details="Detail2_2" />
  <Test1 Id="6" FK="2" Details="Detail2_3" />
  <Test1 Id="7" FK="2" Details="Detail2_1" />
  <Test1 Id="8" FK="2" Details="Detail2_2" />
  <Test1 Id="9" FK="2" Details="Detail2_3" />
</Test>

I hope someone could add more details about XML here.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
290
Andrzejek
Andrzejek
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
230
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
171
grnzbra
grnzbra
Colin Burton
  • Locked
  • Question
Determine if a file exists on s different server to the SQL Server 1
Replies
2
Views
406
gmmastros
gmmastros
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
351
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top