Query on a text file..help needed

I am trying to query a large text file and I'm beginning to wonder if
it is just impossible to do.

I used the excel query wizard but because I am working with a fixed
length file I am not coming up with the columns I need.  The wizard
picks up each row as a solid block of information.  Is there a way to
pick out certain information from the text file into the query?

so if my text line reads:

0110172006johndoe234street

excel could pick out
01 10/17/2006 John Doe 234 street

I know someone ran a query on my file and put it into the most
beautiful excel spread sheet but it just isn't working for me.

0
10/17/2006 5:20:54 PM
excel 39879 articles. 2 followers. Follow

1 Replies
436 Views

Similar Articles

[PageSpeed] 23

If all of your data are in that same format down comumn A, then you could use 
Data > TextToColumns to break some of it apart, and then use some of the TEXT 
formulas like LEFT, RIGHT, MID, FIND, etc to get the rest of it.....

Vaya con Dios,
Chuck, CABGx3



"MaryLindholm@gmail.com" wrote:

> I am trying to query a large text file and I'm beginning to wonder if
> it is just impossible to do.
> 
> I used the excel query wizard but because I am working with a fixed
> length file I am not coming up with the columns I need.  The wizard
> picks up each row as a solid block of information.  Is there a way to
> pick out certain information from the text file into the query?
> 
> so if my text line reads:
> 
> 0110172006johndoe234street
> 
> excel could pick out
> 01 10/17/2006 John Doe 234 street
> 
> I know someone ran a query on my file and put it into the most
> beautiful excel spread sheet but it just isn't working for me.
> 
> 
0
CLR (807)
10/17/2006 5:41:01 PM
Reply:

Similar Artilces:

Hyperlink help #2
I have an array formula that creates a hyperlink:- =IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24& $B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D $10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory! a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D $2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1)) this works and creates the hyperlink:- [Inventory.xls]Inventory!a1 this works fine, but when the workbook inventory.xls is closed, nothing at all works when i clickon th...

Timer for Queries?
Does anyone know of a macro or add-on that I could use as a simple timer for queries? I don't like to use the CPU time in the Task Manager because of it's inaccuracy when not receiving full CPU usage. THanks for any suggestions -- cmungs Exactly what are you trying to accomplish? Are you trying to cause a query to run automatically every so often? If so, you will need to use the timer event on a form to do that. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "cmungs" <cmungs@discussions.microsoft.com> wrote in message news:88EC7019-045F-4EF...

Excel 2003 Function Help
I need help coming up will a function that will average a range in one column based on if the hours match. Here is my set up. -------A-------B 1.....09:21....12 2.....09:46....24 3.....09:57....17 4.....10:04....22 I need to average column B if column A's hour is the same as the hour in a cell in another worksheet. Ryan Hicks wrote: > I need help coming up will a function that will average a range in one column > based on if the hours match. Here is my set up. > > -------A-------B > 1.....09:21....12 > 2.....09:46....24 > 3.....09:57....17 &...

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

Number and text format in the same call
I'm trying to change the format of the following" 12345678 to 123-45-678 -- in other words, adding the dashes. Some of the numbers are the same 8 digits, but also have a letter. They are currently: 12345678A to 123-45-678A Thank you, In a helper column put this formula and copy down.....then do Copy > PasteSpecial > Values on that helper column to eliminate the formulas and delete the original column if desired. =LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&MID(A1,6,99) Vaya con Dios, Chuck, CABGx3 "Jim Davis" <Jim Davis@discussions.m...

Update Query ?
Is there a way to 1) Assign events when adding in data thru an update query for example if you append a bunch of data into a table could you then do some kind of an update query to say if there is an initial event and no others tag this one Renewal 1 etc.. the only real data you have to go on would be Customer A and the total amt .. the event, renewal event and the delta would all need to be added in example: Say Customer A comes in with an initial deal then Customer A renews their deal So, the data would look like when you're done.: Year Custo...

