A suggestion from SQL Table to delimiter Text File 1

[meny21]

Hi guys,

I would like to have suggestions about how to do this thing:

I have two tables:

HeaderTable:
Index
field1 (InvoiceNo)
field2 (InvoiceDescription)

DetailTable
Index
Field1 (ItemCode)
Field2 (Item Description)

it Can be only 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 a delimiter text file like this:

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

It is possible if this comes from a SQL Tables to convert to a delimiter Text File on Visual Basic?

Some suggestions guys about how to interprete this on VB?

Thanks a lot!

MR

 

[Swi]

After you query you tables and return a recordset you can use the recordset object to write a delimited string. Here is some old code that connects to an Excel file and writes the data as a pipe delimited file. It should give you a starting point.

Private Sub Command1_Click()
' Add a reference to Microsoft Scripting Runtime
' Add a reference to Microsoft ActiveX Data Object X.X Library
Dim conn As ADODB.Connection
Dim fso As FileSystemObject
Dim rs As ADODB.Recordset
Dim OutStream As TextStream

' Initialize objects
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set fso = New FileSystemObject
Set OutStream = fso.OpenTextFile("C:\Testit.txt", ForWriting, True)

' Make connection and pull data
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Testit.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""
rs.Open "SELECT * FROM [sheet1$]", conn

' Write tab delimited data to output text file
OutStream.Write rs.GetString(adClipString, -1, "|", vbCrLf)

' Closes and destroys objects from memory
OutStream.Close
rs.Close
conn.Close
Set fso = Nothing
Set rs = Nothing
Set conn = Nothing

' Prompts user of completion
MsgBox "Done!", vbInformation
End Sub

Swi

 

[Swi]

Sorry, comment should have read Pipe delimited and not tab delimited.

Private Sub Command1_Click()
' Add a reference to Microsoft Scripting Runtime
' Add a reference to Microsoft ActiveX Data Object X.X Library
Dim conn As ADODB.Connection
Dim fso As FileSystemObject
Dim rs As ADODB.Recordset
Dim OutStream As TextStream

' Initialize objects
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set fso = New FileSystemObject
Set OutStream = fso.OpenTextFile("C:\Testit.txt", ForWriting, True)

' Make connection and pull data
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Testit.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""
rs.Open "SELECT * FROM [sheet1$]", conn

' Write [red]pipe[/red] delimited data to output text file
OutStream.Write rs.GetString(adClipString, -1, "|", vbCrLf)

' Closes and destroys objects from memory
OutStream.Close
rs.Close
conn.Close
Set fso = Nothing
Set rs = Nothing
Set conn = Nothing

' Prompts user of completion
MsgBox "Done!", vbInformation
End Sub

Swi

 

[JerryKlmns]

This comes from access but you could convert it for your needs
faq701-4233