importing text file to EXCEL

**avveerkar** · 09-28-2006, 03:16 PM

I very regularly need to import text file to excel. Text file format is fixed. It is a fixed width format. ie let us say in each line the characters 1-10 represent date, 11-40 name, 41-50 amount etc. I want to automate import the process so that in the excel sheet first col contains the date, next name and third amount. Manually I can do it by opening the file but then I have to go through the wizard, select field width, type of data etc. I want to write a sub so that it will pick the text file and convert to excel with right data type ( date as date, name as text, amount as number ). Please help.

A V Veerkar

**stevebriz** · 09-28-2006, 03:42 PM

How is the data separated by space, by comma?
Can you post one line fo the txt file?

**avveerkar** · 09-28-2006, 04:26 PM

Originally Posted by stevebriz

How is the data separated by space, by comma?
Can you post one line fo the txt file?

Data is not separated by any delimiter. It is fixed width in the sense first 10 characters are date, next 15 are name, next 10 are amount. Four lines would look like

12/05/2006A V VEERKAR 123.45
03/08/2005John Smith 1234567.89
09/20/2006Gracy Rodrigues 876.54
01/01/2003ABCDEFGHIJKLMNO1234567.89

( Note that first 10 characters make date, next 11 to 25 make name and then 26 to 35 make amount.)

I hope I have made it clear. No delimiter character separating the fields but each field is always of same width on every line. eg date-10, name-15 and amount-10.

A V Veerkar

**stevebriz** · 09-28-2006, 04:52 PM

Ok....clear
You need to read the text file into array line my line
and then split the lines by width and position

Please Login or Register  to view this content.

**avveerkar** · 09-28-2006, 05:00 PM

Thanks a lot Stevebriz. That is exactly what I wanted.

A V Veerkar

**Rijofe** · 03-21-2007, 03:25 PM

Hi Stevebriz,

I'm trying to build a macro similar to that Avveerkar needed but when I try to run the macro, it stops at "Split" with a error message "Sub or Fuction not defined".

Do you know what is going on? Could you help me?

Thanks,
Rijofe

importing text file to EXCEL

LinkBack

Thread Tools

Rate This Thread

Display

importing text file to EXCEL

Help with the code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions