Importing multi line data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a bunch of data that I am having problems with importation. The document gives me the field names, however the data spans 2 rows, like such

row1 name1 Address case dat
row2 name2 city state zi
row3 <empty
There are hundreds of names so retyping it is not an option
How, if possible, can I import this data, to either excel or access, that would generate both rows in one fileId?
 
You have to use code and read the file one line at a time.

Something like this:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub

--
Joe Fallon
Access MVP



omakler said:
I have a bunch of data that I am having problems with importation.
Click to expand...
The document gives me the field names, however the data spans 2 rows, like
such:
row1 name1 Address case date
row2 name2 city state zip
row3 <empty>
There are hundreds of names so retyping it is not an option.
How, if possible, can I import this data, to either excel or access, that
Click to expand...
would generate both rows in one fileId?
 
If the layout of the records is consistent and there's always an empty
line between records, it'w worth experimenting with Find&Replace in a
text editor or in Word.

Start by replacing all the empty rows (i.e. two line breaks in
succession, or paragraph marks if you're working in Word) with something
that doesn't appear in the data (e.g. $%$). Then replace all the
remaining line breaks/paragraph marks with tab characters (or whatever
else separates the fields within the original rows). Finally replace all
the $%$ with line breaks (paragraph marks if in Word) and save the
result (as a text file if in Word).

This should give you a file that Access can import.
 
Back
Top