Transferring Data from a tab delimited .txt file to an excel file

Need to be able to format information before sending to excel file

Page 1 of 1

6 Replies - 3254 Views - Last Post: 13 December 2010 - 03:08 PM Rate Topic: -----

#1 Guest_Katlyn*


Reputation:

Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 08:07 AM

Hello,

I am trying to get information from a tab delimited .txt file to an excel spreadsheet, but I need to do some modifications in between. I also need to add some more information to the excel spreadsheet that is not in the .txt file. I need to do this all inside the VB code (without opening the Excel program in the screen).

I am not exactly sure how to go about doing this. Here is an example of the layout of the .txt file.

0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0

(There are actually 17 columns and 13 rows in the actual file. The values are not always zero but they are always an integer number value)

I need to get the values from the text file into an excel file that looks similar to this:
Title
Printed: Date Time Bin # X

Memo: (text here)
Grade: (text here)
Width: (text here)
Length: (text here)
Thickness: (text here)

3" 4" 5" 6" 7" 8" 9" 10" ... 18"
4'
5'
6'
7'
.
.
.
16'

The information from the .txt file will be inserted above as the values to complete the chart. (Example, there could be a number 7 in 3" and 4', etc.)

Hopefully this makes sense. I'm not sure how to go about doing this. I haven't worked much in VB6. I would really appreciate any suggestions.

Thanks.

Katlyn

Is This A Good Question/Topic? 0

Replies To: Transferring Data from a tab delimited .txt file to an excel file

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 11:47 AM

So after this:

Quote

Memo: (text here)
Grade: (text here)
Width: (text here)
Length: (text here)
Thickness: (text here)


come this:

Quote

0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0


so you know how to start a vb application right? if no check our tutorial section and try google tutorials on the theme. for the question about the reading file and delimeter you can use reading file byte at a time and check if the byte dont contain the ascii code for empty space(if that is your delimeter) and if not write it to excel. writing to excel is pretty easy since the file is separated on rows and cells for example "A1" etc. if you know on what row you may begin the writing you can just go from there and put it to a place while reading the file at the same time.
Was This Post Helpful? 0
  • +
  • -

#3 Guest_Katlyn*


Reputation:

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 12:02 PM

View PostKatlyn, on 13 December 2010 - 07:07 AM, said:

Hello,

I am trying to get information from a tab delimited .txt file to an excel spreadsheet, but I need to do some modifications in between. I also need to add some more information to the excel spreadsheet that is not in the .txt file. I need to do this all inside the VB code (without opening the Excel program in the screen).

I am not exactly sure how to go about doing this. Here is an example of the layout of the .txt file.

0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0

(There are actually 17 columns and 13 rows in the actual file. The values are not always zero but they are always an integer number value)

I need to get the values from the text file into an excel file that looks similar to this:
Title
Printed: Date Time Bin # X

Memo: (text here)
Grade: (text here)
Width: (text here)
Length: (text here)
Thickness: (text here)

3" 4" 5" 6" 7" 8" 9" 10" ... 18"
4'
5'
6'
7'
.
.
.
16'

The information from the .txt file will be inserted above as the values to complete the chart. (Example, there could be a number 7 in 3" and 4', etc.)

Hopefully this makes sense. I'm not sure how to go about doing this. I haven't worked much in VB6. I would really appreciate any suggestions.

Thanks.

Katlyn

Was This Post Helpful? 0

#4 Guest_Katlyn*


Reputation:

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 12:58 PM

View PostNoBrain, on 13 December 2010 - 10:47 AM, said:

So after this:

Quote

Memo: (text here)
Grade: (text here)
Width: (text here)
Length: (text here)
Thickness: (text here)


come this:

Quote

0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0


so you know how to start a vb application right? if no check our tutorial section and try google tutorials on the theme. for the question about the reading file and delimeter you can use reading file byte at a time and check if the byte dont contain the ascii code for empty space(if that is your delimeter) and if not write it to excel. writing to excel is pretty easy since the file is separated on rows and cells for example "A1" etc. if you know on what row you may begin the writing you can just go from there and put it to a place while reading the file at the same time.




Would you have any examples of how to do this, or links to sites where I can get more information. I am trying to Google it but I am finding a lot of code that refers to VB.NET and I need VB6. It would really be appreciated.

Thanks.
Katlyn
Was This Post Helpful? 0

#5 Guest_Katlyn*


Reputation:

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 01:00 PM

And also, the zeros (or integers) in the .txt file are actually separated with a tab not just a space (I'm not sure why it showed up like that on here).
Was This Post Helpful? 0

#6 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 01:25 PM

yea it dose not matter if its space tab or | you just need to track the ascii code
FILE HANDLING USING VISUAL BASIC 6.0 PART II - BINARY FILE HANDLING
Reading Data From Binary
Using Excel with Visual Basic 6!

try to find a solution and if you have problems feel free to post here and we will try to help you :)
Was This Post Helpful? 0
  • +
  • -

#7 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 805
  • Joined: 16-September 09

Re: Transferring Data from a tab delimited .txt file to an excel file

Posted 13 December 2010 - 03:08 PM

One way that I learned allot about vb in the beginning was to record Excel Macros then pick them apart. So in this case try the following

Start recording a macro
Select the Import Data from the data menu in excel
Select the right options(tab delimited)
Then once you have an idea of how to import the data correctly, modify a bit and add a filedialog to select the files(if you want to get fancy) and go from there. If you run in to code problems, come on back and post the code so we can help but I think this was a really good starting point for me, personally.


Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1