Import data from excel file

[gstylianou]

My friends hello,

Attached there is a folder in which includes an Excel file (SECA) as well as one database (accdb). The excel file contains data that is automatically exported by the machine (SECA).

The database contains one table in which I would like through the form1 using the command button to automatically import the data from the Excel file without having to import the Excel file into the database.

Is that possible?

 

[isladogs]

I haven't looked at the files.
Why not link the Excel file and run an append query based on that?

 

[gstylianou]

I haven't looked at the files.
Why not link the Excel file and run an append query based on that?

Hi Isladogs,

Because doctor export different excel file each time use the machine...and the link solution is not a good idea..

 

[Solo712]

My friends hello,

Attached there is a folder in which includes an Excel file (SECA) as well as one database (accdb). The excel file contains data that is automatically exported by the machine (SECA).

The database contains one table in which I would like through the form1 using the command button to automatically import the data from the Excel file without having to import the Excel file into the database.

Is that possible?

Hi gsty...,
I have tried to append the data to the table using the Access import data facility (on the External Data tab). It could not match some column headings and complained about invalid field names. It's actually quite easy to append Excel data to an existing table but you have to match the columns to field names exactly and you must avoid invalid characters in names. If the headings are generated by the Seca machine, you will have to edit the output headings before attempting to import the data into Access. In the alternative, you could lift the data from the spreadsheet via VBA using cell reference, but considering that you have 184 columns to work with it would be quite a chore.

Best,
Jiri

 

[Frothingslosh]

You could use a FileDialog to get the file's path, and TransferSpreadsheet to import the spreadsheet's data either to a staging table (my recommendation) or, if you're 100% confident the data is completely clean, directly to a production table as long as that table has the same columns as the spreadsheet.

You can also, as was mentioned, open the spreadsheet and go cell-by-cell (and there are a couple solutions along that line in the Repository), but that is very, very slow.

 

[Gasman]

Rename the file each time?

Hi Isladogs,

Because doctor export different excel file each time use the machine...and the link solution is not a good idea..

 

[gstylianou]

Rename the file each time?

Guys,

The doctor doesn't want to do nothing more...just export data from machine and then using the command to append the data into the table (tblExcel).

Note that,

those data will later analysed by different procedures into access database (e.g such as graphs, doctor report and may other)..

 

[isladogs]

Years ago I had a similar situation with my main schools' database when teachers were doing student reports. A spreadsheet could be created for a teaching group from the database using a template then completed externally offsite. The next day the teacher would browse for the spreadsheet file from the database and import the grades. As the spreadsheet was created using a template, it's structure was always the same which made the import code relatively straightforward but it was still many lines of code to cope with all possible variations.

So it's possible to do what you say with an automated routine but the file will still need to be browsed for...or renamed. You will also need to ensure a consistent structure in the spreadsheet files. Same column order, headings and datatypes

A better solution (which we changed to) was to get staff to enter the grades direct into the database and (necessary in our case) allow remote access from home using terminal services.

Whether or not you need remote access, I would recommend your system is changed so that doctors enter the data directly into Access.

 

[gstylianou]

Years ago I had a similar situation with my main schools' database when teachers were doing student reports. A spreadsheet could be created for a teaching group from the database using a template then completed externally offsite. The next day the teacher would browse for the spreadsheet file from the database and import the grades. As the spreadsheet was created using a template, it's structure was always the same which made the import code relatively straightforward but it was still many lines of code to cope with all possible variations.

So it's possible to do what you say with an automated routine but the file will still need to be browsed for...or renamed. You will also need to ensure a consistent structure in the spreadsheet files. Same column order, headings and datatypes

A better solution (which we changed to) was to get staff to enter the grades direct into the database and (necessary in our case) allow remote access from home using terminal services.

Whether or not you need remote access, I would recommend your system is changed so that doctors enter the data directly into Access.

Isladogs,

The excel structure is always the same. And yes, doctor is able to browse the file and using routine to import as as is it into the access database.. So, what i'm thinking is.:

1. First to import the file into access database
2. Using an append query to easy append the data into the tblExcell table....

But, the fields are too much for query i think and i'm not sure if this idea is the correct solution for the problem...

 

[isladogs]

There is no need to import the file or even overtly link it if you use a VBA procedure to extract the data

An append query would require the Excel file to be linked. You could simplify it by giving the Excel file an alias e.g. ExcelImport & using that alias in your append query ... but a VBA procedure would work better

Better still completely bypass the Excel file & just get your doctors to work directly in the Access file using forms designed to be as straightforward as possible so it makes their life simple. If they can do everything in one step they should be happy

 

[gstylianou]

There is no need to import the file or even overtly link it if you use a VBA procedure to extract the data

An append query would require the Excel file to be linked. You could simplify it by giving the Excel file an alias e.g. ExcelImport & using that alias in your append query ... but a VBA procedure would work better

Better still completely bypass the Excel file & just get your doctors to work directly in the Access file using forms designed to be as straightforward as possible so it makes their life simple. If they can do everything in one step they should be happy

My friend isladogs,

how can i give an alias ? Could you please modify my exmple?
: An append query would require the Excel file to be linked. You could simplify it by giving the Excel file an alias e.g. ExcelImport & using that alias in your append query ... but a VBA procedure would work better

thanks again

 

[isladogs]

After linking your Excel file, right click and click rename to e.g. Excel Import.
As the original file is unchanged, you are just creating an alias.

However if the structure is always the same, can't they just append a copy to the final table... or does it need data manipulation first?

The advantage of creating a procedure is of course that the import 'just happens' automatically

 

[Gasman]

And I would ask why is a doctor doing this? Surely they are better employed being a doctor using doctor skills. Hell, if I go to my GP, my doctors can barely type on the keyboard.

Some admin/IT person should be doing this.

In my last job, I had to download data from various systems each day and bring into the various databases or Excel files I created.

I didn't expect the IFA to have to do it.

Guys,

The doctor doesn't want to do nothing more...just export data from machine and then using the command to append the data into the table (tblExcel).

Note that,

those data will later analysed by different procedures into access database (e.g such as graphs, doctor report and may other)..

 

[gstylianou]

After linking your Excel file, right click and click rename to e.g. Excel Import.
As the original file is unchanged, you are just creating an alias.

However if the structure is always the same, can't they just append a copy to the final table... or does it need data manipulation first?

The advantage of creating a procedure is of course that the import 'just happens' automatically

Unfortunately needs data manipulation first because the data into excel file is Short Text and to the final table is number (Double)

 

[gstylianou]

And I would ask why is a doctor doing this? Surely they are better employed being a doctor using doctor skills. Hell, if I go to my GP, my doctors can barely type on the keyboard.

Some admin/IT person should be doing this.

In my last job, I had to download data from various systems each day and bring into the various databases or Excel files I created.

I didn't expect the IFA to have to do it.

Gasman the SECA its a just a machine with its own software..!! Doctor have our Patient Management System Software which use every day for all the other purposes...

 

[isladogs]

Unfortunately needs data manipulation first because the data into excel file is Short Text and to the final table is number (Double)

Not a problem. The Excel file (renamed) acts as a staging or buffer table from which the manipulated data is extracted, converting datatypes as necessary.

 

[Gasman]

Only you know your own processes, but if software is exporting some file, surely there must be some logic to it, like today's date.?

At the end of the day though, I think you will have to carry our an additional step or steps to get what you want (or would like).

Good luck