Importing Text File into Excel

[Naraine Ramkirath]

Hi All,

On a monthly basis I import a text file into excel and "massage" the data to
fit into the columns. However, this is a long and tedious process. I am
wondering if some one could provide a vb script that will do this for me.

Any help would be greatly appreciated.

Regards,

Naraine

 

[FSt1]

hi,
have you tried data>text to columns? Its a built in wizard.

Regards
FSt1

 

[Naraine Ramkirath]

yes, this is what I'm actually using. I'm still faced with the huge problem
of massaging the data to get the desired result.

 

[Tim]

"Massaging" data means what exactly ?
Automating a process begins with understandidng what it is...

Tim

 

[IanKR]

On a monthly basis I import a text file into excel and "massage" the

data to fit into the columns. However, this is a long and tedious
process. I am wondering if some one could provide a vb script that
will do this for me.

If I understand you correctly, then this is something I do frequently as
well. One solution would be to open a blank workbook, turn on the macro
recorder (have the macro recorded in the current blank workbook) and go
through what you do when you import the text file. Then edit the macro if
need be, if it needs generalising.

The code that opens the text file will look something like this:

Workbooks.OpenText Filename:="<Your txt filename>", dataType:=xlDelimited,
tab:=True ...

Short of you explaining what exactly you do with the data when you massage
it, I can't help you any further.

 

[Naraine Ramkirath]

Here is a sample of data I'm working with. I need to split each category
into a separate column. Note:

the Vendor Number and Name is in the row above the Vendor Total. I'm trying
to split vendor total into the respective columns: Document;Transaction
type;Transaction Date;Amount;Past due;Current;31-60 Days;61-90 Days;91+
Days.

I also have to deal with blank rows & report titles that repeats at the
beginning of each page.

Any help would be greatly appreciated.


Transaction Transaction
Document Type Date Amount Past Due Current
31-60 Days 61- 90 Days 91+ Days
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : ALLIED ALLIED FULFILLMENT SERVICES


Vendor Total : -1000.00 -1000.00 0.00
0.00 0.00 0.00
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : ALTSHULER LOU ALTSHULER & ASSOC.


Vendor Total : 14720.68 0.00 0.00
14720.68 0.00 0.00
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : BAUMAN RON BAUMAN & ASSOCIATES


Vendor Total : 0.00 0.00 0.00
0.00 0.00 0.00
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : BELK BELK INC.


Vendor Total : 1737.80 1737.80 0.00
0.00 0.00 0.00
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : BELLVILLE BELLVILLE RODAIR INTERNATIONAL


Vendor Total : 117966.37 15114.55 55494.99
47356.83 0.00 0.00
--------------------------------------------------------------------------
----------------------------------------------------------

Vendor : BL ROGERS B.L. ROGERS ASSOCIATES, INC.


Vendor Total : 206.18 32.36 0.00
173.82 0.00 0.00

 

[IanKR]

Here is a sample of data I'm working with. I need to split each

category into a separate column. Note:

the Vendor Number and Name is in the row above the Vendor Total. I'm
trying to split vendor total into the respective columns:
Document;Transaction type;Transaction Date;Amount;Past
due;Current;31-60 Days;61-90 Days;91+ Days.

I also have to deal with blank rows & report titles that repeats at
the beginning of each page.

Any help would be greatly appreciated.

[snip]

Please see the e-mail I just sent you.

Ian

 

[Naraine Ramkirath]

joel,

i removed the spaces as you suggested. I still get that error. The total
number of records is approx. 600.

I don't think this data set is large. maybe the code is creating this
complexity. can this code be written in a more efficient way?

Naraine