Importing .csv file

[Emma35]

Hi Everyone,
Having a little trouble importing external data (.csv file). The problem is that the file has two separate date columns but the date is in different formats in both columns and Access keeps creating a new table called ImportErrors. My two dates are in the following formats

Column 1 - 2013.02.01
Column 2 - 01/02/2013

Is there a way around this ?. I tried the advanced options but nothing worked.



Thanks,
Emma

[JoeM]

I think you can only pick one date format per Import routine.

What I would recommend doing is importing both fields as Text fields, and then converting them to date fields once in Access through an Update Query or VBA.

[Emma35]

Ah ok thanks....i'm trying to organise this for someone who has no Access experience so update queries would probably confuse him more than anything else. I was hoping to sort it out through the importing process. I'll maybe look into getting the .csv file set up differently if possible.

Cheers

[JoeM]

i'm trying to organise this for someone who has no Access experience so update queries would probably confuse him more than anything else

Not if you set up the whole process as a macro!

You could have your Macro import the file, then run the Update Queries. Your users would be none the wiser. All they have to do is click the Macro and let it run.

That being said, clean, consistent data is always nice to work with, if possible! It certainly makes things a little easier and reduces the chances of errors.

[Emma35]

Oh didn't realise you could do that. I checked and i don't see any options in the macro action list to import a file ?

[rpeare]

Blech, macros!

I would suggest using a VB module to do what you want, VB is much more flexible than macros.

If your import file is in the same location and has the same name every time you go to import it a macro might work (though I still don't recommend it).

You can also run a conversion on your text file before you import it, however if this is an export from another piece of software you *should* be able to get an export that does it in a consistent format.

Barring that you can use filesystemobject to manipulate your file before the import.

[JoeM]

Oh didn't realise you could do that. I checked and i don't see any options in the macro action list to import a file ?

You would want the TransferText Action to import a text file. If you do not see it, click on the "Show All Actions" button on the Show/Hide ribbon (in versions of Access 2007 and higher, they hid some of the actions by default for security reasons).

Blech, macros!

I would suggest using a VB module to do what you want, VB is much more flexible than macros.

I agree with rpeare, if you are comfortable working with VB. Macros are all hard-coded, where you can make VB dynamic. So if you have changing file names, VB would work better. One trick that is great for beginners is to start out with a Macro that does what you want for a specific example. Then, highlight that macro and select the "Convert Macros to Visual Basic" button from the Macro ribbon. This will convert your Macro to VBA and give you a good start on your VBA code.

[Emma35]

Thanks for the help guys....i've created a macro which imports the file and added a little message box just to confirm to the user that the data has been imported successfully. I'll look into converting to VB next. I was wondering if it would be possible to add anything to the macro which would prevent somebody importing the same file twice ?. At the moment, this file gets downloaded to the My Documents folder from a scanning device and the new file will automatically overwrite the old one

Thanks,
Emma

[rpeare]

the only way to prevent duplicates is if your source data has some sort of unique identifier (a shipping number, an order number, etc) You'd have to make that field your primary key then if you did accidentally import the same data twice it wouldn't import anything that already existed.

[Emma35]

Ok thanks rpeare i'll organise that. Thanks again for the help guys, everything is working fine now.

[Emma35]

Sorry for being a nuisance guys but another small problem has popped up. The macro imports the data from the .csv file fine, and it goes into the table just like it should. However, one of the dates from the .csv file also has a time stamp and so it looks like this 07/02/2013 10:17
When Access imports the data it formats it to Short Date so i get 07/02/2013 in the field (which is great)....but when i run a query on the table using that field and the date 07/02/2013 as a parameter, i'm getting no results at all ??

Any idea what's wrong or what i need to change ?

Thanks,
Emma

[JoeM]

Its probably importing the time component too (if you change the display format to one that shows time like "General Date", it will probably show it).

There are various ways to address this. In Microsoft programs like Access and Excel are stored as the number of days since 1/1/1900. So the time component represents a partial day (or fraction). So you can create a calculated field to apply your criteria to that removes the time (fraction) from the date using the INT function, i.e.

DateOnly: INT([MyDateField])

There are other ways as well, this is just one.

[Emma35]

Wow you really got me out of a jam there....worked perfectly. Thank you Joe

[JoeM]

Great! Glad to help.