Combine two text rows into one record/row in Access table

[kaseu]

I am having trouble getting Access to separate my data in the way I want. I am trying to import a very large text file which has one record on two lines. When I try to import it into a table in Access, the wizard recognizes each line as a separate record. Changing them manually is not an option because my text file is too large (millions of lines). How do I get Access to combine the two lines to create a single record?

 

[BNPMike]

What I would do is import the table and then copy it. For the copy, delete the first line. Now add autonumber columns to each. I'm assuming they'll go from 1 upwards.

Now join the first to the second. Every record from the first table will match the record after it in the second table because you deleted a record. With this query you can append a new table you have designed, concatenating the two fields before loading.

This will get you all your data. Problem is you'll have a set of A+B interspersed with a redundant set B+A. How you separate those - is somebody else's turn to answer.

 

[RoyVidar]

Never been able to make those wizard thingies work with the data I've imported, and always had to revert to reading the textfile "manually".

- one question here, would also be if it is only Access having problems with the record, or if the records are also viewed as two lines when viewing in a text browser. I'm assuming the latter in this suggestion.

The fastest thing, would simply be to read the whole file into a variable, and manipulate that, but here I'll give a small sample of reading line by line (or two lines at a time).

Note - this is a very generic approach, errorchecking, validation and assembling the pieces will be up to you, if this approach is appliccable...

Requires that the libraru Microsoft Scripting Runtime is checked (in any module Tools | References...)

[tt]dim fs as scripting.filesystemobject
dim txt as scripting.textstream
dim sLine1 as string
dim sLine2 as string
dim arr1() as string
dim arr2() as string

set fs=new scripting.filesystemboject
set txt=fs.opentextfile("c:\test.txt", ForReading)
do while not txt.atendofline
sLine1=txt.readline
sLine2=txt.readline
arr1=split(sLine1,";&quot
arr2=split(sLine2,";&quot
' ... validating, dumping to recordset...
loop
txt.close
set txt=nothing
set fs=nothing[/tt]

Reading the two lines, assigning them to two different variables. Using the Split function to splitt the lines into arrays - which is possible if the file is using some kind of delimiter.

Should your version not support the split function, there are ample examples in these foras on how to create your own, else use the Mid function on the lines (which you'd probly have to use if the file is fixed width).

Looping thru arrays:

[tt]dim lCounter as Long
for lCounter = 0 to Ubound(arr1)
Debug.Print arr1(lCounter)
next lCounter[/tt]

Assigning values to a recordset (using DAO):

[tt]dim rs as dao.recordset
dim db as database
set db=currentdb
set rs=db.openrecordset("MyTable&quot
rs.addnew
rs!field1=arr1(0)
rs!field2=arr1(1)
...
rs.update[/tt]

Or perhaps, if possible, use the index:

[tt]...
rs.addnew
for lCounter=0 to ubound(arr1)
rs(lCounter)=arr1(lCounter)
next lCounter
rs.update
...[/tt]

Files I've been working with has needed some extra validation, so my favorite way is building a sql string to append this:

[tt]dim sSql as String
dim sSqlVals as String

sSql="Insert into sometable ("
sSqlVals=" Values ("
if isnumeric(arr(0)) then
sSql=sSql & "NumFieldName, "
sSqlVals = sSqlVals & val(arr(0)) & ", "
end if
if len(trim$(arr1(1)))>0 then
sSql=sSql & "TextFieldName, "
sSqlVals=sSqlVals & "'" & trim$(arr1(1)) & "', "
end if
...[/tt]

and use the runsql method of the docmd object, or execute on a connection object...

Hope some of this might be a step in the direction you need.

Roy-Vidar

 

[BNPMike]

There you are - two completely different approaches. One 3GL and one 4GL.