Text to columns?

[ppataki]

Dear All,

I have a csv file that is separated with comma values so I can import it easily into Access
But the imported table's first field contains a text string which is delimited with _ characters, eg: text1_text2_text3_text4

Could you please advise on how I could parse that field to separate fields in Access?


Many thanks in advance

 

[DCrake]

Can you provide a same of the file for more info?

 

[ppataki]

Of course, sample attached below

 

[DCrake]

This zip file would not unzip.

 

[ppataki]

let's try this one then
(I renamed the csv file to txt)

 

[DCrake]

I have just imported the text file into access with any errors. Are you stating that the first row contains field names when you step through the wizard?

David

 

[ppataki]

No I am saying that the first field (EntName) has values in this format: text1_text2_text3_text4
which I would like to parse to separate columns

 

[DCrake]

The problem you have here is that the first column of data is infact 4 fields but there is only one column heading for it. What you would have to do is to import it into a temp table then split out the first field into 4 seperate items and run an append query to copy the data into a table that has the correct structure. (The 3 additional fields).

Another alternative that would take a little bit of vba would be to first open the file directly using the Open File for Input command. Read each line of the incoming data, replacing the underscores with a comma.

then saving the updated lines to another text file. Then you use the revised file to import in to Access. There are 2 issues with this.

1. You would have more columns of data than you have column headings
2. Access doesn't know what the column heads should be.

Solution:
First create a correctly structured tabe that will recieve the incoming data.
Secondly when saving the raw data to the new file you would omit the column headings found in the first row. And tell your import spec that the first row does not contain column headings.

David

 

[ppataki]

Hi David,

I tried your solution but it still does not work, the data was imported the same way, the first field contains the string and was not parsed to separate columns
Maybe I did something incorrectly?
(screenshot attached)

Many thanks

 

[DCrake]

Had a look at writing the script to automate the import, however I have come accross an important issue, which is, not all records have four distinct fields that are joined with an underscore. Such as:

UK,ENG,CUST,ORDER
UK,ENG,EMP,ORDER
UK,KEYACCT
UK,NALCO,DIRECT
FIN,KEYACCT
UK,CASH,COLLECTIONS

Therefore it is not possible to distinguish which text goes into which field.

You need to get you provider to modify their output routine to accommodate this. Once could say you could guestimate this, but how confident would you be that all options have been considered.

Sorry
David

 

[ppataki]

No problem, thank you for trying!

 

[DCrake]

If you do get it resolved here is the code that rewrites the incoming textfile to a correctly formatted text file.

Public Function MakeTxtFile(SourceFile As String, TargetFile As String)
'This function reads the contents of the SourceFile that contains the invalid underscore characters
'in the data items. replaces them with commas and writes the amended line to the TargetFile.
'
'The next step is to use the TargetFile as the new sourceFile to import into the prestructured Access table.
'
'Note the target file does not need to know the field headings as they have already been established via the table accepting the data.

'Check to se if the source file exists before doing anything

If Dir(SourceFile) = "" Then
    MsgBox "Cannot find " & SourceFile & ". Import rountine abandoned", vbExclamation + vbOKOnly, "Procedure Failed"
    Exit Function
End If

'if the target file already exists then ask the user if they want to overwrite it

If Dir(TargetFile) <> "" Then
    If MsgBox(tartgetfile & " already exists. Do you want to overwrite it?", vbQuestion + vbYesNo, "File already exists") = vbYes Then
        Kill TargetFile
        DoEvents
    Else
        MsgBox "Import cancelled by user", vbInformation + vbOKOnly, "Information"
        Exit Function
    End If
End If

Dim strLine As String
Dim nIndex As Integer

nIndex = 0

Open SourceFile For Input As #1
Open TargetFile For Output As #2

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
        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
Close #1
Close #2
Debug.Print "Done"

End Function

 

[ppataki]

Thank you very much for the solution!!!

 

[ppataki]

