Import Text File

N

Newbie

Hi,

I need to import a FixedLength text file every month and every month I have
to go through the process of setting up which columns I want to import and
so on.

In Access you can save the import spec for use later on i.e. I only have to
import the file once and then on subsequent imports I use the same spec.

Is this possible with Excel, if yes could someone give me step by step
instructions or direct me to a place that can tell me

Thanks
 
N

Newbie

Thanks but I am not quite sure how this helps me. . . My vba is not that
good

What I need to do is:
Import each line in the text file as a row in the spreadsheet but . . .

Starting at row 35 of the text file
Import into column A postion 1 to 7 of the text file - formatted as text
(these are numbers but need to be text as they have leading zero's)
Import into column B position 91 - 109 - general format
Import into column C position 110 - 111 - general format
Import into column D position 112 - 130 - general format
Import into column E position 131 - 132 - general format

Thanks for you help so far

Alison
 
K

kkknie

Here's some simple VBA to do it (I think).

Code
-------------------
Sub Importit()

'Import each line in the text file as a row in the spreadsheet but . . .
'Starting at row 35 of the text file
'Import into column A postion 1 to 7 of the text file - formatted as text
'(these are numbers but need to be text as they have leading zero's)
'Import into column B position 91 - 109 - general format
'Import into column C position 110 - 111 - general format
'Import into column D position 112 - 130 - general format
'Import into column E position 131 - 132 - general format

Dim strLine As String
Dim i As Long

Sheets("YourSheetName").Select

i = 1
Close
Open "c:\Yourfilename.txt" For Input As #1

Line Input #1, strLine
If i < 35 Then
Cells(i, 1).Value = strLine
Else
Cells(i, 1).Value = "'" & Mid(strLine, 1, 7)
Cells(i, 2).Value = Mid(strLine, 91, 18)
Cells(i, 3).Value = Mid(strLine, 110, 2)
Cells(i, 4).Value = Mid(strLine, 112, 18)
Cells(i, 5).Value = Mid(strLine, 131, 2)
End If
Close #1

End Su
 
N

Newbie

Thanks for your help so far but it doesn't quite work . . .

A) I don't want to import rows 1 to 34 at all
B) It doesn't step through each line
c) I want it to import row 35 of the file into row 2 of the worksheet and
then move down the rows

Any further suggestions?

Thanks again

Alison
 
N

Newbie

Thanks again - I have edited your code slightly and it now does what I want
Here is what I ended up with . . . .

Dim strLine As String
Dim i As Long
Dim r As Long
Sheets("March2004").Select

i = 1
r = 2
Close
Open "C:\testtext.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strLine
If i > 34 Then
Cells(r, 1).Value = "'" & Mid(strLine, 1, 7)
Cells(r, 2).Value = Mid(strLine, 91, 18)
Cells(r, 3).Value = Mid(strLine, 110, 2)
Cells(r, 4).Value = Mid(strLine, 112, 18)
Cells(r, 5).Value = Mid(strLine, 131, 2)
r = r + 1
Else

End If
i = i + 1
Loop
Close #1

End Sub
 
K

kkknie

Oops!


Code
-------------------
Sub Importit()

'Now starts at row 35
'Now loops
'Now puts data starting at row 2

Dim strLine As String
Dim i As Long

Sheets("YourSheetName").Select

i = 2
Close
Open "c:\Yourfilename.txt" For Input As #1

Do Until EOF(1)

Line Input #1, strLine
If i >= 35 Then
Cells(i, 1).Value = "'" & Mid(strLine, 1, 7)
Cells(i, 2).Value = Mid(strLine, 91, 18)
Cells(i, 3).Value = Mid(strLine, 110, 2)
Cells(i, 4).Value = Mid(strLine, 112, 18)
Cells(i, 5).Value = Mid(strLine, 131, 2)
End If

Loop
Close #1

End Su
 
D

Dave Peterson

I think I'd just record a macro when I did it once manually.

The first step of the wizard asks you what row you want to start with.

Then you can parse your input line any way you want using the Fixed width
option.

I've found that excel's text importing is much quicker than parsing the input
records myself.

(And when I do this, I never remember which record to start with. So I start
with record 1. But at the end of my code, I can delete those rows I don't
want.)
 
O

onedaywhen

I don't know what you mean by 'import spec'. Are you referring to the
schema.ini file?
 
Top