Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
Hi
I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isnt imported correctly. Is there a parameter that can also specify column formats? I dont mind even if there is a parameter to make the wizard show also. Thanks Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
Array(Array(1, 1), Array(2, 2)) The first of the two characters for each field specifies the column (or character number) and the second character specifies the data format... 1 is general and 2 is text. So in the above example the first column has a general format and the second column has a text format. And the details are found in the "OpenText Method" in the Excel VBA help file. Also, this link has helpful advice on posting to Excel newsgroups... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Uradox" wrote in message Hi I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isnt imported correctly. Is there a parameter that can also specify column formats? I dont mind even if there is a parameter to make the wizard show also. Thanks Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
If you record a macro when you do this import, you'll have the code for each
field. Uradox wrote: Hi I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isnt imported correctly. Is there a parameter that can also specify column formats? I dont mind even if there is a parameter to make the wizard show also. Thanks Kevin -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
Hi Jim
Thank you for your reply. My question is a tricky one as its programming related but not VBA (thus I could not post it in the programming forum). I program in assembly (yes I am crazy) I parse the information on a byte level from a text file. Given the company you work for in your signature you might understand if I talk a bit more specific on the subject. The text files contains information like: " 5249 UNDE03 UNDERWATER TECH-USE UNDE11 6.00 Hire of HIAB crane model no 2 45.00 13.50 185.00 Completed 14-JUN-1999 14-JUN-1999 99737-01.T005" Its clear and easy to follow in the text file, however when its opened in excel it requires a bit of messing around with the wizard. I remove spaces where needed(020h) and add a column break (09h) after each section. This tells excel where to expect each colomn to end on each line Now if you open excel and open the text file from within, the wizard shows up. It displays all the columns correctly without any overlapping. I want to open it via CreateProcessA excel.exe etc.... It works but it opens it up in excel and totally skips the wizard. Is there byte flags I can insert that tells excel general,text etc..? Thats my prefered method to deal with it, but its acceptable if theres a parameter to make the wizard show also. I am dealing with excel 2003 but I dont think it makes a difference which version the text file is imported with, 2007 behaves the exact same way. Hope I made sence?. I didnt want to give all this information at the start because I think noone ever deals with things on the level I am here outside vba, unless that person has a very good knowledge of the file formats. "Jim Cone" wrote: Array(Array(1, 1), Array(2, 2)) The first of the two characters for each field specifies the column (or character number) and the second character specifies the data format... 1 is general and 2 is text. So in the above example the first column has a general format and the second column has a text format. And the details are found in the "OpenText Method" in the Excel VBA help file. Also, this link has helpful advice on posting to Excel newsgroups... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Uradox" wrote in message Hi I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isnt imported correctly. Is there a parameter that can also specify column formats? I dont mind even if there is a parameter to make the wizard show also. Thanks Kevin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
The Text Wizard third step has the function to select the columns and designate
what format. Text, General, Date or do not import. Record a macro while you're importing the text file to get the code. Gord Dibben MS Excel MVP On Tue, 6 Nov 2007 16:34:01 -0800, Uradox wrote: Hi I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isn’t imported correctly. Is there a parameter that can also specify column formats? I don’t mind even if there is a parameter to make the wizard show also. Thanks Kevin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text files automaticly.
You might try changing the file extension to ".txt" to see if Excel will show the wizard. Beyond that I can't be of any help. Jim Cone "Uradox" wrote in message Hi Jim Thank you for your reply. My question is a tricky one as its programming related but not VBA (thus I could not post it in the programming forum). I program in assembly (yes I am crazy) I parse the information on a byte level from a text file. Given the company you work for in your signature you might understand if I talk a bit more specific on the subject. The text files contains information like: " 5249 UNDE03 UNDERWATER TECH-USE UNDE11 6.00 Hire of HIAB crane model no 2 45.00 13.50 185.00 Completed 14-JUN-1999 14-JUN-1999 99737-01.T005" Its clear and easy to follow in the text file, however when its opened in excel it requires a bit of messing around with the wizard. I remove spaces where needed(020h) and add a column break (09h) after each section. This tells excel where to expect each colomn to end on each line Now if you open excel and open the text file from within, the wizard shows up. It displays all the columns correctly without any overlapping. I want to open it via CreateProcessA excel.exe etc.... It works but it opens it up in excel and totally skips the wizard. Is there byte flags I can insert that tells excel general,text etc..? Thats my prefered method to deal with it, but its acceptable if theres a parameter to make the wizard show also. I am dealing with excel 2003 but I dont think it makes a difference which version the text file is imported with, 2007 behaves the exact same way. Hope I made sence?. I didnt want to give all this information at the start because I think noone ever deals with things on the level I am here outside vba, unless that person has a very good knowledge of the file formats. "Jim Cone" wrote: Array(Array(1, 1), Array(2, 2)) The first of the two characters for each field specifies the column (or character number) and the second character specifies the data format... 1 is general and 2 is text. So in the above example the first column has a general format and the second column has a text format. And the details are found in the "OpenText Method" in the Excel VBA help file. Also, this link has helpful advice on posting to Excel newsgroups... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Uradox" wrote in message Hi I wrote a parser for my company that creates excel friendly text files that saves a lot of time importing them manually. The text file is formatted in such a way excel can read the correct column sizes and all data is imported perfectly. Normally if you open this text file in excel the import text wizard pops up. Now to the problem I wish my parser tool to automatically open the txt file in excel as it does via parameters. However I need to change the column data format (last option in the text wizard). By default excel imports everything as General. The last column needs to be text else the data isnt imported correctly. Is there a parameter that can also specify column formats? I dont mind even if there is a parameter to make the wizard show also. Thanks Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing text-files | Excel Discussion (Misc queries) | |||
importing two text files to same sheet | Excel Discussion (Misc queries) | |||
Importing Text Files | Excel Discussion (Misc queries) | |||
importing multiple text files??? | Excel Discussion (Misc queries) | |||
Importing text files into Excel | Excel Discussion (Misc queries) |