design query match anywhere?
In the design query I want to match the letters "ABC" anywhere in the column 'description'. I could not get instr to work by using: InStr(description, "ABC")) > 0 How else can I match ABC (upper and lowercase) to anywhere in my 'description' column? Thanks! Are you saying you put that InStr bit as a criteria under your Description field in the query designer? Try putting Like "*ABC*" as the criteria instead. Alternatively, you could add a computed field to the query InStr([Description, "ABC") and then put >0 as the ...

Unexpected Error- Running Advance Find Query
I am getting a message that says "Unexpected Error An Error has occurred" when I am running an advanced find query on two custom entities. It seems to be when I have certain custom fields included in my view that the query errors out. Any help on this would be greatly appreciated. Thanks so much. Just wanted to add that I seem to get errors when there is a 0.00 amount in these fields... "TJ" wrote: > I am getting a message that says "Unexpected Error An Error has occurred" > when I am running an advanced find query on two custom entities. It se...

Help
I am using Exchange 5.5 SP4 + IIS 4.0. OWA often stops response after installing the Mailbox Manager, says - error 'ASP 0115' Unexpected error /exchange/CHS/logon.asp A trappable error occurred in an external object. The script cannot continue running. When this happens, I can't stop and restart WWW publishing service with Control Panel, the only way is to reboot the whole NT system. Have stopped the mailbox manager service and reapplied SP4, but just useless. Pls help! Thanks! I've never heard of that happening. You might try reinstalling Exchange to reinstall OWA. ...

pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...

blind copies in sent mail file
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) How do I look at, or better yet, list, the blind copies on my sent e-mail? On 2/10/10 8:06 PM, in article 59bb2a27.-1@webcrossing.JaKIaxP2ac0, "Lynn_Drayton@officeformac.com" <Lynn_Drayton@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) How do I look at, or > better yet, list, the blind copies on my sent e-mail? If you open the message into its own window by double-clicking it in the message pane, you'll see the Bcc header line. If it is a group name, Control-cli...

Cannot open file
Hello, I am in need of assistance. A file was created in Publisher 2002 SP-1 (Windows XP Pro). The file was saved as Publisher 2000. We have tried to open the file in Publisher 2000 SR-1 (Windows 98). A message pops up "Publisher cannot open files from a different Version." How can we open the file in Publisher 2000?? Any suggestions would be greatly appreciated!! Thanks Do you have that garbage trash Symantec Norton installed. If you do, REMOVE permanently the garbage trash junk software and your problems will be over. -- We are not running Norton. Any other suggestion...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

In Publisher, how do I get background text to print gray?
I'm trying to print a pamphlet on a black and white HP1200Laserjet printer. I colored the background text gray and the foreground text black, so it overprints. Both texts print black, even though the line I placed below it prints gray. I've tried changing all the commercial printer settings, using different colors and it always comes out black. When I print it on the color inkjet printer, it comes out gray. How can I get it to print gray on the laser printer? ...

Converting text to numbers
I'm running Excel 2000 ... After opening a text file with the Text Import Wizard, I hav successfully segregated a column of numbers into one column. Only on problem, the NEGATIVE SIGN (for negative numbers in the text file) i positioned in front of the number, rather than at the end. When th file import is complete, the negative numbers show up as text, and ar not included in the column totals. I believe that this is controlled by the Region Settings in the Contro Panel ... and if I changed the "Negative Number Format" option fro "1-" to "-1" ... then I woul...

Saving Username and Pass with Web Queries
I am pulling data from a website that requires you to provide a usernam and password everytime you visit the site. Excel has cachin capabilities, but everytime you close excel and try and open up th spreadsheet again, it can't login. What is the fix for this? Thanks, Hend -- Message posted from http://www.ExcelForum.com Bump! --- Message posted from http://www.ExcelForum.com/ ...

