Separate comma delimited values in a new table (1 Viewer)

Ltravis · Sep 25, 2018

Hello All,

This is my first thread post in this forum. I have been scouring the internet to help identfy a resolution to my issue with no results. I am trying to take a file that produces results that are to large for excel to handle and create a reliable export of unique rows.

My input data looks like this:

Code:

 [COLOR="BLUE"]File name:[/COLOR] AF12345678912345678
[COLOR="Blue"]SCode:[/COLOR] 1,2,3,4,5
[COLOR="blue"]Error Code:[/COLOR]34567,45678,23456,67890,34567
[COLOR="blue"]Error Message:[/COLOR] Green Grass is great., Wish I could Figured this out., Man this is hard!
  [COLOR="blue"]Error Segment:[/COLOR] HI*BH:50:D8:20180622*BH:50:D8:20180627~,HI*BH:50:D8:20180622*BH:50:D8:20180627~,HI*BH:50:D8:20180622*BH:50:D8:20180627~,HI*BH:50:D8:20180622*BH:50:D8:20180627~

The end results should look like this:

Code:

[COLOR="blue"]File Name:[/COLOR] AF12345678912345678 [COLOR="blue"]SCode:[/COLOR] 1 [COLOR="blue"]Error Code:[/COLOR]34567 [COLOR="blue"]Error Message:[/COLOR] Green Grass is great. [COLOR="blue"]Error Segment:[/COLOR] HI*BH:50:D8:20180622*BH:50:D8:20180627~

[COLOR="blue"]File Name:[/COLOR] AF12345678912345678 [COLOR="blue"]SCode:[/COLOR] 2 [COLOR="blue"]Error Code:[/COLOR]45678 [COLOR="blue"]Error Message:[/COLOR]Wish I could Figured this out. [COLOR="blue"]Error Segment:[/COLOR] HI*BH:50:D8:20180622*BH:50:D8:20180627~

[COLOR="blue"]File Name: [/COLOR]AF12345678912345678 [COLOR="blue"]SCode:[/COLOR] 3 [COLOR="blue"]Error Code:[/COLOR]23456 [COLOR="blue"]Error Message:[/COLOR]Man this is hard! [COLOR="blue"]Error Segment:[/COLOR] HI*BH:50:D8:20180622*BH:50:D8:20180627~

[COLOR="blue"]File Name:[/COLOR] AF12345678912345678 [COLOR="blue"]SCode:[/COLOR] 4 [COLOR="blue"]Error Code:[/COLOR]67890 [COLOR="blue"]Error Message:[/COLOR][COLOR="Red"]Null  [/COLOR][COLOR="blue"]Error Segment:[/COLOR]HI*BH:50:D8:20180622*BH:50:D8:20180627~

[COLOR="Blue"]File Name:[/COLOR] AF12345678912345678 [COLOR="blue"]SCode:[/COLOR] 5 [COLOR="blue"]Error Code:[/COLOR]34567 [COLOR="blue"]Error Message:[/COLOR][COLOR="Red"]Null  [/COLOR][COLOR="blue"]Error Segment:[/COLOR][COLOR="red"]Null[/COLOR]

The_Doc_Man · Sep 25, 2018

OK, here are a couple of ideas.

First, your issue is that you need to split records into sub-records. You are lucky that you have delimeters that are consistent.

Second, you have a couple of approaches that you could take.

Approach A: You could just try to import the file as a .CSV file, which Access will read. In that case, the first record would contain (for example) one field = File name: AF12345678912345678 and the second record might contain five fields, the first of which would be SCode: 1. I think you can see where this is going.

Approach B: Read the file as a text file using VBA and the OPEN and LINE INPUT verbs (and CLOSE verb). You can look them up easily. Then write a loop or just hard-code if it is only five lines. Look up the SPLIT and the INSTR functions. You can use INSTR to find the first colon on the line. The LEFT$ based on that colon's position will tell you which line you are reading. The RIGHT$ based on the first colon will give you the rest of the line. For the first line, you would just copy that five times.

You would then use the SPLIT function using the comma as a delimiter to break up the subsequent lines into an array of however many parts you have. (Looks like five parts implying five output records for your example.) SPLIT is NOT limited to five split segments so if you ever had six segments, it would handle it OK.

Read up on those options because one or the other will be what you need to do. I can't choose for you.

arnelgp · Sep 25, 2018

can you post the complete file maybe through dropbox.

Ltravis · Sep 25, 2018

I am sorry arnelgp, I can not it is sensitive information.

I have tried the CSV approach. There was data lost in this approach and the values where not all seperated properly.

