Text To Excel

[rocky2011]

hi Everyone

I basically have a large text file and I want to convert into Excel exactly as I have attached both text & excel files here.
Can anyone help me how can I convert/import this 'text' file into Excel
P.S.: Please note that text file data is too large this is just sample so I can't do 'tab' settings.
any other shorter way if anyone know would be highly appriciated

Thanks
Rocky

[pike]

Hi Rocky
can you check the text file as the data has different layouts
Is the data grouped over four or five lines?

[pike]

Hi Rocky
with out knowing the possible data layouts something like this

Please Login or Register  to view this content.

[rocky2011]

Thnks very much Pike for your reply & the suggestion.
And yes you are right. text file data are grouped over four or five lines.
In text file you can see that data is starting with "MD-DD-XXXXXX" & ending with "1 TO 2 YEARS," and total no. of data for one entry/raw is 26. i.e. in excel the data should be imported from A1 to Z1, A2 to Z2, A3 to Z3............and so on.
I hope you can understand & for your complete knowledge I am attaching herewith a sample of Text file that contains 147 Data. In text file you will see as I have written above the
Entry no. 1 starts with MD-DD-030000, XC-DD-25001,SN1OOOO25001,.................1 TO 2 YEARS (Total 26 columns)
Entry no. 2 starts with MD-DD-030001, XC-DD-25002,SN1OOOO25002,.................1 TO 2 YEARS (Total 26 columns)
........................................................................
.......................................................................
Entry no. 147 starts with MD-DD-030146, XC-DD-25147, SN1OOOO25147,.................1 TO 2 YEARS (Total 26 columns)

Now I want to convert this 147 Entries into Excel & for your ready reference I am attaching sample of excel file(1st 3 entries) as what exact data I want to be imported all 147 entries into excel, I have also given the Titles to all 26 columns maybe that can help out in importing.

I am awaiting your reply.

Thnks & Rgds
Rocky

[pike]

Hi rocky
This will just add the text file to the worksheet per 4 or five grouping but there are to many variables to work out what goes where
How do you create the text file? If you could add the delimiters to the text file it would be easier

Please Login or Register  to view this content.

[rocky2011]

Thanks very much Pike for your reply.
I have tried your last code but sorry it didn't worked!
Well the original file is PDF and I have converted into text. The data of text file is too large so adding delimiters or applying tab will take too long time!
But one common thing is that there are exactly 26 columns in each entry( you can see the excel file i have attached earlier)

Thanks
Rocky

[pike]

Hi Rocky
conversion from PDF is a problem as there is no real pattern to follow; Its near impossible to allow for all the variations in the layout.
The PDF is created from a word document?

[rocky2011]

Well Pike I have some softwares that can easily turn any pdf into word exactly the data as it is. and not only pdf , like jpg files can also be converted to word easily.
And i really don't know that pdf is created from word, cuz I hv rcvd. pdf.
Tell me if you want to see the pdf, i will attach it.
thnks
Rocky

[pike]

Hi Rocky
veiwing the pdf won't help.. it just the way the data is layed-out.. to many variables to capture them all fro the table..
Can you control how the data is inputed?

[rocky2011]

Pike , well I really don't know how the data is inputed into pdf, but I think maybe through some software or programme.
Rgds
Rocky

[pike]

Hi rocky2011
change the file path name to the txt files and this will take out another variable

Please Login or Register  to view this content.

[rocky2011]

Hi Pike,
Sorry but last code didn't work at all. I rcvd. a blank file in excel.
And I am applying correct file path and that is c:\147.txt
For your ready reference I am enclosing both 147 txt & 147 excel files
so that you can workout more on it.

thnks very much
Rocky

[pike]

Hi rocky2011,
147.txt has another different layout with added line breaks
since the txt file was made from a PDF I'm afraid it is not practical to convert it to the table you require

[rocky2011]

Hi Pike,
I have attached a new text of 147 entries in a different format. Where you will see that each entry is seperated by a comma, can you please take a look on file and give me your feedback?
Thnks very much
Rocky

[foxguy]

Hi Rocky;

I don't work with text files, but since you asked me to look at it I'll try.
This code DEPENDS on a "," being at the end of each line and a SPACE, or CR or LF to separate the text for each column.

I tried it on "new 147.txt" and it worked fine, but I don't see any reason why it would not work on any of the formats you have uploaded.

Please Login or Register  to view this content.

[rocky2011]

Thnx very much Pike, its worked correctly. For larger data also this will work although I have to make some changes in text file. Thanks again for your help.
Rocky