Streaming text from a view to a file.

Streaming text from a view to a file.

• JohnMaison

  You are talking about parsing correctly in Query analyzer. Are you talking about that the data simply being retrieved and displaying or are you iterating round the fields.

  If you have the data in a single output field then looking at this in query analyzer would show you the data as a single field in table field or the string normal contents in text view.

  However for the iterating through the rows and columns its depending upon the view/dataset to be returned as multiple fields (not a single field). If its a single field then there will only be one column and it will simply print the contents of this column (which would be the entire view in the single field).

  
  

• Mohit Gupta - MSFT

  The following code should work (Its VB Express / 2005). Have you put a breakpoint on any lines and code stepped the code to see the variable that are being set at different stages. I've stepped it through with data from a dataset

  
  Dim dvExport As DataView
  Dim stExport As String = ""

  Dim z As Integer
  Dim y As Integer
  
  dvExport = MyDataSet.Tables(0).DefaultView '//Set the view based upon first table in dataset - change to whatever table it is in the dataset
  For z = 0 To dvExport.Table.Rows.Count - 1
  For y = 0 To dvExport.Table.Columns.Count - 1
  stExport = stExport & dvExport(z).Item(y).ToString & vbCrLf & vbCrLf
  Next
  stExport = stExport & vbCrLf & vbCrLf
  Console.WriteLine(stExport)
  Next

  My.Computer.FileSystem.WriteAllText("C:\Doc\Streaming.txt", stExport, False)

  Your using a view but do you actually need to use a view or can you just use the contents of the datatable in the dataset

  Dim stExport As String = ""
  For Each dr As DataRow In MyDataSet.Tables(0).Rows
  For Each dc As DataColumn In MyDataSet.Tables(0).Columns
  stExport = stExport & dc.ToString & vbCrLf & vbCrLf
  Next
  stExport = stExport & vbCrLf & vbCrLf
  Console.WriteLine(stExport)
  Next

  
  

• panthraz

  Thanks for your help. I've verified that your code works. The problem is that the view I'm calling is formatted in a very complex way with a lot of hard-coded tags, line breaks and carriage returns, very similar to XML. It's a standard financial exchange format called SWIFT and I'm trying to generate a message from the data. Here is one typical record returned from a Query Analyzer page of the view:

  {1:F01CODEUS31XXXX0000000000}{2:I300CODELXXXXN}{3:{113:XXXX}{108:}}{4:
  :15A:
  :20:0509295051
  :22A:NEWT
  :22C:CODE2L1350CODE31
  :83J:/NAME/FIRMNAME
  :15B:
  :30T:20050929
  :30V:20050930
  :36:0,7611350
  :32B:USD61271367,5
  :33B:AUD80500000,
  -}
  

  Could this be what's causing the failure to export data

  
  

• K N Chary

  Further information:

  I can set a breakpoint on this line:

  For Each dr As DataRow In ManForexCLSDataSet.Tables("vwExport").Rows

  but not on the next line:

  For Each dc As DataColumn In ManForexCLSDataSet.Tables("vwExport").Columns

  The first line shows 17 rows of data as the count, which is correct. I don't know why it's skipping the second line.

  By the way, I'm having some trouble with this as F8 doesn't appear to be working. I haven't figured out how to step through the code yet.

  Thanks again.

  Tom

  
  

• alexis.sourceau

  I've pointed to a different view with a more typical array of columns and rows. The first code sample you gave me produces records in sequence with each field separated by two line feeds. The second sample (using direct connection to table) produces the column headings for each record rather than the data itself.

  The text variable is set to "" before the parsing.

  
  

• KienLip

  Additional complications:

  The view is produced as a single output field. Perhaps there are too many characters for the column.

  Once this is set, I need to change the view to a stored procedure so I can change certain internal codes at runtime. Can I modify the procedure you sent me for a stored procedure

  Thanks for your help.

  Tom

  
  

