I'm having trouble importing a .txt file, then parsing the data to columns. I've attached a sample of the raw data and the .XLSX layout I'd like to generate. The issue with the text is that each one item has six subsequent lines. I hope you can help!
Last edited by Mordred; 12-16-2011 at 01:34 PM.
Bumping.
I think I can live with the title being split oddly, matching the lengths of the subsequent six rows. Column A of these six can be text (name of a month or ...) or numerical (quantity ordered or cost). I couldn't figure out the how to use if-then- else to parse in both situations. If it can't be don't, will someone please say so?
There should be a way to get this done. Someone from the forum will surely help you soon.
Hi Hoosaskin
In what versions of Excel does this need to run? Excel 2007 and above?
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi Hoosaskin
The code in the attached appears to so as you require EXCEPT that I could not align Columns D through H properly and I don't know how to correct the issue.
The code will prompt the user to select the File Name they wish to import. Let me know how I can help.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Thanks for all your work on this John! I have run into a problem when I run this with my full file. Some of the "Descriptions" are being carried into cells farther along the row, sometimes as far as Column H. I've been looking at your code and can't figure out how to include those last few characters for column A. This spreadsheet shows some of the errors I'm getting. I've included a new Sample data.txt file with these problem children. And here I thought the spacing would be the easy part to figure!
Thanks for lokking at this again.
Sean
Hi Sean
I've looked at the file and have some ideas. It'll take a bit. Get back to you.
On your initial sample file I had an issue with aligning columns. What kind of heartburn did this cause (on the initial sample file)The code in the attached appears to so as you require EXCEPT that I could not align Columns D through H properly
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
I don't think the reorder points are as critical a piece of information, hence it's not a populated field for each line item. We're more concerned with the values aligned with the proper months. Excel can't work its wonders if there's not a usable format.
Hi Sean
I don't know...you'll need to tell me. Try the attached...let me know of issues.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
It works great, John! But... now with my full data file there are some records that pull too much to the left. There's even one entry the doesn't import at all (starts on line 272). Here's most of the full TXT file (it's too big to upload the whole thing), so you can see what I'm getting now. I really appreciate your help with this!
Sean
Hi Sean
I'll look at this tomorrow...too late tonight.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi Sean
The record you're referring to that's not being processedSLEEVES-CD-XRAY 2054522000/CS 400 80 is formatted differently. There's no break between the M# and the UNIT/IS. Not much I can do with this. You may wish to ask your IT Dept why there's no break point.one entry the doesn't import at all (starts on line 272)
The rest I'll look into tomorrow.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
I'm guessing it's a rare instance when the MI# is 6 characters and Unit/issue is 4 characters/issue type. We should be able to deal with them when they occur.
Hi Sean...I'll get back into this tomorrow...had outside work to do today.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi Sean
I've not given up yet...the cells that don't process correctlyhave more data than the other samples. I SEE what's happening...need to figure a way of dealing with it.records that pull too much to the left
I'll get back to you.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks