LinkBack URL
About LinkBacks
Hi
I've a large text file which I need to import selected information only.
Below is the sample text file which showing profile for 3 item. Any sample code or ideas would be appreciated.
Please Login or Register to view this content.
Hi
I've a large text file which I need to import selected information only.
Below is the sample text file which showing profile for 3 item. Any sample code or ideas would be appreciated.
Please Login or Register to view this content.
Last edited by Leith Ross; 09-28-2009 at 12:22 AM. Reason: Added Code tags
Hello norhaya,
Welcome to the Forum!
To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.
How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.
As a member, You have agreed to follow the forum posting rules. Please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...
Forum Rules
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank you and noted
Hi,
you can use the Open statement:
then read through each line within a loop until EOF. What part(s) of the text file are you specifically looking for?Please Login or Register to view this content.
Have you read all the rules, if so you should see that your title does not comply
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I need information as follows and data to be entered as 1 rowOriginally Posted by bigmack
Hi,
you can use the Open statement:
then read through each line within a loop until EOF. What part(s) of the text file are you specifically looking for?Please Login or Register to view this content.
Header
Item Number, Description, last shipment, last receipt, Average Unit Cost, Stocking Unit, Qty on Hand, additional Information.
Data
47, lamp, dd/mmm/yy, dd/mmm/yy, ###,000.00, EA, 50.00, K5501
I'm self-taught myself in vba, I understand loop code if i work on spreadsheet. however for this I cannot figure out how to capture data in a text file.
any ideas or example with some explanation will be helpful for me.
Last edited by norhaya; 09-28-2009 at 04:12 AM. Reason: Wrong Header
Hi Norhaya,
The header is the easy part. You don't need a macro for that! Here's a basic recipe--create the headers in sheet1. Now, you can begin importing/parsing data from a text file.
Please Login or Register to view this content.
Hi BigMack
Thank you I'll have a look when m back in office. fyi I've a large file and not possible to transfer all into excel 2003. That's why I would like to select relevant information only. I prefer to select relevant which u have given some ideas. My issues here is what the best solution to have the data for item number and description. I tried to go into an option to delete blank rows 1st and then concatenate the two lines. Another problem is, the "item number and description" will be on 1st header in the page of text file, that's why I'm a bit blank here.
I'll come back n post my code here and let you review and give me feedback
Last edited by shg; 09-30-2009 at 01:03 PM. Reason: deleted spurious quote
Hi
Below is my code, please review and comments, however, I am still not able to call up the part number and description. Any ideas?
Sub ImportText()
Dim i As Long, n As Long
Dim tmpString As String
Dim tmpAUC As String 'AUC = Average Unit Cost
Dim Fname As String
Dim tmpCAT As String 'CAT = Category
Dim tmpPICK As String ' PICK = Picking Sequence
Dim tmpUOM As String ' UOM = Unit of Measure
Dim tmpPN As String ' PN = Part Number
Dim tmpDesc As String ' Desc = Description
Dim tmpQOH As Variant ' QOH = Qty on Hand
Fname = "C:\Import\Accpac.txt"
Open Fname For Input As #1
Cells.Clear
Range("A1:I1").Value = Array("Part No", "Desc", "Date Last Shipment", "Date Last Receipt", "Average Unit Cost", "UOM", "Qty On Hand", "Location", "Remarks")
tmpPN1 = InStr(tmpString, "") & InStr(tmpString, " ") & InStr(tmpString, "category")
i = 2
n = 1
Do While Not EOF(1)
Line Input #1, tmpString
If (InStr(tmpString, "Category")) > 0 Then
tmpCAT = Right$(tmpString, 12)
Sheet1.Range("C" & i).Value = tmpCAT
ElseIf (InStr(tmpString, "Picking")) > 0 Then
tmpPICK = Right$(tmpString, 12)
Sheet1.Range("D" & i).Value = tmpPICK
ElseIf (InStr(tmpString, "Average unit cost")) > 0 Then
tmpUOM = Right$(tmpString, 16)
tmpAUC = Mid(tmpString, 44, 14)
Sheet1.Range("F" & i).Value = Trim(tmpUOM)
Sheet1.Range("E" & i).Value = tmpAUC
i = i + 1
End If
Loop
Close #1
Worksheets("Sheet1").Range("A1:I1").Columns.AutoFit
End Sub
Hi Bigmack
After going thru all the text, if I could join the line before line with "catergory" so it would be 1 string, then I could parse it easily. Can you guide/show me on the code? If this works then I can complete this task.
Appreciate your assistance.
Thanks, norhaya
Last edited by norhaya; 10-02-2009 at 12:37 PM. Reason: to add code tags
norhaya,
Please edit your prior post to add code tags.
Entia non sunt multiplicanda sine necessitate
Hi Bigmack
After going thru all the text, if I could join the line before line with "catergory" so it would be 1 string, then I could parse it easily. Can you guide/show me on the code? If this works then I can complete this task.
Appreciate your assistance.
Thanks, norhaya
CODE tags, please, not QUOTE tags, and only around code.
Re: Import text file
Norhaya, please follow the rules--simply edit your post and use <code> tags otherwise I cannot even post a hint for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks