function to Import- change field to memo or text

[mchadwick]

I am running this Function to import several tables into Access.

Option Compare Database
Public Function csvImport()
Dim InputDir, ImportFile As String, tblName As String
InputDir = "C:\Import_Export\To_Import\"


ImportFile = Dir(InputDir & "\*.csv")
Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'This is to import each file into single tables.
tblName = "tablename" 'This is to import all files into one table.
DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True
ImportFile = Dir
Loop

End Function

It works really well doing just that. However, I also need some of the fields to import as text when they are naturally importing as a number (I will not be able to change the way it is exported from the other end) and some are importing as text and I need them to import as a memo (more than 250 characters sometimes). Because of this, I am losing the zip code on a good amount of data and that is the second most important field in the database. If I do a manual import and change the field types to what I need them to be it works great. I can even save the import and use that, but I then have to import each table manually and this decreases the value of the db. The idea is to suck the files in and spit them back out the other end the way we need them to be.

My real question is, how do I tell the function how to change how the data gets imported? Can I use a saved import to do it from the function?

Any help would be greatly appreciated.

[rpeare]

As long as you are not importing a text field into a numeric value you shouldn't have any issues if you set the fields you want as text to text on your import table and memo on the ones you want as memo. If you are importing all your data into a static table just change the data types. If you are importing the data into a new table each time you can't just do a blanket create and expect access to guess what data type you want. You want to use a CREATE TABLE macro query and set the data types you want.

EDIT: That should say CREATE TABLE QUERY not CREATE TABLE MACRO QUERY

[mchadwick]

I have not tried the create table macro yet, but I have tried importing into an existing table and setting all the field types the way I needed them to be. I still get the same errors. The only way I have been able to get it to import properly is to use a saved import that I already setup the way I needed them to go.

[rpeare]

without knowing what your data looks like or what errors you are receiving I can't help you. I've imported numeric fields to text fields going from excel to access so I know that part works. I haven't messed with memo fields but the concept is the same.

[mchadwick]

It looks like any time the zip code has the full set of numbers (instead of 5 numbers it has 9) example 06437-0000. When this comes up it wont work. I can attach a file that I am trying to import if you like?

[rpeare]

That's fine just make sure there's nothing that's going to violate any sort of privacy issues with the data. If there is just put in some fake data.

[mchadwick]

Okay, here you go. The 3 fields I have issues with (highlighted in yellow) are property address zip code, Work instructions and CFSC Comments to contractor.

[mchadwick]

I was just able to save the import specs the way I want to import the data. Is it possible to add those specs to the function?

[rpeare]

There's nothing attached, you have to click the upload button when you attach something.

[mchadwick]

Odd, I did hit it. Let me try again. It was an invalid file type.

[rpeare]

Dim InputDir, ImportFile As String, tblName As String

InputDir = CurrentProject.Path & "\"
ImportFile = Dir(InputDir & "*.csv")
Do While Len(ImportFile) > 0
    tblName = "tblImportedInfo" 'This is to import all files into one table.
    DoCmd.TransferText acImportDelim, "ImportSpec", "tblImportedInfo", InputDir & ImportFile, True
    ImportFile = Dir
Loop

this is the code I used and it works.

I made a table called tblImportedInfo with all the fields you have and the data type I wanted. You have made an import spec, I named mine IMPORTSPEC

You can see in the transfertext code how those are used. I would just be careful about doing bulk inserts of files, you may not have ONLY the files you're interested in or you may have files that are older and should not be imported etc.

EDIT You have to set trusted locations for your database for you to be able to use an import spec, otherwise you have to call a saved procedure that you set up through manual steps. Secondly I used currentproject.path because I was testing with .csv files that were in the same folder as the database.

[mchadwick]

It's all looking good, but I get the error:
Run-time error '3625':

The text file specification 'ImportSpec' does not exist. You cannot import, export, or link using the specification.

I have my spec named exactly the same. Not sure what Im doing wrong.

[rpeare]

Have you set trusted locations? If you're using Access 2007+ you have to set your database as a trusted location before you can use import/export spec names in your code.

[mchadwick]

Yes, I did

[rpeare]

I don't think you've set it for wherever you're storing your database. I'd double check that. I can send you my test database but it will fail if it's not in a trusted location either.