I have tried the formula approch seperating the values with Left$, Mid & Right$ but because of the comma being the delimiter and some data sources only having one number (exp: sCode:3 instead of sCode: 3,4,5) I had to systematically add a comma to all values to get them to read. This is not an ideal repeatable solution as I need to build this for a Non-Access user.

I tried to create a SPLIT function in a select statment but this had no effect. I beleive I am missing the related VBA Function to call here:

Code:

SELECT mySplit([DCN/Filename],",",0) AS Filename, mySplit([Severity code],",",1) AS Severitycode, mySplit([Error code],",",2) AS Errorcode, mySplit([Error Message],",",3) AS ErrorMessage, mySplit([Error Segment],",",4) AS ErrorSegment
FROM Delimited_Results;

I have also tried the Function provided below:

Code:

Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long, Optional sDelim As String = ",")

Dim sElements() As String

sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
    GetValueFromDelimString = ""
Else
    GetValueFromDelimString = sElements(nPos)
End If

End Function

I can not figure out how to call the function in a qry though:

Code:

GetValueFromDelimString([MultiValueField],0) AS FirstElement, GetValueFromDelimString([MultiValueField],1) AS SecondElement, etc.

Please advise on the best way to proceed.

Thank you in advance!!

arnelgp · Sep 25, 2018

you cant use query with it, it's too complex.
you need to save it on the table.

here is a sample importer for your errors.

add reference first to Microsoft Office XX.X Object Library.

browse the code behind the MainForm.

EDIT: how sensitive is it. can it be sold to black market. there is no one interested in your file here, except to dissect it and see all posibility to code.

Ltravis · Sep 25, 2018

Please find the requested data linked/attached below...

Sample file attached:

View attachment Sample.xlsx

The Database provided did not allow for my files to be drawn in as expected. I did update all appropriate/relevent field names and table names for the sampled date to reflect properly.

I Imported the data into the sample database table. The data was not sorted as expected.

Please let me know based on the sample data provided if the expected export is possible.

I have tried to use the following found on another thread:

Code:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function

And calling it like this in the query:

Code:

ParseText([Sequence Code],1)

However the results are the same as using Left$( function. It returns no value for the rows that have only 1 value and no comma.

Ltravis · Sep 25, 2018

From the previous post above continued....

Does anyone know why I am getting null values appended to the fields if no comma exists? Is there a condition I can use if no comma exists to not append the data?

This is the select statement I am using:

Code:

INSERT INTO Results1 ( DCN_Filename, Severity_code, Error_code, Error_Segment )
SELECT Delimited_Results.[DCN/Filename], ParseText([Delimited_Results]![Severity code],0) AS [Severity code], ParseText([Delimited_Results]![Error code],0) AS [Error code], ParseText([Delimited_Results]![Error Segment],0) AS [Error Segment]
FROM Delimited_Results
WHERE (((Delimited_Results.[Severity code]) Like "*,*") AND ((Delimited_Results.[Error code]) Like "*,*") AND ((Delimited_Results.[Error Segment]) Like "*,*"));

Counting up 0,1,2,3,etc.... The concept is that it appends less data as the amount of comma(ed) values are reduced. I am getting the same amount each time.

arnelgp · Sep 26, 2018

here again is the samole. test it with excel file. if you must import multiple xls nake sure the columns names are same with the table.

Ltravis · Sep 26, 2018

In attempting to run the database I have run into the following error:

Run-time error '3011'.

The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure that the object exists and that you spell it's name and path correctly. If 'Sheet1' is not a local object, check your network connection or contact the server administrator.

arnelgp · Sep 26, 2018

find sheet1 in the code and replace with the sheet name in the workbook.

Frothingslosh · Sep 26, 2018

Ooooh, that looks like you're working with 834 files. If that's the case, you really might be better off finding an existing product. Even the error reporting gets pretty complex, and mistakes can have legal consequences.

Ltravis · Sep 26, 2018

arnelgp: I have done this and it still gives me the same error

arnelgp · Sep 26, 2018

post the excel so i csn further test. try it first to the excel you have posted earlier.

arnelgp · Sep 27, 2018

i revised the code.
it now checks and display which worksheet
(if there are multiple sheets in the wb)
you want to import.
added validation on table's structure
against worksheet columns.

Separate comma delimited values in a new table (1 Viewer)

Ltravis

New member

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

Ltravis

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Ltravis

New member

Ltravis

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Ltravis

New member

arnelgp

..forever waiting... waiting for jellybean!

Frothingslosh

Premier Pale Stale Ale

Ltravis

New member

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Similar threads

Users who are viewing this thread