Moving data in one field up 1 row

[errolf]

I have a table of imported data with 2 fields,I need to move 1 field up uniformly 1 row in order that I can use the imported data eg:

P/N Supplier
1234 Null
SuppName
Change To
1234 SuppName

I have been putting the data into Excel and moving 1 column up 1 row but I would like to get away from this method.

Thanks in anticipation
Errolf

 

[JoanneM]

P/N Supplier
1234 Null
SuppName


What type of file is your data in .txt .csv or other
what version are you working on

do you just want the 2nd column up one in total

if its text I've had this one before ..pesky but already written


Jo

 

[errolf]

Dear Jo,
thanks very much for you prompt reply, The data is in text .
Best Regards
Errolf

 

[JoanneM]

Sorry for the delay Errolf
This Post for Info
Next post purely code - to make reading easier....

Hardly had time to brush my teeth yesterday

car Stopped in rush hour traffic on way to interview
Decorators were due
-Hubby busy saving the world
Tel Intvw
Decorators arrived
PowerCut
BT came to install another line
Freezers Defrosting
-Hubby still busy saving the world

11:00 pm your code
realised you might have a blank supplier
So I rejigged a little
Things you should know:

1. DAO 3.6 Library is referenced
if you don't understand this
email me on
email2jo@btinternet.com
2. You will need the full path for your text file

3. My table was called supplies amend code to use your table
with fields P_N ,Supplies

text file looked like this for input
P_N Supplier
1234
1235 SuppName1
1236 SuppName2
1237 SuppName3
1238 SuppName4



My output looked like this in the table after multiple Runs

P_N Supplier
1234 SuppName1
1235 SuppName2
1236 SuppName3
1237 SuppName4
1238
1234 SuppName1
1235 SuppName2
1236 SuppName3
1237 SuppName4
1238
1234 SuppName1
1235 SuppName2
1236 SuppName3
1237 SuppName4
1238


Code removed the headers - hope that is OK

Jo

 

[JoanneM]

Sub readuparow()

' put your own error trappping here
' reference DAO 3.6 library

' nb if you have a partnumber without a supplier in the
' list - ensure supplier field in the table has the property
' Zero Length String set to YES or you will get an error

Dim mynumber As Integer
Dim currId As String
Dim currstring As String
Dim dataArray()
Dim spacepos As Integer
Dim mystring As String
Dim mydb As Database
Dim myrs As DAO.Recordset

Set mydb = CurrentDb()
Set myrs = mydb.OpenRecordset("Suppliers", dbOpenDynaset)

Dim LineNum As Integer
Dim x As Integer

'place full path to your text file here

Open "e:\development\tecforum\suppliers.txt" For Input As #1 ' Open file for input.
LineNum = 1 'this is your array index

'This reads the data into a single column array
'ready for manipulation

Do Until EOF(1) ' Loop until end of file.
ReDim Preserve dataArray(LineNum) 'keeps data but resizes array
Input #1, mystring 'Read data into two variables.
dataArray(LineNum) = mystring
LineNum = LineNum + 1
Loop
'_________________________________________________
'the following for next loop can be deleted
' its so you can get a view of the data
For x = 1 To LineNum - 1
Debug.Print x, dataArray(x) 'Original State
Next
'_________________________________________________

Close #1 ' Close file.

'Data is now read into your array ,ready to manipulate
'and write to your table
'
'Initalise the table
'if you want to overwrite not append
'then remove the else option leaving the end if


If myrs.EOF = -1 And myrs.BOF = -1 Then
myrs.AddNew
myrs.Update
myrs.MoveFirst
Else
myrs.AddNew
myrs.Update
myrs.MoveLast
End If

'Process the array data into the table
'without headers
For x = 2 To LineNum - 1

currId = Left$(dataArray(x), InStr(dataArray(x), " &quot) 'chks for space delimeter
If currId = "" And Not IsNull(dataArray(x)) Then currId = dataArray(x)

'Gets nxt rec supplier name
If x + 1 <= LineNum - 1 Then
mystring = dataArray(x + 1)
Else
mystring = &quot;&quot;
End If

currstring = Mid$(mystring, InStr(mystring, &quot; &quot + 1)


'Brings the two together (currId) & (currstring)
'omitting blank records completely
'Write to destination table

If Not IsNull(currId) And Not IsNull(mystring) Then

With myrs

.Edit
!P_n = currId
!Supplier = currstring
.Update

If x < LineNum - 1 Then
.AddNew
.Update
.MoveNext
End If

End With

Else

End If
Next

End Sub

 

[errolf]

Dear Jo ,
thanks for your latest reply I haven't tried it as yet due to me trying to save the world down under.
Thanks very much for your efforts

Best regards
Errolf