Import CSV to Excel matching column header using VBA

Question

I want to ask if how can I achieve importing CSV file to my Excel file matching the columns of my template using macro (or VBA).

Here's why I'm frustrated to ask this question. I searched for answer. Google didn't help. No one is asking for this type of question.

I have a file name "UserInfo.xlsx" which has a column headers:

UserID
Username
Name
ContactNumber
EmailAddress
LastLogin

Now I have a CSV file (generated by our website) with some excess headers which I don't need.

The headers from my CSV file and headers from my template are exactly the same. The problem is I want to get those information based on their column headers (CSV) that will match the template's header.

How can I achieve this? I will use this template everyday because new users are signing up to our website. So I need a VBA code for this.

I don't know where to start so I'm asking for your help.

Thank.

score 2 · Accepted Answer · edited Nov 23 '16 at 03:05

You can query the csv file with an ADODB.Connection. If you have over 65K record then CopyFromRecordset probably won't work and you'll have to use ADODB.Recordset.GetRows to retrieve the data.

Sub Example()
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim conn As Object, rs As Object
Dim Filename As String, FilePath As String

FilePath = "C:\"
Filename = "UserInfo.csv"

'Refernce: https://msdn.microsoft.com/en-us/library/ms974559.aspx
'Excel VBA Introduction Part 30 - ADO (ActiveX Data Objects) Querying a Database: https://www.youtube.com/watch?v=HE9CIbetNnI&index=33&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & FilePath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

rs.Open "SELECT [UserID],[Username],[Name],[ContactNumber],[EmailAddress],[LastLogin] FROM [" & Filename & "]", _
        conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
conn.Close
End Sub

I created an example workbook: [Download Example Workbook](https://drive.google.com/open?id=0ByjAVr__2CigOEhHVWhmemJVUjQ) — , Nov 06 '16 at 04:50

score 1 · Answer 2 · answered Oct 15 '16 at 01:21

Your code works like a charm! Although I change it a little bit since I'm using Windows 10 and I think Microsoft.Jet.OLEDB.4.0 doesn't work with my current Excel version (2016).

I changed Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0

I also found out that the FilePath variable is wrong. I removed the filename which is UserInfo.csv and I was shocked when I tried running it. Works as expected.

Since I don't have enough points to post a screenshot, I can say that your code really and I mean really works!

Thank you so much.

Here's my final code:

Sub Example()
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim conn As Object, rs As Object
Dim Filename As String, FilePath As String

FilePath = "C:\" 'Just remove the filename and extension
Filename = "UserInfo.csv"

'Refernce: https://msdn.microsoft.com/en-us/library/ms974559.aspx
'Excel VBA Introduction Part 30 - ADO (ActiveX Data Objects) Querying a Database: https://www.youtube.com/watch?v=HE9CIbetNnI&index=33&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & FilePath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited""" 'Change the Provider

rs.Open "SELECT [UserID],[Username],[Name],[ContactNumber],[EmailAddress],[LastLogin] FROM [" & Filename & "]", _
        conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
conn.Close
End Sub

Again thank you so much Thomas Inzina. You have just saved my life. :)

I have a question Thomas. Is it possible to import my CSV files in UTF-8 encoding? — Daddy Joe, Oct 20 '16 at 00:23
The excepted answer to this post shows you how: [Is it possible to force Excel recognize UTF-8 CSV files automatically?](http://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically) You could record a macro to find out the steps. Can you provide me a sample csv file? — , Oct 20 '16 at 02:28
Another question. I know this is kinda off-topic but how can I stop Excel converting a data into numbers. Example, the usercode is 0008, Excel automatically converts it into 8 literally. Or should I ask how to stop excel removing the preceding zeros and treat it as a normal string? — Daddy Joe, Oct 21 '16 at 00:29
You can format the cells as text. If all the ID's have 4 characters you can use a custom NumberFormat format e.g. if `[A1].NumberFormat = "0000"` then if `[A1] = 8` then `[A1].Value` would equal `8` and `[A1].Text` would = `0008`. — , Oct 21 '16 at 04:33

score 0 · Answer 3 · answered Nov 02 '16 at 05:35

0

Contextures Excel Sample Data

I would record a macro importing your file using these steps: How to import a .csv file that uses UTF-8 character encoding. You should be able to easily customize the recorded macro to fit your needs.

Note: When importing CSV files Excel allows you to skip columns.

answered Nov 02 '16 at 05:35

Thanks for this. I already found an answer and it works fine. – Daddy Joe Nov 02 '16 at 06:01
I add CharacterSet=65001 to my connection string. conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & FilePath & ";" & _ "Extended Properties='text;HDR=YES;FMT=Delimited(,);CharacterSet=65001'" – Daddy Joe Nov 02 '16 at 06:02
Another question (sorry for too much questions hehehe) Is it possible to scan first the file before putting it to the designated sheet? I mean it will first read the column row by row. If detected a number with preceeding zeros or any number, automatically it will add semicolon in front of that number. Is it possible? I wonder how to do that. – Daddy Joe Nov 02 '16 at 07:06
1

A CSV file is just a text file. There are several ways to read and write to it. But if you add delimiters you will through off the columns. – Nov 02 '16 at 07:16
Oopss. Hahaha sorry. Maybe because I often use OpenOffice to open CSV files. Hahahaha. – Daddy Joe Nov 02 '16 at 07:44

Import CSV to Excel matching column header using VBA

3 Answers3