Excel provides rich features by which you can bring data from various data sources into your excel file and make necessary tasks accomplished. In this article we will see how we can bring data first from a CSV file to MS Access database then into Excel file.
- CSV file
- MS Access database
Step 1: open MS Access database and Add new database. Navigate External Data ribbon tab as shown below and New Data Source dropdown and select File From>> Text File under Import & Link group.
It pops up a Import dialog (shown below) “Get External Data – Text File” asking for File name, user needs to specify the CSV file path by clicking on Browse button or simply copy paste. In this example my CSV file name is “data.csv“. There are three options in below section as:
- Import the source data into a new table in the current database (default): this indicates that the data that you are trying to bring into database would be placed in a new table
- Append a copy of the records to the table: if you choose so, next dropdown gets enabled with all available tables in your database where you can make a choice that under which table you want to put all new records coming from CSV file
- Link to the data source by creating a link table: this is really a good feature in MS Access where you can directly link your worksheet into MS Access database and it turns into a real table. You can perform all database operations like INSERT, UPDATE, ALTER, DELETE etc. Another benefit, if you want to change your source info you can do it in Excel and upon Saving the changes gets reflected in Access database.
Now clock OK button by selecting the default option “Import the source into a new table in the current database“. a new screen comes as below:
In Above screenshot we can see there two options available:
- Delimited: this is default it refers to the various delimited data formats like comma(,), tab, hyphen (-), pipe (|) etc. which system can auto recognize based on user input
- Fix width: if your data source is broken using space then user can opt for this option and system will recognize the fields
In our csv file the data is comma separated hence we will go with “Delimited” option. And it will take to the next screen as shown below:
Make sure Comma option is selected. If you think your data contains column name please enable the check labeled as “First Row contains Field Names“. In our CSV we have headers hence will check it and hit Next button. The next screen comes asking to set your data types for each column. It is an optional process as Access auto recognize the columns data type based on values as shown below:
Click next and it will take through to opt for Primary key where you can choose which column will be primary key. If primary key does not present it will default add one auto increment column, however user has option to opt for no primary key.
Click next and it will prompt for the table name that you would like to have. By default it picks file or sheet name as table name as shown below:
Finally you hit finish and table will be created as shown below: