VBA to import text file

[robertbwainwright]

I'm trying to import a pipe delimited text file into a table. I can import the entire table using the following code, but I only get one column of data (the entire data set in one column). If possible I would like to import with the columns defined or if not possible use some code for a function similar to text to columns. Any help is appreciated.

 DoCmd.TransferText acImportDelim, , "tblTest", "C:\Work\2015\PPV\MasterData.txt"

 

[CJ_London]

if this is a one off, I would just use the access import facility.

If it is not a one off, then use the import facility the first time and save the import profile, then use this in your transfertext command for subsequent imports

The other alternative is to create a schema.ini file - see this link

http://support.microsoft.com/en-us/kb/149090

 

[robertbwainwright]

if this is a one off, I would just use the access import facility.

If it is not a one off, then use the import facility the first time and save the import profile, then use this in your transfertext command for subsequent imports

The other alternative is to create a schema.ini file - see this link

http://support.microsoft.com/en-us/kb/149090

CJ, thanks for the reply, I'm doing the import route already and it works fine but I want to make a dynamic procedure that I can but in variables for files. My end goal is to make a module import data from our ERP system outputs and strip off the header it puts on the file. I want to do this dynamically and I have been successful except for getting the text into separate columns.

 

[CJ_London]

if you want to create a generic importer which works with different text files (with different column headings/datatypes etc) 'automatically' then you'll need to go the schema.ini route.

Another option is to open the txt file, replace pipes with commas and save as a .csv file.

text files come in all shapes and sizes with issues like use of text delimiters in text, varying field types in the same column (e.g. currency value with no cents shown as 1234 rather than 1234.00)

One other option I have used with limited success (but if you only have the one 'type' of file, i.e. pipe delimited, that should be OK) is to manually complete a record in MSysIMEXSpecs and use that as your profile in transfertext.

Be aware this table is where Access stores the import profiles created using the import facility and has to be created by Access - if you try to create it manually, Access won't recognise it. This is only an issue if you recreate the db - you'll also need to create an import spec to create the table.

It is a while since I did this so can't provide more info, but google MSysIMEXSpecs to find out more.

Personally, I would go the schema.ini route

 

[Frothingslosh]

I brute-forced something similar a few years back by importing the file into a variable and then using Split on it, dropping the result into an array that I then cycled through.

That only works on files up to a certain size, however.