Import files into access automation

[Rob_U]

Hi

I have a form on access which allows users to upload csv files.
The files can be in any layout, so the field names and the number fields will always be different each time.
This can cause issues on data types, as access assumes a certain data type for a field, but the data may not always reflect that.

For example telephone numbers, access assumes this is a numeric field, however sometimes there will be symbols in this field such as +44. Which then causes import errors

Is there any way to import these files without getting these sort of errors?
Ideally if they could all be imported as text fields, this would solve all my problems, but I don't believe that is possible.



Currently the import process uses a simple TransferText method
DoCmd.TransferText acImportDelim, , "Original Data", Me.filelist.RowSource, True

This imports the data into a new table called 'Original Data'.

I had the idea of saving the table structure of the csv being imported
(Importing the data with errors, then deleting all the records to just leave the structure)
However I can't find a code to change the data types of that table to TEXT format, when I can't tell what the field names will be.

This whole thing is an automated process, which why there are loads barriers btw

Any help or ideas would be greatly appreciated

Thanks
Rob

[JoeM]

I don't know if this helps, but here is something I did some years ago. I actually did it for Excel, but just tested and confirmed that the same concept works in Access too.

We had CSV files that could be any number of fields. So what I did was first determine the maximum number of fields I may get in any file, and then create a shell of a table that has that number of fields, with the Data Type of every field set to "Text".

I then created a very simply test csv file that had one or two rows with maximum number of fields in them.
I then went through the process of manually importing this file, just to create an Import Specification which I could use.

So now that I had the table shell and Import Specification, I could import any CSV file using a TransferText action like you have.

The main purpose of doing this was so a group of non-technical people could easily examine the contents of any CSV file, using this process I set up for them.

[Rob_U]

That's brilliant Joe, thank you for this, this does seem to work.
The only problem now is I need to change the field names to the first row of data (As it currently still uses the temporary field names from the table it imported to) . I've searched online for coding for this, but I can't seem to get it to work correctly. Did you have a way of getting round this, or have gone about this the wrong way?

Thanks for your help again
Rob

[JoeM]

Most of the files we receive do not have field names in the first row, so I never really had to worry about it.
One possibility is to add an Autonumber field at the very end of your table. Then the field name row will always display in the first row of data.
Don't know if that is an acceptable alternative or not.

I think that any other solution is going to require a bit of VBA, maybe using some RecordSets.

[Rob_U]

I've managed to create the table structure and change all the data types to text, so the data can be imported correctly into that table. However it is still getting type conversion error, even though all the fields in the table which it is being imported to are text format.

Does anyone have ideas on this?

[JoeM]

What you could do is upload a copy of your database and a sample file we can test.
Just make sure that you remove any sensitive data/information.

[Rob_U]

Hey

Currently I've created the following code which imports 'Test File.csv' into a new table called original data.
This causes import errors as I have purposely included a mix of text and numbers in the telephone field.
The code then deletes all the data in the data, so only the field header remains.
It then changes field 3 (the telephone field) to a text data type, which should stop any import errors.
However when importing that data into the table, I'm still receiving errors, even though all the fields in table are now text data types.

Now i'm pretty stumped, as I can't think of other way around this?

Private Sub Import()

'Imports raw data into new original data table
DoCmd.TransferText acImportDelim, , "Original Data", "C:\Access Drive\Audits\Test 080415\Test File.csv", True

'Delete all imported data, to just leave the structure
DoCmd.RunSQL "DELETE [Original Data].*" & _
"FROM [Original Data];"

'Appends a unique ref id column to table
DoCmd.RunSQL "ALTER TABLE [Original Data] ADD COLUMN [Unique DHQ Ref] AUTOINCREMENT;"

'Counts how many columns are in the imported table
ColumnCount = CurrentDb.TableDefs("Original Data").Fields.Count

'Defines field 1 name
Field0 = CurrentDb.TableDefs("Original Data").Fields(3).Name

'Edits field 1 to text data type
DoCmd.RunSQL "ALTER TABLE [Original Data] " & _
"ALTER COLUMN [" & Field0 & "] text;"

'Imports the data back in to the table
DoCmd.TransferText acImportDelim, , "Original Data", "C:\Access Drive\Audits\Test 080415\Test File.csv", True

End Sub

Thanks for all your help again

[JoeM]

I don't see any Import Specification being used in your TransferText commands. Note what I said earlier:

I then created a very simple test csv file that had one or two rows with maximum number of fields in them.
I then went through the process of manually importing this file, just to create an Import Specification which I could use.

So now that I had the table shell and Import Specification, I could import any CSV file using a TransferText action like you have.

Try setting up an Import Specification, and using that in your TransferText command.

I also usually don't mess with editing the table structure in the VBA code. If you want a unique ID, you can add an Autonumber field as the last field in your table (after you last possible field). Then you don't need to worry about adding it/messing with it in your VBA code.

[Rob_U]

Hey

I did try using a import spec as you suggested, but it appeared to make no difference.
I made spec based upon a manual import of the test file, but when using it in the transfer text method, I still get errors.

I've attached the test database I have been working with, along with some test data, which I have been trying to import.
There is a module saved in the database, with the code.
Private Sub Import_2 is simple transfer data method using the import spec mentioned.

If you could give me some more tips and advice, that would be brilliant

Thanks
Rob

[JoeM]

I am unable to download the files from my current location, but will do so when I get home tonight, and see if I can figure out what is going on.

[Rob_U]

Thank you Joe

[JoeM]

I have to apologize. I got wrapped up in some business at home and never got a chance to look at this last night.
I will try to take a look at it tonight.

[Rob_U]

No worries Joe, take all the time you need

[ssanfu]

PMFJI,

Add quotes around the specification name:

Private Sub Import_2()

DoCmd.TransferText acImportDelim, "Test_Spec_080415", "Table1", "C:\Documents and Settings\sanforsh\Desktop\AccessForums\Test 080415\Test File.csv", True

End Sub

[JoeM]

I think what Steve pointed out was your issue, but I played around with your database.
I added an Autonumber field to the end of the table that will populate automatically. I also created another copy of the table with no field names.
I then created a Form with two buttons, one that will import to your original table, and one that imports to my new table with no field names. Both ways worked.

Here is the database back with those things in it:
Import Test.zip