• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Extract from a Text File into Excel

G

guna_sekar87

Member
I will explain my requirement :


i have a TEXT file ,in which number of datas ar present as P001,P0002 ETC..


in each P001, i need to extract "DTC"+"Fault type" in a coloumn and "specific snapshot" in another coloumn.


Please find the link below:

http://www.fileconvoy.com/dfl.php?id=g3debafdee1a53a38999243049ebabe54187131925


Edit by Hui:

Please find the 2 files refereed to here

https://www.dropbox.com/s/49wmsr61ky9wo84/Guna_INPUT.txt

https://www.dropbox.com/s/dceu218via2zxtx/Guna_Required.xlsx


Note: i attached TEXT FILE WHICH IS THE INPUT AND A EXCEL FILE WHICH IS THE REQUIRED OUTPUT.

I WILL BE VERY VERY GRATEFULL IF SOMEONE MAKES MACRO OR ANY TOOL TO FULFILL MY REQUIREMENT.
 
Hi Guna,


Can you please upload your file, is some other site.. I think.. its loosing files extension. and after adding Extension.. I am not sure.. am I getting the correct file..


One more request to the community..

Please check once the Download link is working correctly, and then only submit url..


Regards!

Deb
 
Deb


You can download the files and open in Excel

It warns that the files have the wrong extensions, but opens them anyway


If you download and save, simply change the files extension from *.pdf to *.xlsx
 
dear team,


sorry for the inconvenience, i can access only fileconvey.com. or please give some other link which was not listed.


the problem is mentioned is my huge task ,,like this i have many,,if u can give soultion to this,,i can slove many prob similar to this task,,,infact i will post another different text file,in same also data from text has to extracted to a excel file
 
Hi Gunasekar ,


I think what you want done can be done using VBA ; is this acceptable to you ?


You should confirm that what you are looking for in tabular form in an Excel worksheet , is consistently formatted in the text file i.e.


1. The PVALUE is always preceded by the text string DTC ; what follows this will be put in the PVALUE column , after stripping off the character 'h'.


2. The values in the DTC column will be preceded by the text string "Description ABC type" ; whatever numeric values ( after stripping off the character 'h' ) follow this text string will be taken , till a line beginning with a non-numeric character is encountered.


3. The values in the SPECIFIC SNAPSHOT column will be preceded by the text string "Specific Snapshot" ; whatever numeric values ( after stripping off the character '$' ) follow this text string will be taken , till a line which does not begin with a '$' character is encountered.


Narayan
 
Please find the two files referred to here:

https://www.dropbox.com/s/49wmsr61ky9wo84/Guna_INPUT.txt

https://www.dropbox.com/s/dceu218via2zxtx/Guna_Required.xlsx
 
in the text file:


6.1.1 P0001 – Fuel Volume Regulator Control Actuator

DTC 0001h

Fuel Volume Regulator Control Actuator

Fault

Type

Description ABC Type

73h No Motion Detected in response to Opening the Component

72h No Motion Detected in response to Closing the Component

98h Temperature too High

Detection Conditions for Bit 4 (readiness)

73h Always

72h Always

98h Always

Detection Conditions for Bit 0 (test failed) Detection timing Resorption Timing

73h Hardware detection of short circuit to ground (leading to a blocked closed

valve)

220 ms

At the next driving

cycle

72h Hardware detection of short circuit to battery or open load (leading to a

blocked open valve)

230 ms

at the next driving

cycle

98h The hardware detects an over temperature error on the PWM output power

stage for the metering unit

220 ms at next driving cycle

Specific Snapshot

$2802 -Rail pressure setpoint

$2801 -Rail pressure

$FDEA -Setpoint current for the metering unit

$FDE9 -Current actual value of the analog input

-


________________ End of FID_Enveloppe K9K Gen5_EDC17C42_1701 600_CR3_V32.xls__0001h__.doc ______________


______________ Begin of FID_Enveloppe K9K Gen5_EDC17C42_1701 600_CR3_V32.xls__0002h__.doc ______________


44 / 16from this data , i want to dispaly the data as i mentioned in the execel for P001.
 
Hi Gunasekar ,


Can you tell me whether this is OK ?


http://www.fileconvoy.com/dfl.php?id=g30f3c9f1bc13a4579992436348a45fca912d2ae71


Narayan
 
Dear Narayaan,


First thank u very much for the very good job,,,,u soolved many of my time consumption,,,,,as request there are few datas which are missing i mentioned them in red colour. please can u correct them.and as a humble request if u can give me your email id ..it will be very usefull for me...


thanks once again


http://www.fileconvoy.com/dfl.php?id=g991b998f7857db07999243657ee5bb512ae6e3c35
 
http://i49.tinypic.com/2vkcf2x.png


please tell where put the input test file ,,as i am new to use ms ecxel 2010.please guide me to use this valuable macro ,,i have attached the picture wheni opened the macro...
 
Hi Gunasekar ,


The problem is that the values which start with a non-numeric character e.g. F5 , F6 , F2 , have been omitted. Let me see how I can get around this problem. Give me some time.


Narayan
 
Hi Gunasekar ,


Download this file :


http://www.fileconvoy.com/dfl.php?id=g8d370024af11ce74999243786d6bb733f04bc2d80


There are two requirements you have to fulfill before you can run the macro :


1. Ensure that the text file , which is named INPUT TEXT.txt is in the same directory as your workbook REQUIRED_DATA_LIST_R1.xlsm


2. The worksheet named Feuil2 , which will contain the copied data exists ; also columns A through C should be formatted as text. These columns should not be used for any other data , since that data will be overwritten by data from the text file , as a result of running the macro.


3. To run the macro , do whatever you have been doing ; click on Macros , select the macro ThisWorkbook.Create_Table_From_Text and click on Run.


Narayan
 
thank you very much NARAYANK991,,,,thanks a lot....

i got the exact result which i expected....

u saved my effort in typing all the list from that input text data.


similar to this problem i have posted "DATA inside a text into a excel"


if u can send me ur mail id to my mail guna_sekar87@yahoo.co.in.


thanks once again
 
You must log in or register to reply here.
Back
Top