Importing text files automaticly.

[Uradox]

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

 

[Jim Cone]

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 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

 

[Dave Peterson]

If you record a macro when you do this import, you'll have the code for each
field.

 

[Uradox]

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.

 

[Gord Dibben]

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

 

[Jim Cone]

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.