Macro To Importing Several CSV Files into Access

[Lucas in London]

Hi,

I have about 25 CSV files located in a folder that I want to open in access as separate tables within one database. All files are identical in the sense that they all contain only two fields (Coolum 1 date field, column 2 data), the top rows contains column headers. The only thing that is different between the files in the data content and so the length of fields (number of rows).

Opening/importing each file is taking a while and was wondering if I could import all the files using some kind of macro? I'm thinking this should be easy given that I have to go through identical steps to import each file:

1) Get external Data - Import File and select my file
2) Select Comma Delimited in the next window
3) Click first row Contains field names in next window
4) Click import into a new table in next window
5) Do not assign a Primary key

I am completely new to macros for Access – do not even now how to record! So any help will be much appreciated.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

The code could probably be adapted (or re-written) but I can't provide a VBA solution right now. In the meantime if you want to import the files manually, how about selecting Access menu option File > Get External Data > Import. If the tables are set up differently I would recommend importing the data into seperate tables and then using queries to append the data from the 3 tables into one. Again this can be done manually without VBA. If this will get you through the day then we can look at a VBA solution later.

Or you could save your Excel files as csv files and run them through the routine above. Beware the routine may first delete all of the data in the import table (so you may want to copy that if you need it). Also, you will need to get the data into the same format (i.e. same number of columns columns and in the same sequence as the original import routine).

HTH, Andrew

 

[Lucas in London]

Hi Andrew,

Thanks for the guidance. I didn't think I'd be able to import the files as CSV because wasn't the orginal code set up to import only the first two columns?

Anyway, I'm sure I can get the data into a usable format manually for now using the steps you've described. In fact I was thinking of importing of three files into separate folders and then creating a query linking each table using the date fieild and copying the contents of the query to Excel. But an VBA solution at some point would be ideal as I fear I will be doing this quite a lot on a regular basis.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

One way would be to manually manipulate the data into a single text file format that can be imported by the code previously written. I think the original code was written to import 3 fields - it can be easily modified to import more columns (the comments within the code provide some help). Plus I believe it will work on any text file (e.g. csv).

If you would rather have a full VBA solution, can you provide details of the 3 spreadsheets? What are the names and fields for each spreadsheet? And which fields do you want imported? Also, if there are 3 spreadsheets in a directory, how will the code know which one is which?

Andrew

 

[Lucas in London]

Hi Andrew,

I'll try saving the files as CSV and importing into the table based on the
previous code and see what happens.

The names of the three spreadsheets are:

1) BOE.csv (23 Cols including Date Col)
2) Indices.csv (29 Cols including Date Col)
3)Technicals.csv (21 Cols including Date Col)

The names of the fields/columns are in the first row of each sheet, ie. A1, B1, C1 etc.

There are quite a few columns in each sheet so I'm not going to list them here. But it is very likely that the number of columns will vary from time to time. So partly the reason for seeking a VBA solution is so that maybe the macro can determine the columns (and number) to import based on the existence of entries in the first row perhaps or some other way? Also I was hoping that it would be possible to automaticaly import the column names based on what in row 1 of each column rather than pre-specifying them!

Hope this makes sense.

Thanks,

Lucas

 

[Lucas in London]

Hi Andrew,

Did you get a chance to re-visit my question? I've played around with some configurations but I'm still far far away from a automated solution.

Some oberservations. Your import code does not work unless there are at least the same number of fields created in the import table as in the source spreadsheets/CSV files (returns the error "Field 'F6' doesn’t exist in the destination table 'Import File'".

As a test, I removed the several fields in my source files so to constrain each file to a max of four and created two additional fields in the import file destination table to accommodate for this.

In this instance, the code runs but when I view the import table, the field indicating the name of the file (previously in field three) is no longer being populated. However, even if I had this information it would not be that useful, instead is there a way to populate the name of the data series (from the first row of each worksheet) in some designated column say column 10 for example?

So really from my perspective I see that two fundamental changes the procedure needs and I guess from you I need to know if it is possible to do and if not maybe I need to look for a slightly different approach.

They are:

1) I need a macro to automatically determine the number of fields to create in the import file table based on say the maximum number of fields in any given source spreadsheet. Related to this, I was thinking could I just not create say 50 fields in the table even if I don't have that many in the source files? Then if I add some more fields to the source files the code will not need to be changed. Would the macro still work in this case if the number if fields in the table exceeds the maximum number of fields in the source files?

2) I need a column in the import table with shows the Field names to work out what each data series is
3) Another column showing the name of the spreadsheet/source CSV file the data as been pulled from would also be useful - but this is not necessary if I have the fields names.

Interested to hear your thoughts.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

I will have to dig out my test and see what I can do. You could experiment with the idea of the additional fields in a copy of your database. From memory (don't quote me on this) I got some inter-record corruption where the number of fields did not line up - but it would be worth a test.

Anyway, I think we might need a total re-write of the code because you are wanting to import data from spreadsheets, set the field names according to the column headers from Excel, store the source file name and so forth. I think the original code deletes all of the data from the import table prior to importing anything so I know that will give you problems if you try to re-use that code.

This might take a while and I have quite a bit on this weekend but I will see what I can do.

Andrew

 

[Andrew Fergus]

Hi Lucas

Do the 3 spreadsheets have differing columns in differing sequences? If so, then I believe we will need to import the 3 spreadsheets into 3 different tables. Or are there only certain columns you want to import? If you only want to import some particular columns, which are in all 3 spreadsheets but may be in different positions, then it would be possible to import that into a single table. If it is just the 3 spreadsheets have you tried the standard import wizard available under menu option File > Get External Data?

Andrew

 

[Lucas in London]

Hi Andrew,

First of all, I just like to say thanks again for your time on this.

I created the extra fields in the table and ran the code. The data from the sheets does import but yes I found a small element of inter-record corruption - namely that one of the series does not align up properly, the data appears in the right column but not in a continuous series of rows, one small chunk is broken out with data for another series. But I shouldn't think this would be an issue if we can get the field names to populate in the table.

Yes, the three spreadsheets do have differing columns in differing sequences, that is, the spreadsheets have different field names/columns, there is no identical column in sheet apart from the first column, which are the dates.

When doing this manually, I did use the import wizard to import each file. I then did a join on the date field across the tables but ran into problems, as Access doesn't allow you to create a full outer join.

Lucas

 

[Andrew Fergus]

Hi Lucas

Have you tried using a union query?

Andrew

I then did a join on the date field across the tables but ran into problems, as Access doesn't allow you to create a full outer join.

 

[Lucas in London]

Hi Andrew,

I'm not really a SQL wizard, just know the basics, never done a union query before. In the past have only used SQL oracle, Access seems to use a strnage sQL language.

Below is the oracle SQL code that I used (inner join only) which seemed to have worked in Access but I don't know why!

SELECT *
FROM BOE, Indices, Technicals
WHERE BOE.date=indices.date and boe.date=technicals.date;

Lucas