Text to columns?

[DCrake]

Do While Not EOF(1)
    Line Input #1, strLine 'read the incoming line from the SourceFile
    If nIndex > 0 Then 'Do not do anything with the first line - this contains the column headings
>>> Insert Here
        Print #2, Replace(strLine, "_", ",") 'Replace all underscores with commas to act as delimiters and write to the TartgetFile
    End If
    nIndex = nIndex + 1 'Prevent from skipping the next line of data
Loop

The code will need to be inserted as a function here. I have not got enough time or the inclination at present to write it as we are not here to offer full blown solutions, mainly pointers to enable you to reach a solution.

As explained previously once you find out how many words they are in the first field you need to decide which word will og into which field. For example if the field was
FRANCE_FRA_CUST_ORDER would yield FRANCE,FRA,CUST,ORDER

Whereas

FIN_CASHCOLLECTIONS would yield FIN,CASHCOLLECIONS,,

As you need to ensure that nomatter how many underscores you started with you need to ensure you have the at the end. Why? so that the fifth field ( the date field) goes into the date field, if you don't everything will be truncated and missaligned.

David

 

[ppataki]

Hello,

I managed to get another code for this problem:

Sub splitter()
Dim dbCurr As DAO.Database
Dim rsOld As DAO.Recordset
Dim intLoop As Integer
Dim strSQL As String
Dim varReps As Variant
Set dbCurr = CurrentDb()
Set rsOld = dbCurr.OpenRecordset("TableOld")
Do While Not rsOld.EOF
varReps = Split(rsOld!RepID, "_")
If Not IsNull(varReps) Then
For intLoop = LBound(varReps) To UBound(varReps)
strSQL = "INSERT INTO Table2 (Field1,Field2,Field3,Field4) " & "VALUES (" & rsOld!RepID & ", '" & varReps(intLoop) & "')"
dbCurr.Execute strSQL, dbFailOnError
Next intLoop
End If
rsOld.MoveNext
Loop
rsOld.Close
Set rsOld = Nothing
Set dbCurr = Nothing
End Sub

The problem now is that I get an error message:
"Number of query values and destination fields are not the same"

Can anyone help please?

Many thanks!