Copy to a new file to rid file bloat?
Has anyone else had this happen? I copied my masters and slides over to a new blank presentation and it greatly reduced the file size. I shaved off 4 megs off of a 9 meg file, almost cutting its size in half by doing this. Is there a known reason for this change? -Melina Hi Melina Could you have "Fast Save" enabled in options? (Only likely if you are using a pre 2007 version) -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "Mel" wrote: > Has anyone ...

Birthday query for many children in one record
I have a database for the church, it has one main flat table only. Each record for the parents have field for child1 name, child1birthday, child2 name, child2birthday, child3 name, child3birthday - up to 5 children. I am having difficulty trying to get a report to list the birthdays of the children like this parent name, child 1 name, child 1 birthday month etc. so you get something like this:- Parent John Smith, child Mary Smith January 3 Parent Fred Jones, child Bert Jones March 4 Parent John Smith, child Jane Smith March 6 Parent Harry Brown, child Jane Brown March 6 Parent Bert Taylor,...

Why am I getting virus protection error when load excel file
I just upgraded to excel 2003. When I try to old or new file I've create, I get virus file detection error Hi! Have you recently run these files through a virus checker? Just thought... Al -- Message posted from http://www.ExcelForum.com Try using Tools/Options/General and uncheck Macro Virus Protection which detects any macro in a workbook - even if it is yours -- Message posted from http://www.ExcelForum.com Gary, This is ambiguous. If you get an Excel warning to the effect of "This workbook contains macros. Macros can be bad...," this is normal if the workbook...

Comparing text strings in cells
A cell contains a number of text strings which form sentences. Text from another source is pasted into another cell via the edit bar, it may be exactly the same or not. The question is, is it possible for excel to highlight where any differences occur between to contents of the cells - the pasted in text would be highlighted perhaps? -- Thank you very much for your advice Best Wishes Andy ...

Unresponsive Query Wizard
Hi there, I have an Excel 2003 user who is using a spreadsheet that pulls data from our SQL server. When he opens it and refreshes the data it is fine, it pulls the current data, but when he tries to edit the query, the Query Wizard does not appear, he can see it on the task bar, but when he switches focus using task manager everything is greyed out and nothing works. I have tested the file from my computer and it is fine, which suggests a problem with his machine or installation. Does anyone have any ideas? Cheers, Steve Dooza wrote: > Hi there, > I have an Excel 2003 user who...

Word 2007 document files not opening/saving
My Word 2007 opens normally with a new document screen. As soon as I try to open a file (either through file open or filefolder) Word crashes. If I click on a recent document it will open but as soon as I try to save or save as, Word crashes. This is only happening in Word 2007 and none of the other Office Enterprise programs. There are no error messages when this occurs. I've uninstalled and reinstalled Office Enterprise 2007, along with all the updates and security patches, etc. but the same error occurs. I've checked my printers and they are all okay and connecte...

Purchase Orders copying (URGENT HELP PLEASE)
Can the purchase orders from one database be copied to the new live database. One of my customers had a database with several purchase order and now he has created a new database but he want his purchase orders to come over tot he new database. ...

Static Text
Can a static text control be modified if it is hooked up to a member variable thanks for answers to these newbie questions. yes. 'static' doesn't mean it can't change. "jason wolfe" <anonymous@discussions.microsoft.com> wrote in message news:FF4F278D-A78C-41BD-96C6-92859D4026C3@microsoft.com... > Can a static text control be modified if it is hooked up to a member variable? > > thanks for answers to these newbie questions. On Thu, 19 Feb 2004 05:11:10 -0800, jason wolfe wrote: >Can a static text control be modified if it is hooked up to a mem...

Another simple query
Dear all, I have a table that contains first names and last names in different fields. Is there any way to write a query that will give as a result the first name and last name in the same field? I hope this makes sense. Thanks in advance! SELECT [last_name_field] & ", " & [first_name_field] AS whole_name FROM [your_name_table] Change the field and table names to whatever your actual fields and tables are. Cheers, Jason Lepack On Apr 23, 9:47 am, Homer <peter.a.r...@googlemail.com> wrote: > Dear all, > > I have a table that contains first names an...