• EvilPenguin

  Replace with this line

  stExport = stExport & dr.Item(dc.ToString).ToString & vbCrLf & vbCrLf

  So after the looping has completed and the prior to writing the file - is the
  variable that is being used to store the contents set to anything.

  If the view is a single field then the iteration wouldnt work - it would find a single column.

  Using a stored procedure wont effect this - as by the time you run this code you have the dataset - which would be populated by a sproc. So sure you maodify the code to populate the dataset to call a stored procedure.

  
  

• Matthew Hoover

  You create a dataview from a datatable not a tableadapter.
  
  
  

• Yael Rootberg

  Thanks for your patience. This is still not working. I modified the view so it produces two columns which parse correctly in Query Analyzer. However, I am still getting an empty text file with that view, although it works fine with other, more typical views.

  Here is what I'm working with currently:

  Dim dvExport As DataView
  Dim stExport As String = ""
  Dim stExportFile As String = ""

  For Each dr As DataRow In MyDataSet.Tables("vwExport").Rows
  For Each dc As DataColumn In MyDataSet.Tables("vwExport").Columns

  stExport = stExport & dr.Item(dc.ToString).ToString & vbCrLf & vbCrLf
  Next
  stExport = stExport & vbCrLf & vbCrLf
  Console.WriteLine(stExport)
  Next

  
  

• Kathleen Honrade

  The view of the data in Query Analyzer is the same whether it's one or two fields. I can concatenate a CHR(13) + CHR(10) immediately before the first character of the second field, and it parses the same as when it's all one field.

  If you can stand it, this is the query text:

  SELECT ' ' + CHAR(13) + CHAR(10) +
  'FROM:/SA- ' + 'CODE22' + CHAR(13) + CHAR(10) +
  ' NAME' + CHAR(13) + CHAR(10)
  + 'TO:/SA ' + [57D] + CHAR(13) + CHAR(10) +
  ' ' + Counterparty + CHAR(13) + CHAR(10) +
  'DATE: ' + RIGHT(TradeDate, 6) + CHAR(13) + CHAR(10)
  + ' ' + CHAR(13) + CHAR(10) +
  '::300' + CHAR(13) + CHAR(10) +
  ' ' + CHAR(13) + CHAR(10) +
  ':15A:' + CHAR(13) + CHAR(10) +
  ':20:' + IDTrade + CHAR(13) + CHAR(10) +
  ':22A:' + [22A] + CHAR(13) + CHAR(10) +
  ':94A: CODE 94A' + CHAR(13) + CHAR(10) +
  ':22C: CODE21' +
  CASE WHEN LEN(RIGHT(AdjustedRate, 4)) = 4 THEN RIGHT(AdjustedRate, 4)
  WHEN LEN(RIGHT(AdjustedRate, 4)) = 3 THEN '0' + RIGHT(AdjustedRate, 4)
  WHEN LEN(RIGHT(AdjustedRate, 4)) = 2 THEN '00' + RIGHT(AdjustedRate, 4)
  WHEN LEN(RIGHT(AdjustedRate, 4)) = 1 THEN '000' + RIGHT(AdjustedRate, 4)
  END +
  LEFT([87A], 4) + RIGHT([87A], 2) + CHAR(13) + CHAR(10) +
  ':82A:' + 'CODE82A' + CHAR(13) + CHAR(10) +
  ':77D: ' + CHAR(13) + CHAR(10) +
  ':15B:' + CHAR(13) + CHAR(10) +
  ':30T:' + TradeDate + CHAR(13) + CHAR(10) +
  ':30V:' + CHAR(13) + CHAR(10) +
  ':36:' + Rate + CHAR(13) + CHAR(10) +
  ':32B:' + CHAR(13) + CHAR(10) +
  CASE WHEN LEFT(Amount, 1) = '-' THEN Currency + ' ' + RIGHT(Amount, LEN(Amount) - 1)
  ELSE CounterCurrency + ' ' + RIGHT(CounterAmount, LEN(CounterAmount) - 1)
  END AS Transfer1,
  CHAR(13) + CHAR(10) + ':56D: /SA ' + 'CODE56D' + CHAR(13) + CHAR(10) +
  ' NAME BANK' + CHAR(13) + CHAR(10) +
  ' LOCATION' + CHAR(13) + CHAR(10) +
  ':57D: /SA ' + 'CODE57D' + CHAR(13) + CHAR(10) +
  ' NAME BANK' + CHAR(13) + CHAR(10) +
  ' LOCATION' + CHAR(13) + CHAR(10) +
  ':33B:' + CHAR(13) + CHAR(10) +
  CASE WHEN LEFT(Amount, 1) = '-' THEN CounterCurrency + ' ' + CounterAmount
  ELSE Currency + ' ' + Amount
  END + CHAR(13) + CHAR(10) +
  CASE WHEN LEN([56D]) > 0 THEN ':56D: /SA ' + [56D] + CHAR(13) + CHAR(10)
  END +
  ':57D: /SA ' + [57D] + CHAR(13) + CHAR(10) +
  ' ' + Counterparty + CHAR(13) + CHAR(10) +
  ':15C:' + CHAR(13) + CHAR(10) +
  ':72:' + CHAR(13) + CHAR(10) +
  '/CRX/' + CHAR(13) + CHAR(10) + ' ' + CHAR(13) + CHAR(10) + ' ' AS Transfer2
  FROM dbo.vwImport

  
  

