Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
I have been asked to do some research on having excel read a text file and
from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
Hi,
do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
A vba macro would work just fine. I'm pretty limited in knowledge about vba
codes -- Ron P "MadZebra" wrote: Hi, do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
Just a bit more info...
1. Is the file comma delimited? 2. Are there multiple records in the file which you would like shown as multiple rows in the spreadsheet, or just a single record to be shown in a single row? "Ron P" wrote: A vba macro would work just fine. I'm pretty limited in knowledge about vba codes -- Ron P "MadZebra" wrote: Hi, do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
The file is tab delimited. There could potentially be anywhere from 100 to
1000 records in the file set up as the example. Each occurance where the condition is met should result in a new row in the excel sheet thus you are correct taht there could possibly be multiple rows in the spreadsheet. -- Ron P "MadZebra" wrote: Just a bit more info... 1. Is the file comma delimited? 2. Are there multiple records in the file which you would like shown as multiple rows in the spreadsheet, or just a single record to be shown in a single row? "Ron P" wrote: A vba macro would work just fine. I'm pretty limited in knowledge about vba codes -- Ron P "MadZebra" wrote: Hi, do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
Ok, here is the macro:
============ Sub GetFileData() ' On Error GoTo eh_GetFileData ' Dim filePath As String Dim startRow As Integer Dim startColumn As Integer ' filePath = "D:\data.txt" startRow = 5 startColumn = 5 ' Dim fso As Object Set fso = CreateObject("Scripting.FilesystemObject") ' Dim textFile As Object Set textFile = fso.OpenTextFile(filePath) ' Dim fileContents() As String fileContents = Split(textFile.ReadAll, vbCrLf) textFile.Close ' Dim recordCount As Integer Dim recordPointer As Integer recordCount = 0 For recordPointer = 0 To UBound(fileContents) Step 5 recordCount = recordCount + 1 Cells(5 + recordCount, 4) = Split(fileContents(recordPointer), vbTab)(0) Cells(5 + recordCount, 5) = Split(fileContents(recordPointer), vbTab)(2) Cells(5 + recordCount, 6) = fileContents(recordPointer + 1) Next CleanUp: Set textFile = Nothing Set fso = Nothing Exit Sub ' eh_GetFileData: GoTo CleanUp ' End Sub =============== You will need to paste it into the code for your worksheet or workbook, then add a button to your sheet and assign the macro name "GetFileData". You should be able to see which bits to change for your correct file path and sheet start position (near the beginning). Don't forget to enable macros or of course it won't work, in fact you won't even see the macro name when you try to assign it. Let me know how it goes :) .... "Ron P" wrote: The file is tab delimited. There could potentially be anywhere from 100 to 1000 records in the file set up as the example. Each occurance where the condition is met should result in a new row in the excel sheet thus you are correct taht there could possibly be multiple rows in the spreadsheet. -- Ron P "MadZebra" wrote: Just a bit more info... 1. Is the file comma delimited? 2. Are there multiple records in the file which you would like shown as multiple rows in the spreadsheet, or just a single record to be shown in a single row? "Ron P" wrote: A vba macro would work just fine. I'm pretty limited in knowledge about vba codes -- Ron P "MadZebra" wrote: Hi, do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading an external text file
....I just noticed a minor bug :$
Changing the startRow and startColumn values won't make any difference. To fix this replace these lines: Cells(5 + recordCount, 4) = Split(fileContents(recordPointer), vbTab)(0) Cells(5 + recordCount, 5) = Split(fileContents(recordPointer), vbTab)(2) Cells(5 + recordCount, 6) = fileContents(recordPointer + 1) with these lines: Cells(startRow + recordCount, startColumn) = Split(fileContents(recordPointer), vbTab)(0) Cells(startRow + recordCount, startColumn + 1) = Split(fileContents(recordPointer), vbTab)(2) Cells(startRow + recordCount, startColum + 2) = fileContents(recordPointer + 1) "MadZebra" wrote: Ok, here is the macro: ============ Sub GetFileData() ' On Error GoTo eh_GetFileData ' Dim filePath As String Dim startRow As Integer Dim startColumn As Integer ' filePath = "D:\data.txt" startRow = 5 startColumn = 5 ' Dim fso As Object Set fso = CreateObject("Scripting.FilesystemObject") ' Dim textFile As Object Set textFile = fso.OpenTextFile(filePath) ' Dim fileContents() As String fileContents = Split(textFile.ReadAll, vbCrLf) textFile.Close ' Dim recordCount As Integer Dim recordPointer As Integer recordCount = 0 For recordPointer = 0 To UBound(fileContents) Step 5 recordCount = recordCount + 1 Cells(5 + recordCount, 4) = Split(fileContents(recordPointer), vbTab)(0) Cells(5 + recordCount, 5) = Split(fileContents(recordPointer), vbTab)(2) Cells(5 + recordCount, 6) = fileContents(recordPointer + 1) Next CleanUp: Set textFile = Nothing Set fso = Nothing Exit Sub ' eh_GetFileData: GoTo CleanUp ' End Sub =============== You will need to paste it into the code for your worksheet or workbook, then add a button to your sheet and assign the macro name "GetFileData". You should be able to see which bits to change for your correct file path and sheet start position (near the beginning). Don't forget to enable macros or of course it won't work, in fact you won't even see the macro name when you try to assign it. Let me know how it goes :) ... "Ron P" wrote: The file is tab delimited. There could potentially be anywhere from 100 to 1000 records in the file set up as the example. Each occurance where the condition is met should result in a new row in the excel sheet thus you are correct taht there could possibly be multiple rows in the spreadsheet. -- Ron P "MadZebra" wrote: Just a bit more info... 1. Is the file comma delimited? 2. Are there multiple records in the file which you would like shown as multiple rows in the spreadsheet, or just a single record to be shown in a single row? "Ron P" wrote: A vba macro would work just fine. I'm pretty limited in knowledge about vba codes -- Ron P "MadZebra" wrote: Hi, do you need an add-in to do this or are you happy to do it using a VBA macro? the macro code should be fairly straightforward to do. "Ron P" wrote: I have been asked to do some research on having excel read a text file and from that text file extract pieces of data based on a condition and then populate an excel sheet for anaylsis. The programmers have a text file created as in the example and it is set up is such a manner as the required information is in two seperate lines while the condition is in another. example date name number seller amount item item number sale price value The items being requested a From line 1 -- "date", "seller" From line 2 -- "amount" based on the "sale price" which is on line 5 of the text file Does any one have any ideas or point me in the general direction on how I can populate excel so that sales managers can evaluate the informtion in excel? The managers would like to see the excel sheet look like this Column A Column B Column C Date Seller Amount Thanks and as always any assistance is appreciated. -- Ron P |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading from a text file | Excel Discussion (Misc queries) | |||
reading from text file to excel file | Excel Programming | |||
Reading a text file ? | Excel Worksheet Functions | |||
Reading a text file | Excel Discussion (Misc queries) | |||
Help reading in a text file | Excel Programming |