How to get specific data from a line and save to Excel?

I need to get Hex data from VB TextBox, convert it to Decimal and save

Page 1 of 1

3 Replies - 4447 Views - Last Post: 25 October 2007 - 08:41 AM Rate Topic: -----

#1 Wanxi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 16-October 07

How to get specific data from a line and save to Excel?

Posted 23 October 2007 - 11:36 PM

Hi everyone,


My Problem:
How to get only specific data from a whole line?

eg. I got --> "00 1F 00 5D 25 4E 3C 23..." but i only want 00 1F, the next time i will only wan 00 5D...

Anyone got this idea?


p/s:
The data "00 1F 00 5D 25 4E 3C 23.." will keep adding on (update)..
So once it update, i will only take the updated data only..

Thanks

Regards
Wanxi

Edited: The "00 1F 00 5D 25 4E 3C 23" are Hex data.. i need to convert 00 1F to decimal than save into excel..

This post has been edited by Wanxi: 23 October 2007 - 11:41 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to get specific data from a line and save to Excel?

#2 orcasquall  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 158
  • Joined: 14-September 07

Re: How to get specific data from a line and save to Excel?

Posted 24 October 2007 - 08:43 AM

Try this to get the first chunk of hexadecimals you want
Dim i As Int32 = 0
Dim s As String = "00 1F 00 5D 25 4E 3C 23"
' get rid of the spaces in between
s = s.Replace(" ", String.Empty)
If (s.Length >= 4) Then
	i = Int32.Parse(s.Substring(0, 4), Globalization.NumberStyles.HexNumber)
End If



As for saving to Excel, you'll have to post some code so we can help further.
Was This Post Helpful? 0
  • +
  • -

#3 Wanxi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 16-October 07

Re: How to get specific data from a line and save to Excel?

Posted 24 October 2007 - 06:57 PM

View Postorcasquall, on 24 Oct, 2007 - 08:43 AM, said:

As for saving to Excel, you'll have to post some code so we can help further.


Hi, Below is my code of the saving file part..
I tried to save it into ".xls" as a Excel file..
Although it success, but when i open the excel..
The data will come out with some weird things: "17:48:55:50 12337"
But if i open with text document, the "" will not be there..
What i think is even i save in excel, but the data still in text format..
How to change it to excel.. and how to get rid of the ""
And how to make excel recognize the "space" between the TIME and the DEC DATA?
So that i will save it into 2 column --> TIME and DEC DATA

Thanks
Regards
Wanxi

Dim FilePath As String = "..\MyFile"
Dim FileName As String = FilePath & "\" & Now.ToString("ddMMyyyy") & ".xls"

If My.Computer.FileSystem.DirectoryExists(FilePath) = False Then ' If there is no this file(Path: ddMMyyyy.txt)
My.Computer.FileSystem.CreateDirectory(FilePath) ' Create the file (Path: ddMMyyyy.txt)
End If

Dim sw As StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(FileName, True) ' True to make it not overwrite the previous text
Dim HexValue As String
Dim DecimalValue As Integer

HexValue = TextBox1.Text
DecimalValue = CDec(Val("&H" & HexValue)) ' &H means declare the value is in Hex
sw.Write(Now.ToString("HH:mm:ss:ff") & "   " & DecimalValue)

sw.Close()


This post has been edited by Wanxi: 24 October 2007 - 06:59 PM

Was This Post Helpful? 0
  • +
  • -

#4 orcasquall  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 158
  • Joined: 14-September 07

Re: How to get specific data from a line and save to Excel?

Posted 25 October 2007 - 08:41 AM

There are many ways of creating an Excel file.
  • you can use a COM object
  • you can write to native Excel file format (not recommended. Too tedious)
  • write to an XML file (and let Excel interpret it)
  • write to a text file, with either tabs or commas between your column data
Try this link for a COM example.
http://www.dynamicaj...B_DOT_NET-.html

The last one is the easiest. So you can try that. Use a .txt extension first.
Dim FileName As String = FilePath & "\" & Now.ToString("ddMMyyyy") & ".txt"


Then use this
sw.WriteLine(Now.ToString("HH:mm:ss:ff") & vbTab & DecimalValue)
' if you want subsequent data to be in the adjacent columns instead
' of below the current data, then use this
' sw.Write(Now.ToString("HH:mm:ss:ff") & vbTab & DecimalValue & vbTab)



Then, fire up Excel, and then open your text file as a tab-delimited file. You can't use a .xls extension and expect Excel to correctly interpret your text file without some help from you.

If you're really keen on making Excel do more interpreting, try running Excel first. Then type in some sample data. Then save as an XML file. Then open up the XML file, and study how Excel wrote it.

Then write a file with the .xls extension with the tags and it looks something like
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <LastAuthor>Vincent Tan Wai Lip</LastAuthor>
  <Created>2007-10-25T15:28:55Z</Created>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7875</WindowHeight>
  <WindowWidth>14895</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>300</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="test">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row>
	<Cell><Data ss:Type="String">21:23:41:33</Data></Cell>
	<Cell><Data ss:Type="Number">31</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
	<Header x:Margin="0.3"/>
	<Footer x:Margin="0.3"/>
	<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>


Yes, it's a lot of text for just two pieces of data. But that's the Office 2003 XML format. Some of the tags can be copied as is, like the <WindowHeight> and <WindowWidth> tags. You might want to change contents of the <LastAuthor> tag, unless you want my name propagating all over the place... :)

Anyway, note the <Cell> tags
	<Cell><Data ss:Type="String">21:23:41:33</Data></Cell>
	<Cell><Data ss:Type="Number">31</Data></Cell>


That's what you need to take care of.

Hope that gives you enough ideas to work with.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1