• Niamh

  Form what you said about the vwExport you said previously it was in a single field. With this statement then it would only break on the rows as there is only one column.

  The iteration process you have looked at appears to work ok with views. Thats because the views probably contain multiple rows and columns.

  It would appear from you description that it is more to do with this specific view and the data it contains or the way it is populated than a general method of iterating through a view or datatable and processing the contents.

  .

  Previously you detailed some output from Query Analyzer

  {1:F01CODEUS31XXXX0000000000}{2:I300CODELXXXXN}{3:{113:XXXX}{108:}}{4:
  :15A:
  :20:0509295051
  :22A:NEWT
  :22C:CODE2L1350CODE31
  :83J:/NAME/FIRMNAME
  :15B:
  :30T:20050929
  :30V:20050930
  :36:0,7611350
  :32B:USD61271367,5
  :33B:AUD80500000,
  -}

  This does not look like multiple columns of data but that they are all in the same column. Is this the data from one single record or multiple records.

  
  

• Chuck Hanson

  I followed your advice with the following code:

  Dim dvExport As DataView
  Dim stExport As String
  Dim z As Int16
  Dim y As Int16

  dvExport = New DataView()
  dvExport.Table = MyDataSet.vwExport

  For z = 0 To dvExport.Table.Rows.Count - 1
  For y = 0 To dvExport.Table.Columns.Count - 1
  stExport = stExport & dvExport(z).Item(y).ToString & vbCrLf & vbCrLf
  Next
  stExport = stExport & vbCrLf & vbCrLf
  Console.WriteLine(stExport)
  Next
  
  File.WriteAllText("C:\Doc\Streaming.txt", stExport)
  

  The text file is empty, however, although vwExport has rows. I'm missing something. Can you see what I'm doing wrong

  Thanks for your help.

  
  

• abonefas22

  if the view is correct then this shouldnt cause a problem, if its iterating through the rows/columns correctly and adding to the string - you should be able to set a breakpoint and step through a simple example and see this occuring by looking at the variables in a watch window.

  If prior to the file writing - does the string with the StExportView actually contain anything.

  ie.   Is it the writing of the string or the iterating through the view/table that is not working for you.

   

   

  
  

• Dean Stewart

  Further information:

  I have verified that vwImport (a conventional view) breaks on both the rows and columns lines, but that vwExport (the view with two fields and a lot of line breaks) breaks only on the rows line.

  Something strange is going on.

  Tom

  
  
• Streaming text from a view to a file.