subhasmita sahu
asked on
How to parse excel, txt file and insert into MS Access databae
I have few excel and .rpt file which are updating daily basis, in my scenario i have to parse the files and some of the columns should be insert into MS access database. i am not sure how to start with. As per my manager i should use shell scripting to write the code but i am not sure which is the best way.
I need help in this any kind of help will be appreciated.
I need help in this any kind of help will be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Everyone for the quick reply, It helps me to understand more - i am new to MS Access also i am not aware of all the features in MS access.
If i link the excel file to ms access then will be refresh daily basis as the excel file is a daily feed file?? and also i don't require the whole excel data i need to import some of the column into the table.
Here .rpt i am refereeing to a .txt file which is report containing information as below format:
==========================
x: ABC y: yut z: kkjlsdlk
I have to retrieve some of the value.
And i was suggested to use shell script so that it can made as a batch file which can be run daily basis and in future the database is getting migrated to sql.
If i link the excel file to ms access then will be refresh daily basis as the excel file is a daily feed file?? and also i don't require the whole excel data i need to import some of the column into the table.
Here .rpt i am refereeing to a .txt file which is report containing information as below format:
==========================
x: ABC y: yut z: kkjlsdlk
I have to retrieve some of the value.
And i was suggested to use shell script so that it can made as a batch file which can be run daily basis and in future the database is getting migrated to sql.
Yes, if you choose the link option in Access, the data will update in Access as the Excel file is updated.
If i link the excel file to ms access then will be refresh daily basis as the excel file is a daily feed file??If the file name and format don't change, the file will be refreshed every time Access opens it. However, if you are going to do this in vbScript, why involve Access at all?
Sounds like you are not really using Access but are just loading data into Jet or ACE for use in another application. Access is a Rapid Application Development (RAD) Tool. It is used to create applications. Jet and ACE are the database engines that hold data. Access, the RAD tool is not limited to Jet and ACE as data stores. They can use data from any RDBMS that supports ODBC. So I have applications with Access front ends that link to SQL Server, Oracle, DB2, and several other less well known RDBMS'.
ASKER
@patHartman
I too don't want to use any shell script but i am new to MS Access so i am not aware of the features in MS access. I think now i can start with my project but can you please guide me whether my steps are correct or not to proceed:
1.Create a new table in the database as per the data type.
2.write VBA in MS Access to import the excel file.
Now here can i use the DoCmd.Transferspreadsheet method ? If so then how to use to copy only some columns and the column names in excel and database will be different.
3. How to link both excel and ms access?if i link excel to ms access then if any changes made in ms access then will it reflect in excel because i don't want the changes should reflect in excel as its a daily feed file.
I too don't want to use any shell script but i am new to MS Access so i am not aware of the features in MS access. I think now i can start with my project but can you please guide me whether my steps are correct or not to proceed:
1.Create a new table in the database as per the data type.
2.write VBA in MS Access to import the excel file.
Now here can i use the DoCmd.Transferspreadsheet method ? If so then how to use to copy only some columns and the column names in excel and database will be different.
3. How to link both excel and ms access?if i link excel to ms access then if any changes made in ms access then will it reflect in excel because i don't want the changes should reflect in excel as its a daily feed file.
Once again, you don't need to use VBA or any other kind of code to import the excel.
Create a blank access database. Click on External Data on the Ribbon. Select Excel.
Select the Excel file you want to bring data from.
Select Link Data source.
Click Ok. This will get you started.
Create a blank access database. Click on External Data on the Ribbon. Select Excel.
Select the Excel file you want to bring data from.
Select Link Data source.
Click Ok. This will get you started.
ASKER
@JP_TechGroup
why i am looking for VBA is because i need make it automate because the solution will be used by some of the users who are do not have any idea of database. I have to first make the process automate so the data will be saved from excel in MS access and it should be refreshed every day and from database they should be able to populate the data.
why i am looking for VBA is because i need make it automate because the solution will be used by some of the users who are do not have any idea of database. I have to first make the process automate so the data will be saved from excel in MS access and it should be refreshed every day and from database they should be able to populate the data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
But again here the excel sheet column names and database table columns will be different. and i do not need to import all of the column only some specific columns should go into some columns.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
»bp