Importing Text - Unusual delimiters

[Jackfam58]

Sample output from .txt file
AL³00002³0351³02³20001³Drug1
PL||00002||0351||02||20001||Drug1


Notice that some of the fields are separated by a small 3 and others are separated by ||. I attempted to import the first file manually(External Data-Text- I then clicked Advanced and attempted to set up a text import specification called cms2

Sub importCMS()
Dim strDir As String, strFile As String
strDir = "C:\pc1_export_files\CMS\"
strFile = Dir(strDir & "*.txt")
While strFile <> ""
DoCmd.TransferText acImportDelim, "cms2", "cms01", strDir & strFile, True, ""
strFile = Dir
Wend
End Sub

The objective is to import all text files that reside in a folder called CMS. What happens is the data imports all in the same column and not separated by the apprpriate delimited. Is there a way to address the different delimiters. I think the issue is how to set up the import specifications correctly.

[redbull]

I have the same problem with my imports when dealing with a txt file. Going to be watching this one closely.

[ser01]

Access stores the Import and Export Specifications in 2 system tables:

MSysIMEXSpecs
MSysIMEXColumns

The 2 tables can be joined using the SpecID field. You could create a query with the 2 tables that would allow you to change the data. That way, you could change an existing spec and perhaps even create a whole new one. This should be a lot easier than creating a whole new spec from scatch Here is some SQL to get you started:

SELECT    
MSysIMEXSpecs.SpecName,   MSysIMexColumns.*
FROM    
MSysIMEXSpecs   
LEFT JOIN MSysIMEXColumns      
ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID 
WHERE   
SpecName = 'MySpecName' 
ORDER BY   
MSysIMEXSpecs.SpecID, MSysIMEXColumns.Start;

Drop this into a new query and try experimenting with it.
Also, consider posting a small sample of your data and someone may try and come up with a more concrete solution. Please post back and let us know how it goes.

[Jackfam58]

ser01
here is a sample of the data
Sample output from .txt file
AL³00002³0351³02³20001³Drug1
PL||00002||0351||02||20001||Drug1
Notice that some of the fields are separated by a small 3 and others are separated by ||. I attempted to import the first file manually(External Data-Text- I then clicked Advanced and attempted to set up a text import specification called cms2

I plugged in your code and pulled it up as a query. I do not understand what it does

[ser01]

ser01
here is a sample of the data
Sample output from .txt file
AL³00002³0351³02³20001³Drug1
PL||00002||0351||02||20001||Drug1
Notice that some of the fields are separated by a small 3 and others are separated by ||. I attempted to import the first file manually(External Data-Text- I then clicked Advanced and attempted to set up a text import specification called cms2

I plugged in your code and pulled it up as a query. I do not understand what it does

Taking a closer look at your data, I realized that it may be very difficult fi not impossible to do this with a modified import spec.
So I was thinking of accomplishing this a different way. It seems that the data strings in the different fields are of the same length. If this is correct, then you could import the data all in one field, then use a query to break up the string into the different fields. I used a select query to try this, and it all works very well. I will post the SQL. You could take the code, drop it into a query, then modify it to an update query. Or you could also use a make table query, and uncheck the field that contains the field with all the original data. That would create a table, that would look exactly like your correctly imported table would have looked like.

I know this sounds a bit convoluted, i'm just very tired.

So, here is the SQL code:

SELECT SampleData.iData, Left([iData],2) AS Fld1, IIf(Mid([iData],3,1)<>"|",Mid([idata],4,5),Mid([idata],5,5)) AS Fld2, IIf(Mid([iData],3,1)<>"|",Mid([idata],10,4),Mid([idata],12,4)) AS Fld3, IIf(Mid([iData],3,1)<>"|",Mid([idata],15,2),Mid([idata],18,2)) AS Fld4, IIf(Mid([iData],3,1)<>"|",Mid([idata],18,5),Mid([idata],22,5)) AS Fld5, IIf(Mid([iData],3,1)<>"|",Mid([idata],24,5),Mid([idata],29,5)) AS Fld6FROM SampleData;

The field that I imported all the original data into is called: iData
The fields that break the data into the different fields are: Fld1-Fld6

Bellow is a screenshot with the original data. I have changed the first string a bit, just so it can be differentiated from the second one to make sure each type is processed correctly.



And here is a screenshot with the table that the create table query created:



I am pretty sure you would be able to make this work for you. Please post back to let us know either way.