Hello,

I checked the above code and basically what it does is that it creates a new file, parsing the first column

In fact I would like to add as many new fields to the input table as many output Split makes and fill them in with the output of Split

Here is a code I was trying without success

Sub Splitter()
Dim db As DAO.Database
Set db = Application.CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("input")
Dim ary
ary = Split(rs!entname, "_")
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
Rem Debug.Print ary(0)
Rem Debug.Print ary(1)
Rem Debug.Print ary(2)
Rem Debug.Print ary(3)
rs.MoveNext
Loop
Dim strsql As String
Dim rstupdate As DAO.Recordset
strsql = "SELECT * From [input]" 'No WHERE Clause
Set rstupdate = db.OpenRecordset(strsql)
rstupdate.AddNew
rstupdate!test = ary(0)
Rem rstupdate!Field2 = ary(1)
rstupdate.Update
rstupdate.Close
Set rstupdate = Nothing

End Sub

I attach a screenshot also of what I am talking about

Many thanks in advance

 

[DCrake]

As I say earlier it would be easy if the first field always had three underscores seperating four fields, but it doesn't. How do you know which array element belongs to which field. When there are 4 it's easy as you example shows, but what about the one
FIN_CASH_COLLECTIONS?
FRA_KEYACCT?
ETC

How would you split that?

David

 

[ppataki]

Hello,

It is not a problem if we dont know which field is which
The aim is to get them separated and put them into those fields as shown in the screenshot

Many thanks in advance

 

[DCrake]

If you are not bothered which field they go in then why do you want to split it in the first place.

From what I can see the first element is the country in full
The second is an abreviation of the country
the third is data type order/invoice/etc
but you have some that don't follow that concept. You either want the data split meaningfully or not split at all. You need to look at the arguments for doing both and what are the advantages and disadvantages of doing either.

Are you going to be using any of the seperate fields later on?

David

 

[ppataki]

I would need to use all 4, but, for example I am not interested in CASHCOLLECTION, KEYACCT, etc
For me those ones are important where all 4 arguments are filled in
So I still need to get this field apart somehow to be able to work with it

 

[DCrake]

Right So we know that the first field after this bogus field is a date. Therefore the first step is to extract the left portion of the string upto that point. You can do this by using a loop reading from left to right looking for a numeric character. Once you have established this you need to use the split function with an underscore as the delimeter to seperate the bogus field into an array. Or do another loop through the field counting the underscores. If it finds three then you need to parse the four words and append them to the relevant fields.


Step 1: Where does the string end

For nPos = 1 To Len(StrItem)
     If  IsNumeric(Mid(StrITem,nPos,1)) then
         SubStr = Left(StrItem,nPos-1)
         Exit for
     End If
Next

Step 2: How many underscores are they

For cPos = 1 to Len(SubStr)
    If Instr(SubStr,cPos,1) = "_" Then
       uCnt = uCnt + 1
    End If
Next

Step 3: Evaluate uCnt

Select Case uCnt
    Case 1
    Case 2
    Case 3
End Select

David

This code is all aircode and simplified for brevity.

 

[ppataki]

This looks really great!
My only problem is that unfortunately I am not an expert enough to know how to finish the code, I tried the following:

Sub split()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("input")
StrItem = rs!Entname
'Step 1: Where does the string end
For nPos = 1 To Len(StrItem)
     If IsNumeric(Mid(StrItem, nPos, 1)) Then
         SubStr = Left(StrItem, nPos - 1)
         Exit For
     End If
Next
'Step 2: How many underscores are there
For cPos = 1 To Len(SubStr)
    If InStr(SubStr, cPos, 1) = "_" Then
       uCnt = uCnt + 1
    End If
Next
'Step 3: Evaluate uCnt
Select Case uCnt
    Case 1
    Case 2
    Case 3
End Select
End Sub

But when I run it nothing happens
Could you please give me some hints?

Your help is very much appreciated